asked on

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

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!!

Saurabh Bhadauria

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

Pratima

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'

Armand G

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.

awking00

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

ASKER CERTIFIED SOLUTION

Armand G

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial