Multi-Table, Multi-WHERE and SELECT MySQL query

Question

So, I am trying to select some data from 4 tables using a query I have attempted to throw together.

SELECT *
FROM cards
LEFT JOIN cards_viewers ON cards.card_id = cards_viewers.card_id
  (SELECT *
   FROM folders
   WHERE folder_id = cards.card_folderID)
  (SELECT user_firstName,
          user_lastName,
          user_avatar
   FROM user_data
   WHERE user_id = cards_viewers.user_id)
WHERE cards_viewers.user_id = '.$u_id.'
ORDER BY cards.card_lastUpdated DESC

Basically, the query selects data from the four tables depending on the user_id in table user_data. I have attempted to initially fetch all data from the tables cards, and cards_viewers, and have went on to use this data to select values from the other tables (user_data and folders).

The query is wrong, I know that. I have learnt the majority of basic MySQL, but I am still struggling with more complex queries like the one I am trying to write now. What query can I use to select the data I want?

Links to any documentation to parts of queries would prove very useful in helping me learn how to create queries in future, rather than just relying on StackOverflow.

Many thanks.

Is that what you are looking for? To join all the four tables? — Asur, Feb 23 '16 at 16:09

score 1 · Accepted Answer · edited May 23 '17 at 12:00

You don't need "MULTI-WHERE" but multiple joins, you just need to keep doing joins until you get the tables you need.
Here's an example:

SELECT *
FROM cards LEFT JOIN cards_viewers 
ON cards.card_id = cards_viewers.card_id
LEFT JOIN folders
ON folders.folder_id = cards.card_folderID
LEFT JOIN user_data
ON user_id = cards_viewers.user_id
WHERE cards_viewers.user_id = '.$u_id.'
ORDER BY cards.card_lastUpdated DESC

To custom the fields you want to get just change * for the name of the field being careful about ambiguous column naming.
For further information check MySql Joins. Hope this helped you :)

Multi-Table, Multi-WHERE and SELECT MySQL query

1 Answers1