Solved

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

Posted on 2012-12-31

Microsoft SQL Server

1 solution

Medium Priority

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

Comment

Question by:DannerCorp

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

5 Comments

LVL 12

Expert Comment

by:Saurabh Bhadauria

ID: 387339362012-12-31

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

LVL 39

Expert Comment

by:Pratima Pharande

ID: 387339632012-12-31

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'

LVL 7

Expert Comment

by:armchang

ID: 387339802012-12-31

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.

LVL 32

Expert Comment

by:awking00

ID: 387371562013-01-02

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

LVL 7

Accepted Solution

by:armchang

armchang earned 2000 total points

ID: 387421032013-01-03

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

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

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Step by step guide to take SQL Server 2012 new feature Available Groups (Hadr) to Production

Article by: Duane

Why is this different from all of the other step by step guides? Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…

Microsoft SQL Server

5 Lessons the Delta Outage Should Teach Us About Datacenter Security and Disaster Prevention

Article by: Concerto Cloud

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…

Executing a SQL Server Function from Within Access

Video by: TechMommy

Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…