?
Solved

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

Posted on 2012-12-31
5
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!!
0
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
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 32

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

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…
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…
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

762 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