Solved

Concatenate fields and use the Alias field to link another field in another table

Posted on 2012-12-31
5
240 Views
Last Modified: 2013-02-22
I have two tables that I would like to link together for making labels.  I would like to link the tables together to get my results.   The issue lies when I inner join the tables.  table a has two fields and table b has one field that I need to link together to get my results.  I was able to get the alias to link the tables together, but I get stuck on how to link an alias to another table in the same query.

Here is the tables and my query
Work_Order
Base_ID       Lot_ID   Part_ID        Printed_Date
WO-00009    1           112A7000    12/31/2012

Trace
Part_ID         AProperty_1      SN
112A7000     WO-00009/1     SN1223

I would like the results to be
Part_ID          SN              Printed_Date
112A7000     SN1223      12/31/2012

I think my issue is with the Alias . .

SELECT WORK_ORDER.BASE_ID, WORK_ORDER.LOT_ID, WORK_ORDER.PART_ID, [WORK_ORDER].[BASE_ID]+ '/' + [WORK_ORDER].[LOT_ID] AS WO
FROM WORK_ORDER
INNER JOIN TRACE ON WORK_ORDER.PART_ID = TRACE.PART_ID AND WO = TRACE.APROPERTY_1
WHERE WORK_ORDER.PRINTED_DATE > 12/31/12

Thanks so much!!  Happy New Year!!
0
Comment
Question by:DannerCorp
5 Comments
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38733936
Try like this.... You can not use aliases in where clause....need to write full expression

SELECT WORK_ORDER.BASE_ID, WORK_ORDER.LOT_ID, WORK_ORDER.PART_ID, [WORK_ORDER].[BASE_ID]+ '/' + [WORK_ORDER].[LOT_ID] AS WO
FROM WORK_ORDER
INNER JOIN TRACE ON WORK_ORDER.PART_ID = TRACE.PART_ID AND [WORK_ORDER].[BASE_ID]+ '/' + [WORK_ORDER].[LOT_ID] = TRACE.APROPERTY_1
WHERE WORK_ORDER.PRINTED_DATE > 12/31/12

Open in new window

0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38733963
You said you need result like this
Part_ID          SN              Printed_Date
112A7000     SN1223      12/31/2012

for this result Query will be

Select Work_Order.Part_ID,SN ,Printed_Date From Work_Order
inner join Trace on Work_Order.Part_Id = Trace.Part_ID

To Correct your given query try this

SELECT WORK_ORDER.BASE_ID, WORK_ORDER.LOT_ID, WORK_ORDER.PART_ID, [WORK_ORDER].[BASE_ID]+ '/' + [WORK_ORDER].[LOT_ID] AS WO
FROM WORK_ORDER
INNER JOIN TRACE ON WORK_ORDER.PART_ID = TRACE.PART_ID AND ([WORK_ORDER].[BASE_ID]+ '/' + [WORK_ORDER].[LOT_ID]) = TRACE.APROPERTY_1
WHERE WORK_ORDER.PRINTED_DATE > '12/31/12'
0
 
LVL 7

Expert Comment

by:armchang
ID: 38733980
You certainly cannot use a column alias but you could try using subquery aliases if you're heavily using custom columns on your queries like this:

SELECT A.BASE_ID, A.LOT_ID, A.PART_ID, A.WO FROM 
(SELECT *, [WORKORDER].[BASE_ID]+ '/' + [WORKORDER].[LOT_ID] AS WO
FROM WORKORDER) A
INNER JOIN TRACE ON A.PART_ID = TRACE.PART_ID AND A.WO = TRACE.APROPERTY_1
WHERE A.PRINTED_DATE > 12/31/12

Open in new window

A is the subquery alias and replaces the Work_Order table on the outer query.
0
 
LVL 31

Expert Comment

by:awking00
ID: 38737156
select t.part_id, t.sn, x.printed_date
from trace t,
(select base_id + '/' + lot_id as aproperty_1, part_id, printed_date
 from work_order
 where x.printed_date > 12/31/2012) as x
where t.part_id = x.part_id and t.aproperty_1 = c.aproperty_1
0
 
LVL 7

Accepted Solution

by:
armchang earned 500 total points
ID: 38742103
Though if the query I've posted is not working you can add apostrophe's or specify a long date format:
SELECT A.BASE_ID, A.LOT_ID, A.PART_ID, A.WO FROM 
(SELECT *, [WORKORDER].[BASE_ID]+ '/' + [WORKORDER].[LOT_ID] AS WO
FROM WORKORDER) A
INNER JOIN TRACE ON A.PART_ID = TRACE.PART_ID AND A.WO = TRACE.APROPERTY_1
WHERE A.PRINTED_DATE > '12/31/12'

Open in new window


or
SELECT A.BASE_ID, A.LOT_ID, A.PART_ID, A.WO FROM 
(SELECT *, [WORKORDER].[BASE_ID]+ '/' + [WORKORDER].[LOT_ID] AS WO
FROM WORKORDER) A
INNER JOIN TRACE ON A.PART_ID = TRACE.PART_ID AND A.WO = TRACE.APROPERTY_1
WHERE A.PRINTED_DATE > '2012-12-31'

Open in new window

A
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now