CODEWITHSUNDEEP
mysqlsql
techhunter
techhunter

Reputation: 400

Return multiple data with condition from multiple tables in mysql

I am having problem with fetching data from multiple tables with some conditions in MySQL.

I have follwing three tables:

   Like Table

   Like_id  photoID  userID
         1        1       1
         2        2       2
         3        2       1


   BookMark Table

   bookmark_id  photoID  userID
             1        1       1
             2        2       2
             3        2       1


   Users Table

   User_id  Name       Email
         1  Max B      maxb@gmailcom
         2  Tom Smith  toms@gmailcom

CONDITIONS:

At first i want to check whether there is any data from the LIKE table for the userID = 2. If there is no data it should return "false" otherwise it should return "true".

Similarly, i want to check whether there is any data from the BOOKMARK table for the userID = 2. If there is no data it should return "false" otherwise it should return "true".

Finally, i want to fetch the Name and Email from the USERS table for the userID = 2.

WANTED:

I want to achieve all these information in a SINGLE QUERY with the above mentioned conditions from these three tables.

SO FAR tried with this QUERY:

   select Like.Like_id from (Like left join Users on Like.userID = Users.User_id) 
   left join BookMark on Users.User_id = BookMark.bookmark_id 
   where Users.User_id = 2

With @Gervs suggestion:

SELECT
u.user_id,
u.name,
u.email,
(CASE WHEN ISNULL(l.user_id) THEN 'false' ELSE 'true' END) AS 'likes',
(CASE WHEN ISNULL(b.user_id) THEN 'false' ELSE 'true' END) AS 'bookmarks'
FROM 
 users u
LEFT JOIN
  likes l
  ON u.user_id = l.user_id
LEFT JOIN
 bookmarks
 ON u.user_id = b.user_id
 WHERE u.user_id = 2
 GROUP BY u.user_id

What will be the easiest but efficient single query to fetch these information? Will VIEW be a best option for these conditions?

Advanced thanks for your participation.

Upvotes: 0

Views: 173

Answers (1)

Gervs
Gervs

Reputation: 1397

You can inner join both like table and bookmark table on users table, that is if you want only users that have entries in both tables.

SELECT
    u.user_id,
    u.name,
    u.email,
    COUNT(l.user_id) likes,
    COUNT(b.user_id) bookmarks
FROM 
    users u
JOIN
    likes l
    ON u.user_id = l.user_id
JOIN
   bookmarks b
    ON u.user_id = b.user_id
WHERE u.user_id = 2
GROUP BY u.user_id

If you always want the user, just change the inner joins into left joins and likes and/or bookmarks will be zero if no entries are found

SELECT
    u.user_id,
    u.name,
    u.email,
    CASE WHEN COUNT(l.user_id) > 0 THEN 'true' ELSE 'false' END likes,
    CASE WHEN COUNT(b.user_id) > 0 THEN 'true' ELSE 'false' END bookmarks
FROM 
    users u
LEFT JOIN
    likes l
    ON u.user_id = l.user_id
LEFT JOIN
    bookmarks b
    ON u.user_id = b.user_id
WHERE u.user_id = 2
GROUP BY u.user_id

Upvotes: 2

Related Questions