Merging two queries

Databases
21

okay, that makes sense :slight_smile:

SELECT pc.com_comment 
     , u.usr_avatar
     , u.usr_uname
     , CASE WHEN pv.vote_post_id IS NULL
            THEN 'no'
            ELSE 'yes' END AS voted
  FROM posts_comments AS pc
INNER
  JOIN users AS u 
    ON u.usr_id = pc.com_usr_id 
LEFT OUTER
  JOIN posts_votes AS pv
    ON pv.vote_post_id = pc.com_post_id
   AND pv.vote_usr_id = pc.com_usr_id
 WHERE pc.com_post_id = 13 
ORDER 
    BY pc.com_id DESC
22

I’m just looking for a result so I know the user voted for this post. I just picked a name so something would be returned, as a boolean, basically.

I want the user’s comment (com_comment), their avatar (usr_avatar), username (usr_uname) and to know if they voted or not. If a value is in the posts_votes table that contains the post’s ID and the user’s ID, then I know they voted.

23

your second query doesn’t make a lot of sense

why would you combine SELECT vote_usr_id with WHERE vote_usr_id = 2? you’re only going to get the answer “2”, i.e. exactly what you fed in to the query

when you say you want “a result”, which columns, specifically, did you want the combined query to return?

24

Okay, I’ve realized that I forgot to tell you that I need to have an ORDER BY in there so that the most recent comment or vote is at the top of the list (Sorry!). I’ve Googled how to do this and, as you already know, it ain’t easy! I couldn’t figure it out. :slight_smile:

user1 voted (10:00 am)
user2 “hi everybody” (9:45 am)
user1 “oranges” (9:30 am)
user13 “hello!” (9:00 am)
user13 voted (8:45 am)

Of course, I’m not looking for the time stamp in the query, this just shows you how I want the results ordered.

Thanks again!

25

yeah, that looks fine

… well, except for the trailing commas, which i personally don’t like :wink:

26

Yeah, I’ve begun to notice that about you SQL jockeys. :wink:

Thanks SO much! :slight_smile:

27

That was like reading Chinese. :wink:

Does this look correct? It’s returning results… it’s just hard to check if they’re correct because I don’t have any dates in the pc.com_date_added table.

SELECT 
	u.usr_avatar, 
	u.usr_uname, 
	pc.com_comment, 
	pc.com_date_added
 FROM posts_comments AS pc
INNER
 JOIN users AS u 
   ON u.usr_id = pc.com_usr_id 
WHERE pc.com_post_id = 13 
UNION ALL
SELECT 
	u.usr_avatar, 
	u.usr_uname,
	'voted', 
	pv.vote_date_added
 FROM posts_votes AS pv
INNER
 JOIN users AS u 
   ON u.usr_id = pv.vote_usr_id
WHERE pv.vote_post_id = 13
ORDER BY com_date_added DESC