[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to join two tables and show unmatched rows for BOTH tables

Posted on 2006-06-07
9
Medium Priority
?
452 Views
Last Modified: 2012-08-14
Say I want to join table1 and table2. If I do "SELECT * FROM table1 LEFT JOIN table2 USING(SomeField)" any rows that exist in table1 yet do not exist in table2 will still show up as well as matched rows, which is good. But any rows that exists in table2 yet not exist in table 1 will not.

I want to be able to show any rows that do not exists in table1 and yet exists in table2 and any rows that exist in table2 yet not exist in talbe1, along with all the matched rows. Please help.
0
Comment
Question by:krupini
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16856358
SELECT * FROM table1 FULL OUTER JOIN table2 USING(SomeField)
0
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 1600 total points
ID: 16856512
"FULL OUTER" doesn't seem to work in MySQL, at least not as of version 5.0.

You can do a left join and a right join and combine the two with union:
SELECT * FROM table1 LEFT JOIN table2 USING (someField) UNION SELECT * FROM table1 RIGHT JOIN table2 USING (someField);
0
 

Author Comment

by:krupini
ID: 16856543
I tried and it didnt work. It says in MySQL manual that FULL OUTER JOIN is not supported.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16856596
indeed, full outer join is (still) not available, even in 5.1
http://dev.mysql.com/doc/refman/5.1/en/join.html
:-(

I am using  MSSQL Server too much ...
0
 
LVL 5

Assisted Solution

by:sriramiyer
sriramiyer earned 400 total points
ID: 16859422
Hi krupini,

The only way I can think to make it work is through a UNION.

(
SELECT <column list>
FROM table a
LEFT JOIN table b
        ON a.keycol = b.keycol
) UNION (
SELECT <column list>
FROM table b
LEFT JOIN table a
        ON b.keycol = a.keycol
WHERE a.keycol is null
)


The UNION will only return the columns you ask it to. This method also
avoids manually creating a temporary table. So long as you do not say
UNION ALL, you won't get any duplicate rows.

Hope it works!
From
SriRamIyer.
0
 

Author Comment

by:krupini
ID: 16861762
Hmm, I tried them all, none of the seem to do the trick. Maybe if I explain what I am trying to do then all will become clear.

I have a simple sales table

+----+------------+--------+
| id | saleDate   | amount |
+----+------------+--------+
|  1 | 2005-01-01 |   2.25 |
|  2 | 2005-01-02 |   2.27 |
|  3 | 2006-01-01 |   3.55 |
|  4 | 2006-03-01 |   7.88 |
+----+------------+--------+

I am trying to produce a comparative year over year sales summary. For example, I want to know how much was sold on day x in the year 2006, and on the same day in the year 2005. So after executing the query, I should have something like:

+--------------+------------+--------------+------------+-----------------------+
| currSaleDate | currAmount | prevSaleDate | prevAmount | amountDiff |
+--------------+------------+--------------+------------+-----------------------+
| 2006-03-01   |       7.88     | NULL             |       NULL      |       7.88 |
| 2006-01-01   |       3.55     | 2005-01-01    |       2.25      |        1.3 |
| NULL            |       NULL     | 2005-01-02   |       2.27       |      -2.27 |
+--------------+------------+--------------+------------+-----------------------+

My query thus far:

(
SELECT currSales.saleDate AS currSaleDate, currSales.amount AS currAmount,
prevSales.saleDate AS prevSaleDate, prevSales.amount AS prevAmount
FROM sales currSales
LEFT JOIN sales prevSales ON (DAYOFYEAR(currSales.saleDate) = DAYOFYEAR(prevSales.saleDate))
WHERE currSales.saleDate BETWEEN '2006-01-01' AND '2006-12-31'
)
UNION
(
SELECT prevSales.saleDate AS prevSaleDate, prevSales.amount AS prevAmount,
currSales.saleDate AS currSaleDate, currSales.amount AS currAmount
FROM sales prevSales
LEFT JOIN sales currSales ON (DAYOFYEAR(prevSales.saleDate) = DAYOFYEAR(currSales.saleDate))
WHERE currSales.saleDate is NULL AND prevSales.saleDate BETWEEN '2005-01-01' AND '2005-12-31'
)

And it produces:


+--------------+------------+--------------+-----------------------+
| currSaleDate | currAmount | prevSaleDate | prevAmount |
+--------------+------------+--------------+-----------------------+
| 2006-01-01   |       3.55 | 2005-01-01        |       2.25 |
| 2006-01-01   |       3.55 | 2006-01-01        |       3.55 |
| 2006-03-01   |       7.88 | 2006-03-01        |       7.88 |
+--------------+------------+--------------+-----------------------+

Please help.

0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 16862899
Don't you need to extend the where clause to put the prevSales in 2005 for the first select, and currSales in 2006 for the second?
0
 

Author Comment

by:krupini
ID: 16864296
If I do

(
SELECT currSales.saleDate AS currSaleDate, currSales.amount AS currAmount,
prevSales.saleDate AS prevSaleDate, prevSales.amount AS prevAmount
FROM sales currSales
LEFT JOIN sales prevSales ON (DAYOFYEAR(currSales.saleDate) = DAYOFYEAR(prevSales.saleDate))
WHERE currSales.saleDate BETWEEN '2006-01-01' AND '2006-12-31'
AND prevSales.saleDate BETWEEN '2005-01-01' AND '2005-12-31'
)
UNION
(
SELECT prevSales.saleDate AS prevSaleDate, prevSales.amount AS prevAmount,
currSales.saleDate AS currSaleDate, currSales.amount AS currAmount
FROM sales prevSales
LEFT JOIN sales currSales ON (DAYOFYEAR(prevSales.saleDate) = DAYOFYEAR(currSales.saleDate))
WHERE currSales.saleDate is NULL AND prevSales.saleDate BETWEEN '2005-01-01' AND '2005-12-31'
AND currSales.saleDate BETWEEN '2006-01-01' AND '2006-12-31'
)

I get:

+--------------+------------+--------------+----------------------+
| currSaleDate | currAmount | prevSaleDate | prevAmount |
+--------------+------------+--------------+----------------------+
| 2006-01-01   |       3.55     | 2005-01-01    |       2.25      |
+--------------+------------+--------------+----------------------+
0
 

Author Comment

by:krupini
ID: 16864612
Ahh, but this:

SELECT currSales.saleDate AS currSaleDate, currSales.amount AS currAmount,
prevSales.saleDate AS prevSaleDate, prevSales.amount AS prevAmount,
(COALESCE(currSales.amount, 0) - COALESCE(prevSales.amount, 0)) AS amountDiff
FROM sales currSales
LEFT JOIN sales prevSales ON (DAYOFYEAR(currSales.saleDate) = DAYOFYEAR(prevSales.saleDate)
AND currSales.saleDate > prevSales.saleDate)
WHERE currSales.saleDate BETWEEN '2006-01-01' AND '2006-12-31'
UNION
SELECT currSales.saleDate AS currSaleDate, currSales.amount AS currAmount,
prevSales.saleDate AS prevSaleDate, prevSales.amount AS prevAmount,
(COALESCE(currSales.amount, 0) - COALESCE(prevSales.amount, 0)) AS amountDiff
FROM sales currSales
RIGHT JOIN sales prevSales ON (DAYOFYEAR(currSales.saleDate) = DAYOFYEAR(prevSales.saleDate)
AND currSales.saleDate > prevSales.saleDate)
WHERE prevSales.saleDate BETWEEN '2005-01-01' AND '2005-12-31'


Beauty:
+--------------+------------+--------------------------+------------+------------+
| currSaleDate | currAmount | prevSaleDate | prevAmount | amountDiff |
+--------------+------------+-------------------------+------------+------------+
| 2006-01-01   |       3.55     | 2005-01-01    |       2.25      |        1.3 |
| 2006-03-01   |       7.88     | NULL             |       NULL      |       7.88 |
| NULL            |       NULL     | 2005-01-02    |       2.27      |      -2.27 |
+--------------+------------+-------------------------+------------+------------+
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
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…
Suggested Courses
Course of the Month20 days, 5 hours left to enroll

872 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