• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 418
  • Last Modified:

Can a result be accomplish with just one query?

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
Exl04
Asked:
Exl04
1 Solution
 
Patrick MatthewsCommented:
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
 
Exl04Author Commented:
Great Patrick, exacly what I needed!

Thanks!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now