There are three tables built like a data schema. Data schema

I am trying to create a query to the List_tab table in a PHP script. First you need to select all references to a specific user. The request, as I understand it, should look like this:

SELECT * FROM list_tab WHERE user_id=$user_id

Then, for this user, you need to show all the corresponding events along with the data from the events_tab table:

 SELECT * FROM list_tab,events_tab WHERE list_tab.event_id=events_tab.event_id

How can I combine these two queries?

  • Through LEFT JOIN - Invision
  • one
    Three tables are not many. Trivial case - Sergey
  • If you simply combine, add the second condition ... where list_tab.event_id=events_tab.event_id and list_tab.user_id=$user_id . And if you need a third table, add it to the from and also in the where clause add a condition that its user_id is equal to the id from the list. And left join here seems like nothing - Mike
  • @Mike, thank you, your version worked perfectly - FatSlowpoke

2 answers 2

 SELECT * FROM list_tab, events_tab WHERE (list_tab.user_id = $user_id) AND (list_tab.events_id = events_tab.event_id) ORDER BY event_id;
  • thanks, it works the same way - FatSlowpoke

You can do the following:

 SELECT * FROM list_tab JOIN events_tab ON list_tab.event_id = events_tab.event_id WHERE list_tab.user_id = $user_id ORDER BY `date` DESC, `time` DESC