CODEWITHSUNDEEP
mysqlsqlselectjoin
Manish Sahu
Manish Sahu

Reputation: 325

Query to fetch detail of user

This is my query which generates following table

SELECT user_messages.messageid,user_messages.message,
user_messages.sentby,user_messages.visibility,

(
   SELECT GROUP_CONCAT(  `post_images`.`image_id` SEPARATOR ';')
    FROM  `post_images`
    JOIN user_messages
  ON `post_images`.`messageid` =user_messages.messageid
) AS  `Image_post_id`,
(
   SELECT GROUP_CONCAT(  `post_images`.`small_pic_path` SEPARATOR ';')
    FROM  `post_images`
    JOIN user_messages
  ON `post_images`.`messageid` =user_messages.messageid
) AS Small_pic_path,
(
  SELECT count(*) FROM likes
  WHERE element_id=user_messages.messageid
  )AS Total_Likes

FROM user_messages
WHERE user_messages.userid='1';

Sql table

Here I want to include detail of sentby which is id in smsusers table. I want details fname,lname from smsusers table,and small_pic_path from profile_pic table. How to fetch Details.

Upvotes: 0

Views: 52

Answers (1)

John Woo
John Woo

Reputation: 263723

SELECT  user_messages.messageid,
        user_messages.message,
        user_messages.sentby,
        user_messages.visibility,
        (
            SELECT  GROUP_CONCAT(  `post_images`.`image_id` SEPARATOR ';')
            FROM    `post_images`
                    JOIN user_messages
                        ON `post_images`.`messageid` =user_messages.messageid
        )   AS  `Image_post_id`,
        (
            SELECT GROUP_CONCAT(  `post_images`.`small_pic_path` SEPARATOR ';')
            FROM  `post_images`
            JOIN user_messages
                ON `post_images`.`messageid` =user_messages.messageid
        ) AS Small_pic_path,
        (
            SELECT count(*) 
            FROM likes
            WHERE element_id=user_messages.messageid
        ) AS Total_Likes, 
        smsusers.*  -- select the columns you want to show
FROM    user_messages
        INNER JOIN smsusers 
        ON user_messages.SENTBY = smsusers.id
WHERE   user_messages.userid= '1';

To further gain more knowledge about joins, kindly visit the link below:

Upvotes: 1

Related Questions