CODEWITHSUNDEEP
mysqlsql
Prabhakaran
Prabhakaran

Reputation: 3

Two right joins

I am trying to select all records in TABLEC and its equivalent value in TABLEA or TABLEB using right join. I am using MYSQL 5.5.47.

--Table data as follows

TABLEA          TABLEB      TABLEC  
ID  FNAME       ID  MNAME   ID  LNAME
0   ANOOP       0   N       0   SINGH
1   BIMA        2   SITA    3   RAJ
4   CIMI        4   B       5   KUMAR
6   RAVI        5   A       6   D

--Using below query and trying to select all records in TABLEC and its equivalent value in TABLEA or TABLEB 

 SELECT A.FNAME, B.MNAME, C.LNAME
 FROM TABLEA AS A 
 RIGHT JOIN TABLEB AS B ON A.ID = B.ID 
 RIGHT JOIN TABLEC AS C ON C.ID = B.ID

--I am getting the following result 

ANOOP           N       SINGH                   
NULL            NULL    RAJ                 
NULL            A       KUMAR                   
***NULL***      NULL    D

The highlighted value doesn’t show the value as 'RAVI' instead it shows NULL in MYSQL 5.5.47. I tried to modify the '=' condition in second join related to C & A but still no luck. What am I doing wrong here? How do I get the value 'RAVI' in place of NULL? Any suggestion would be highly helpful.

Upvotes: 0

Views: 104

Answers (5)

Strawberry
Strawberry

Reputation: 33935

DROP TABLE IF EXISTS table_a;
DROP TABLE IF EXISTS table_b;
DROP TABLE IF EXISTS table_c;

CREATE TABLE table_a
(id INT NOT NULL PRIMARY KEY
,fname VARCHAR(12) NULL
);

INSERT INTO table_a VALUES
(0,'ANOOP'),
(1,'BIMA'),
(4,'CIMI'),
(6,'RAVI');

CREATE TABLE table_b
(id INT NOT NULL PRIMARY KEY
,mname VARCHAR(12) NULL
);

INSERT INTO table_b VALUES
(0,'N'),
(2,'SITA'),
(4,'B'),
(5,'A');

CREATE TABLE table_c
(id INT NOT NULL PRIMARY KEY
,lname VARCHAR(12) NULL
);

INSERT INTO table_c VALUES
(0,'SINGH'),
(3,'RAJ'),
(5,'KUMAR'),
(6,'D');

 SELECT a.fname
      , b.mname
      , c.lname 
   FROM table_c c 
   LEFT 
   JOIN table_a a  
     ON a.id = c.id 
   LEFT 
   JOIN table_b b 
     ON b.id = c.id;
+-------+-------+-------+
| fname | mname | lname |
+-------+-------+-------+
| ANOOP | N     | SINGH |
| NULL  | NULL  | RAJ   |
| NULL  | A     | KUMAR |
| RAVI  | NULL  | D     |
+-------+-------+-------+
4 rows in set (0.02 sec)

Upvotes: 0

faheem ahmad
faheem ahmad

Reputation: 54

  1. Use this works perfectly


    SELECT A.FNAME, B.MNAME, C.LNAME
    FROM TABLEC AS C 
    LEFT JOIN TABLEA AS A ON (A.ID = C.ID)
    LEFT JOIN TABLEB AS B ON (B.ID = C.ID)

Upvotes: 0

Karthik N
Karthik N

Reputation: 951

As you said "select all records in TABLEC and its equivalent value in TABLEA or TABLEB", so you need to join the C with A and C with B. So your need to update your query as : 

SELECT A.FNAME, B.MNAME, C.LNAME
FROM TABLEC AS C RIGHT JOIN TABLEB AS B
    ON B.ID = C.ID
RIGHT JOIN TABLEA AS A
    ON C.ID = A.ID

If you want all the record that exist in A, B and C. The NULL value will shown for the record which doesn't have the value,

SELECT  A.FNAME, B.MNAME, C.LNAME
FROM (
TABLEA AS A
LEFT JOIN TABLEB AS B ON B.ID = A.ID
)
RIGHT JOIN TABLEC AS C ON ( C.ID = B.ID
OR B.ID = NULL 
OR A.ID = C.ID ) 
WHERE 1

Upvotes: 0

Hogan
Hogan

Reputation: 70523

The problem is you don't have a table that contains all the ids. So you have to make one. Then you can join from that.

Get all the ids with this query

SELECT ID FROM TABLEA
UNION 
SELECT ID FROM TABLEB
UNION 
SELECT ID FROM TABLEC

Now we can use this query/table of ids to join the others

SELECT A.FNAME, B.MNAME, C.LNAME
FROM (
  SELECT ID FROM TABLEA
  UNION 
  SELECT ID FROM TABLEB
  UNION 
  SELECT ID FROM TABLEC
) I
LEFT JOIN TABLEA A ON I.ID = A.ID
LEFT JOIN TABLEB B ON I.ID = B.ID
LEFT JOIN TABLEC C ON I.ID = C.ID

Of course if you had another table (TABLEID) that had a list of all IDs you could use that instead of the sub-query above. It might be your model has such a table, but we won't know unless you tell us.

Upvotes: 0

Srini
Srini

Reputation: 76

You are trying to select all records in TABLEC and its equivalent value in TABLEA or TABLEB using right join. So Table A and B is joined to Table c records. So we need to use Left join(you will get all records of Table C and common records of Table A and B). More info please ref this link

SELECT 
    ifnull(A.FNAME,""), 
    ifnull(B.MNAME,""),
    ifnull(C.LNAME,"")
FROM 
    TABLEA AS A 
LEFT JOIN 
    TABLEB AS B
ON 
    A.ID = B.ID
LEFT JOIN 
    TABLEC AS C
ON 
    C.ID = B.ID

Upvotes: 0

Related Questions