1

I need to join 2 MySQL tables that will return rows if Either side of the join exists, and only one row if both side exist.

Here is a a simple example:

CREATE TABLE `a` (
  `id` INT(11) ,
  `qty` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `b` (
  `id` INT(11) ,
  `price` DECIMAL(8,2)  DEFAULT NULL,
   PRIMARY KEY (`id`)
);

INSERT  INTO `a`(`id`,`qty`) VALUES (1,1),(3,0);
INSERT  INTO `b`(`id`,`price`) VALUES (1,'10.00'),(2,'20.00');

SELECT * FROM a 
LEFT OUTER JOIN b ON a.id=b.id;
returns 1 and 3

SELECT * FROM b 
LEFT OUTER JOIN a ON a.id=b.id;
returns 1 and 2


SELECT * FROM a 
LEFT OUTER JOIN b ON a.id=b.id
  UNION
SELECT * FROM b 
LEFT OUTER JOIN a ON a.id=b.id;
returns 1, 3, 1 , 2 

SELECT * FROM a 
LEFT OUTER JOIN b ON a.id=b.id
  UNION
SELECT * FROM b 
RIGHT OUTER JOIN a ON a.id=b.id;
returns 1, 3, 1

Is this possible in MySQL to get 1,2,3 or do I have to program around it?

sdfor
  • 6,324
  • 13
  • 51
  • 61
  • So, are you just wanting to get the `id` from each table, if `id` exists in both return `id`, `price` and `qty`? – Rwd Dec 11 '13 at 22:34

1 Answers1

2

What you need is a FULL OUTER JOIN, which, unfortunately, is not implemented in MySQL. You can achieve the same with a union, however. See this answer for how to do this.

Community
  • 1
  • 1
willy
  • 1,462
  • 11
  • 12