Improve company productivity with a Business Account.Sign Up

x
?
Solved

Can a result be accomplish with just one query?

Posted on 2011-02-19
2
Medium Priority
?
419 Views
Last Modified: 2012-05-11
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
Comment
Question by:Exl04
2 Comments
 
LVL 93

Accepted Solution

by:
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

by:Exl04
ID: 34935533
Great Patrick, exacly what I needed!

Thanks!
0

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

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.

Join & Write a Comment

Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
I recently worked on a Wordpress site that utilized the popular ContactForm7 (https://contactform7.com/) plug-in that only sends an email and does not save data. The client wanted the data saved to a custom CRM database. This is my solution.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

608 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