CODEWITHSUNDEEP
mysqlouter-join
Chiwda
Chiwda

Reputation: 1344

MySQL Full Join with Where clause giving incorrect results

I am having a problem with getting the correct results from a (pseudo) full join. So I created a sample table to illustrate the problem. I created a test database (TestDB) with two tables as follows:

CREATE TABLE `TestDB`.`MyTable` ( `Leg1` VARCHAR(10) NOT NULL , `Leg2` VARCHAR(10) NOT NULL , `Leg3` VARCHAR(10) NOT NULL ) ENGINE = InnoDB;

CREATE TABLE `testdb`.`MyChair` ( `Back1` VARCHAR(10) NOT NULL , `Back2` VARCHAR(10) NOT NULL , `Back3` VARCHAR(10) NOT NULL ) ENGINE = InnoDB;

(:-P) and put some value in there as follows:

MyTable

MyTable

and MyChair

enter image description here

Now I use the following SQL statement:

SELECT * FROM `mychair` LEFT JOIN `mytable` ON mytable.Leg1 = mychair.Back1 
UNION
SELECT * FROM `mychair` RIGHT JOIN `mytable` ON mytable.Leg1 = mychair.Back1
WHERE mytable.Leg1 = 'A'

This should give me one row, but actually gives me two as if the condition is being ignored (see below). enter image description here

Is there some problem with my syntax?

Upvotes: 0

Views: 142

Answers (1)

Utsav
Utsav

Reputation: 8093

I think this is what you are trying to do. The last where clause is being used only for right join part, not for whole query. Use this

Select * from (
SELECT * FROM `mychair` LEFT JOIN `mytable` ON 
mytable.Leg1 = mychair.Back1 
UNION
SELECT * FROM `mychair` RIGHT JOIN `mytable` ON 
 mytable.Leg1 = mychair.Back1) t
WHERE t.Leg1 = 'A'

Upvotes: 1

Related Questions