Multi mysql query using two different tables

Question

I'm having an issue writing a query. I have two tables I want to pull data from, sales and calls. There is a column in both tables where a phone number is recorded, as well as a date column. I can do the JOIN just fine to match calls to sales, however I want to also show entries from both tables that don't match. Basically on a single page show the rows that match from both tables, then the rows that didn't match from each table separately.

Here is the JOIN query:

SELECT 
   sales.*, 
   calls.* 
FROM `sales` 
JOIN `calls` 
    ON sales.TelephoneNo = calls.TelephoneNo 
WHERE (
       sales.OrderDate >= '$MyStartDATE' 
   AND sales.OrderDate <= '$MyEndDATE'
) AND (
       calls.CallDate >= '$MyStartDATE' 
   AND calls.CallDate <= '$MyEndDATE'
) ORDER BY sales.OrderDate

You are possibly looking for a [`FULL OUTER JOIN`](http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql). — Wrikken, Sep 19 '14 at 19:12

Carca · Answer 1 · 2014-09-19T19:52:39.863

0

Well, try to use <> operator instead = to show not matched results

SELECT 
   sales.*, 
   calls.* 
FROM `sales` 
JOIN `calls` 
    ON sales.TelephoneNo <> calls.TelephoneNo 
WHERE (
       sales.OrderDate >= '$MyStartDATE' 
   AND sales.OrderDate <= '$MyEndDATE'
) AND (
       calls.CallDate >= '$MyStartDATE' 
   AND calls.CallDate <= '$MyEndDATE'
) ORDER BY sales.OrderDate

UPDATE

If you use LEFT JOIN Instead of JOIN it makes difference ?

Try this code

SELECT sales.*, 
       calls.* 
FROM   `sales` 
       LEFT JOIN `calls` 
              ON sales.telephoneno <> calls.telephoneno 
WHERE  sales.orderdate >= '$MyStartDATE' 
       AND sales.orderdate <= '$MyEndDATE' 
       AND calls.calldate >= '$MyStartDATE' 
       AND calls.calldate <= '$MyEndDATE' 
ORDER  BY sales.orderdate

edited Sep 19 '14 at 19:52

answered Sep 19 '14 at 19:14

Carca

564
1
6
16

I tried that option however what I got was one row from the call table repeated for each row of the sales table. So a 200 row sales table matched to the call table with 300 resulted in over 5000 results. – user2843577 Sep 19 '14 at 19:40
Because it's almost similar to a cross join – Gervs Sep 19 '14 at 19:48
A left join makes little difference. you'll only get more records in the result – Gervs Sep 19 '14 at 20:06

Gervs · Answer 2 · 2014-09-19T20:54:44.410

0

MySQL doesn't support full joins, you'll need to combine left and right join with union

SELECT
    s.OrderDate,
    PhoneNumber,
    s.something fake,
    IF(c.PhoneNumber IS NULL, 'Sales', 'Both') match_from,
    IF(c.PhoneNumber IS NULL, 0, 1) has_match
FROM
    sales s
LEFT JOIN
    calls c
    USING (PhoneNumber)
WHERE
    s.OrderDate BETWEEN '$MyStartDATE' AND '$MyEndDATE'
UNION DISTINCT
SELECT
    c1.CallDate,
    PhoneNumber,
    'fake',
    IF(s1.PhoneNumber IS NULL, 'Call', 'Both'),
    IF(s1.PhoneNumber IS NULL, 0, 1)
FROM
    sales s1
RIGHT JOIN
    calls c1
    USING (PhoneNumber)
WHERE
    c1.CallDate BETWEEN '$MyStartDATE' AND '$MyEndDATE'
ORDER BY has_match

edited Sep 19 '14 at 20:54

answered Sep 19 '14 at 19:46

Gervs

1,397
9
8

Is there anyway to get the rest of the data from the other columns? I know that each table has a different number of columns so perhaps that may not be possible with your solution. I will say it's a lot cleaner than what I was coming up with. – user2843577 Sep 19 '14 at 20:45
Yes, you can add 'fake' colums to the SELECT clause for the table where you have less columns. I will update my answer with an example. – Gervs Sep 19 '14 at 20:53

Multi mysql query using two different tables

2 Answers2