How to Join with 3 tables in Mysql

Asked

Viewed 25 times

-1

I have 3 tables in my database, which are:

Table user:

id name
1 User 1
2 User 2

Table pack:

id name id_user
1 Package 1 1
2 Package 2 1

id_user is the foreign key of the table with reference to the table user.

Table order:

id name id_user id_pack
1 Request 1 1 1
2 Request 2 2 null
3 Request 3 1 2
4 Request 4 1 2
5 Request 5 1 1

id_user and id_pack are the foreign key of the table with reference to the table user and for the table pack.

How can I mount an SQL code to display the packages (table pack) containing the requests (table order) that are linked to the user of id 1?

I tried the following code:

select `u`.*, `p`.*, `o`.* from `user` as `u`
inner join `pack` as `p` on `u`.`id` = `p`.`id_user` 
inner join `order` as `o` on `p`.`id` = `o`.`id_pack`
WHERE `u`.`id` = 1
mysql
  • 1

    Try this way:;select "u".*, "p".*, "o".* 
 from "pack" as "p"
 inner join "order" as "o" on "p"."id" = "o"."id_pack"
 inner join "user" as "u" on "p"."id_user" = "u"."id" 
 WHERE "p"."id_user" = 1

  • @Clarckmaciel the result returned empty.

  • It would be interesting if you had the structure created on a site like http:/sqlfiddle.com/. This way we could simulate the command. Create table structures, make data Inserts for testing.

  • @Clarckmaciel already I will arrange and update here.

  • You have two tables that have the field id_user, you want to filter based on which user? Table user pack or the table order?

  • I managed to put there and return the result, the strange thing is that in my database nothing returns. See the link: http://sqlfiddle.com/#! 9/7e91e8/1

  • Let’s go continue this discussion in chat.

Show 2 more comments

1 answer

0


Perform the query as follows:

select `u`.*, `p`.*, `o`.*    
  from `pack` as `p`   
 inner join `order` as `o` on `p`.`id` = `o`.`id_pack`   
 inner join `user` as `u` on `p`.`id_user` = `u`.`id`    
 WHERE `p`.`id_user` = 1

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.