Question 20736 in Mysql Question posted in
The official MySQL documentation can be found here.

3 Table JOIN to show everything – Mysql

I want a query from my 3 table, but i dont know which JOIN should I use. I want to display all of table2’s item_id and item_name, also show stat and shipped from table3 even if its NULL. Also, user_id from table1.

Table1

CREATE TABLE table1(
    id NOT NULL AUTO_INCREMENT,
    user_name varchar(255),
);

Table 2

CREATE TABLE table2(
    id NOT NULL AUTO_INCREMENT,
    item_name varchar(255),
);

Table 3

CREATE TABLE table3 (
  id int NOT NULL AUTO_INCREMENT,
  user_id int NOT NULL,
  item_id int NOT NULL,
  stat tinyint NOT NULL,
  shipped tinyint NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES table1(id),
  FOREIGN KEY (item_id) REFERENCES table2(id)
);

I tired LEFT INNER JOIN, but I only got table3.

Thank you!

Tags:

4

Answers


  1. 0

    You can start from table2 since this is the mandatory one and LEFT JOIN with the other two tables

    SELECT 
    t1.user_name,
    t2.item_name,
    t3.user_id,
    t3.item_id,
    t3.stat,
    t3.shipped
FROM table2 t2
    LEFT JOIN table3 t3 ON t2.id = t3.item_id
    LEFT JOIN table1 t1 ON t1.id = t3.user_id
    Login or Signup to reply.
  2. 0

    As you want all entries from tabl2 you should start there and add the other tables using left outer join or short left join

    SELECT 
    t1.user_name,
    t2.item_name,
    t3.user_id,
    t3.item_id,
    t3.stat,
    t3.shipped
FROM table2 t2
    LEFT JOIN table3 t3 ON t2.id = t3.item_id
    LEFT JOIN table1 t1 ON t3.user_id = t1.userid
    Login or Signup to reply.
  3. 0

    The below query starts at table2 as you need to show all items even if their status is null. Assuming that the relationship between table2 and table3 is a 1-1 then the below query will show you 1 line per item.

    SELECT T2.ID
      ,T2.Item_Name
      ,T3.Stat
      ,T3.Shipped
      ,T3.ID
      ,T1.UserName
FROM table2 T2
LEFT JOIN table3 T3
ON T3.Item_ID = T2.ID
LEFT JOIN table1 T1
ON T1.ID = T3.UserID
    Login or Signup to reply.
  4. 0

    You can use a LEFT JOIN to achieve this. The LEFT JOIN will ensure that all records from table2 are included in the result, even if there are no matching records in table3

    SELECT t1.user_id, t2.item_id, t2.item_name, t3.stat, t3.shipped
FROM table2 t2
LEFT JOIN table3 t3 ON t2.id = t3.item_id
LEFT JOIN table1 t1 ON t1.id = t3.user_id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search