CODEWITHSUNDEEP
mysqlleft-join
Smith
Smith

Reputation: 5959

Select join tables conditionally

i have three tables A, F, R

Table A
-----
id   | type   | user_id


Table F
-----
id   | Title


Table R
-----
id   | Title

In A there is a column Type which can only contain values f or r.

Now i want to select and join the three tables conditionally, joining rows from A to R if column value Type in A is r and joining rows from A to F if column Type in A is f

I tried

SELECT a.*,f.* FROM  `A` a
INNER JOIN F ON F.type= a.type AND a.type = 'f'
INNER JOIN R ON F.type= a.type AND a.type =  'r'

I get am empty row

Upvotes: 0

Views: 47

Answers (2)

Baruch
Baruch

Reputation: 31

It would be easier to have type in f and r else you can add it before the join

DECLARE @a TABLE(id INT, type VARCHAR(1), user_id int)
DECLARE @f TABLE(id INT, title VARCHAR(2))
DECLARE @r TABLE(id INT, title VARCHAR(2))

INSERT INTO @a
SELECT 1,'r',1
UNION ALL
SELECT 2,'f',2

INSERT INTO @f
SELECT 1,'f1'
UNION ALL
SELECT 2,'f2'


INSERT INTO @r
SELECT 1,'r1'
UNION ALL
SELECT 2,'r2'

SELECT a.*,b.* 
FROM  @a a
INNER JOIN (
    SELECT *,'r' AS type FROM @r
    UNION ALL
    SELECT *,'f' AS type FROM @f
) AS B ON B.type = a.type

Upvotes: 1

DRapp
DRapp

Reputation: 48179

You are close, but need to change to a LEFT-JOIN and use coalesce... if the first value is null, it grabs from the other table... But your sample structures don't really match as you are not showing a "TYPE" column in the "F" and "R" tables.

SELECT 
      a.*,
      COALESCE( f.title, r.title ) as WhichTitle
   FROM  
      `A` a
         LEFT JOIN F 
            ON a.type = F.type
         LEFT JOIN R 
           ON a.type = R.type
   where
         a.type = 'f'
      or a.type = 'r'

However, that said, if you can also supply some sample data to show context of each table and what you expect, would help.

Upvotes: 1

Related Questions