CODEWITHSUNDEEP
mysqljoin
Grzegorz
Grzegorz

Reputation: 3608

MySQL compare 2 tables values

I got a query:

SELECT  a.Vendor, i.Vendor
FROM    whole1 a 
        LEFT JOIN whole2 i 
        ON (a.Vendor = i.Vendor) 
GROUP BY a.Vendor, i.Vedor 
LIMIT 0, 200

By default Inner JOIN is used. Left join gives me all results from left table. Same with Right join.

What I'm looking for is combination of Left and Right join, However NOT INNER Join.

I want to have a result like

AMD, NULL
ACER, NULL
NULL, INTEL
NULL, ATI

Any way to do it?

Upvotes: 1

Views: 1448

Answers (2)

Sir Rufo
Sir Rufo

Reputation: 19106

Using union with Left and Right Join and from that a select with the limit

SELECT * FROM (
  SELECT 
    a.`Vendor` v1, i.`Vendor` v2
  FROM 
    `whole1` a 
  LEFT JOIN 
    whole2 i ON (a.`Vendor` = i.`Vendor`) 
  GROUP BY 
    a.`Vendor`, i.`Vendor` 

  UNION

  SELECT 
    a.`Vendor`, i.`Vendor`
  FROM 
    `whole1` a 
  RIGHT JOIN 
    whole2 i ON (a.`Vendor` = i.`Vendor`) 
  GROUP BY 
    a.`Vendor`, i.`Vendor`
) AS whole
LIMIT 0,200

SQL Fiddle

Upvotes: 1

Grisha Weintraub
Grisha Weintraub

Reputation: 7996

You need a full outer join, which is unfortunately not implemented in MYSQL, instead you can use union of left and right join : 

SELECT a.`Vendor`, i.`Vendor`
FROM `whole1` a LEFT JOIN whole2 i ON (a.`Vendor` = i.`Vendor`) 
GROUP BY a.`Vendor`, i.`Vendor` 

UNION

SELECT a.`Vendor`, i.`Vendor`
FROM `whole1` a RIGHT JOIN whole2 i ON (a.`Vendor` = i.`Vendor`) 
GROUP BY a.`Vendor`, i.`Vendor`

Upvotes: 1

Related Questions