x
Solved

# Can a result be accomplish with just one query?

Posted on 2011-02-19
Medium Priority
419 Views
I have two tables (table1, table2) with description of a product (a car) in these two tables the car field is a common field. In the first table de date of when the car was painted is storage, in table2 the when it was delivered.  How can I accomplish with a query a result as table 3 shown in picture? I need to show all the cars numbers from both tables in one column and in two column show when the car was either painted of delivered.

Thanks in advance for the help

tables.bmp
0
Question by:Exl04

LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 34935474
SELECT t1.[CarNumber], t1.[DatePainted], t2.[DateDelivered]
FROM [table1] t1 INNER JOIN
[table2] t2 ON t1.[CarNumber] = t2.[CarNumber]
UNION ALL
SELECT t1.[CarNumber], t1.[DatePainted], Null AS [DateDelivered]
FROM [table1] t1 LEFT JOIN
[table2] t2 ON t1.[CarNumber] = t2.[CarNumber]
WHERE t2.[CarNumber] Is Null
UNION ALL
SELECT t2.[CarNumber], Null AS [DatePainted], t2.[DateDelivered]
FROM [table1] t1 RIGHT JOIN
[table2] t2 ON t1.[CarNumber] = t2.[CarNumber]
WHERE t1.[CarNumber] Is Null
0

LVL 1

Author Closing Comment

ID: 34935533
Great Patrick, exacly what I needed!

Thanks!
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.