Merging two queries

Databases
1

I’m having a difficult time merging these queries to where I get the correct results. I need to get a result whether a user has commented on a post, voted on a post, or both. The first query gets all of the comments, the second all of the votes.

SELECT 
	usr_avatar,
	usr_uname,
	com_comment 
FROM 
	posts_comments PC, 
	users U 
WHERE 
	U.usr_id = PC.com_usr_id 
AND 
	PC.com_post_id = 13 
ORDER BY 
	PC.com_id DESC

SELECT 
	vote_usr_id 
FROM 
	posts_votes 
WHERE 
	vote_usr_id = 2 
AND 
	vote_post_id = 13

CREATE TABLE IF NOT EXISTS `posts_comments` (
  `com_id` int(11) unsigned NOT NULL auto_increment,
  `com_post_id` int(11) unsigned NOT NULL,
  `com_usr_id` int(11) unsigned NOT NULL,
  `com_comment` varchar(255) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`com_id`),
  KEY `com_usr_id` (`com_usr_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=27 ;

ALTER TABLE `posts_comments`
  ADD CONSTRAINT `posts_comments_ibfk_1` FOREIGN KEY (`com_usr_id`) REFERENCES `users` (`usr_id`);
  
CREATE TABLE IF NOT EXISTS `posts_votes` (
  `vote_post_id` int(10) unsigned NOT NULL,
  `vote_usr_id` int(10) unsigned NOT NULL,
  `vote_date_added` datetime NOT NULL,
  PRIMARY KEY  (`vote_post_id`,`vote_usr_id`),
  KEY `vote_date_added` (`vote_date_added`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
2

this is gonna be really hard to do, because the posts_comments table is missing a date column!!

:slight_smile:

3

okay, then :slight_smile: :slight_smile: :slight_smile:

add that column to the SELECT clause in the first of the two subselects in the UNION, and add a similar column from the posts_votes table to the SELECT clause of the second subselect in the same ordinal position, then add the ORDER BY clause at the end, using the column name from the first subquery

4

Not anymore! :slight_smile:

CREATE TABLE IF NOT EXISTS posts_comments (
com_id int(11) unsigned NOT NULL auto_increment,
com_post_id int(11) unsigned NOT NULL,
com_usr_id int(11) unsigned NOT NULL,
com_comment varchar(255) collate utf8_unicode_ci NOT NULL,
com_date_added datetime NOT NULL,
PRIMARY KEY (com_id),
KEY com_usr_id (com_usr_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf

8_unicode_ci;

–
– Constraints for dumped tables

–
– Constraints for table posts_comments

ALTER TABLE posts_comments
ADD CONSTRAINT posts_comments_ibfk_1 FOREIGN KEY (com_usr_id) REFERENCES users (usr_id);

5

I believe you got it! :slight_smile:

i ran the query and it looks like that’s exactly what I was looking for. I’ll perform more tests but initially it looks perfect. thank you! :slight_smile:

6

no, i wanted this –

swoosh       tgavin      11111
swoosh       tgavin      green
swoosh       tgavin      test
swoosh       tgavin      dfdfd
swoosh       tgavin      5566
swoosh       tgavin      6677
swoosh       tgavin      ~voted~
logo         todd        ~voted~

" … as in the sample rows we’ve been looking at"

:slight_smile:

7

oh, wait, i think i see it now… you want a UNION ?

SELECT u.usr_avatar
     , u.usr_uname
     , pc.com_comment 
  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~' 
  FROM posts_votes AS pv
INNER
  JOIN users AS u 
    ON u.usr_id = pv.vote_usr_id
 WHERE pv.vote_post_id = 13 

usr_avatar   usr_uname   com_comment
swoosh       tgavin      11111
swoosh       tgavin      green
swoosh       tgavin      test
swoosh       tgavin      dfdfd
swoosh       tgavin      5566
swoosh       tgavin      6677
swoosh       tgavin      ~voted~
logo         todd        ~voted~
8

is this what you want?

INSERT INTO `posts_comments` (`com_id`, `com_post_id`, `com_usr_id`, `com_comment`) VALUES
(11, 13, 2, 'green'),
(12, 13, 2, 'test'),
(13, 13, 2, 'dfdfd'),
(14, 13, 2, '5566'),
(15, 13, 2, '6677'),
(16, 13, 2, '7878'),
(17, 13, 2, '8989'),
(19, 13, 2, 'Yeah, I''d say this qualifies you as a loser.'),
(20, 13, 3, 'Loser!'),
(24, 13, 2, 'adfsdsf'),
(25, 13, 2, 'dfdfdf'),
(26, 13, 2, 'ddddddd');

INSERT INTO `posts_votes` (`vote_post_id`, `vote_usr_id`, `vote_date_added`) VALUES
(13, 2, '2010-08-28 14:58:25'),
(13, 3, '2010-08-28 15:04:35');
9

could you please reformat this desired output to show the data for post 13 and user 2 as in the sample rows we’ve been looking at

10

Right, and the post’s information is what I’m concerned about. I want all of the comments and votes that are attached to each post so I can display them in a list, like so.

user1 said: “banana”
user2 said: “orange”
user 3 voted for this
user 4 voted for this
user 4 said: “pear”

11

no, because the votes aren’t applied to the comments, they are applied to the post

suppose you have a table for families that live together in a house, and you have a query that lists every member of each family

and suppose that you also have another table that says whether the house has a tv or not

then if you combine them in one query, it would return…

smith, dad     yes the house has a tv
smith, mom     yes the house has a tv
smith, junior  yes the house has a tv
jones, dad     no the house doesn't have a tv
jones, mom     no the house doesn't have a tv
jones, twin1   no the house doesn't have a tv
jones, twin2   no the house doesn't have a tv

see? whether the house has a tv belongs to the family, and therefore it ~should~ be the same on every member of the family

same with your comments – each time you comment on a post, there is an additional row in the comments table, and whether the post has your vote ~should~ be the same on all your comments

12

I supplied the actual tables in my original post. :slight_smile:

Those are the columns I wanted, yes. And returning all the comments is what I wanted as well. However, as you can see in the INSERT statements there’s only two votes, by two different users. So, shouldn’t that only make two rows = ‘yes’ and the rest = ‘no’ for the voting results? So the majority should say ‘no’, not ‘yes’.

13

okay, that’s great

i had to mock up a users table…

CREATE TABLE users
( usr_id INTEGER 
, usr_avatar VARCHAR(9)
, usr_uname VARCHAR(37)
);
INSERT INTO users VALUES
 (1, 'clock'  , 'r937'   )
,(2, 'swoosh' , 'tgavin' )
,(3, 'photo'  , 'todd'   )
;

when i then ran my query against your data, here’s what i got –

com_comment   usr_avatar  usr_uname  voted
6677          swoosh      tgavin     yes
5566          swoosh      tgavin     yes
dfdfd         swoosh      tgavin     yes
test          swoosh      tgavin     yes
green         swoosh      tgavin     yes
11111         swoosh      tgavin     yes

first of all, i’d like to confirm that this is, in fact, what you said you wanted –

I want the user’s comment (com_comment), their avatar (usr_avatar), username (usr_uname) and to know if they voted or not.
so the query produces all the comments for post 13, right?

there is only one post, but there are 6 comments on it

so naturally, the “yes” is going to appear on each line, because it depends on the post, not on the comment

if you add pc.com_post_id to the SELECT clause, you will see that the post id repeats on each line too

does that explain it a bit better?

14 
INSERT INTO `posts_comments` (`com_id`, `com_post_id`, `com_usr_id`, `com_comment`) VALUES
(1, 13, 2, '11111'),
(2, 11, 2, 'dfdfdfdfdfdfdf'),
(3, 11, 2, 'fsfdsfs'),
(4, 11, 2, 'dddddddd'),
(5, 11, 2, '44444444'),
(6, 11, 2, '5555'),
(7, 11, 2, '66666'),
(8, 11, 2, '77777'),
(9, 11, 2, '88888'),
(10, 9, 2, 'cool video. if you''re a dork.'),
(11, 13, 2, 'green'),
(12, 13, 2, 'test'),
(13, 13, 2, 'dfdfd'),
(14, 13, 2, '5566'),
(15, 13, 2, '6677');

INSERT INTO `posts_votes` (`vote_post_id`, `vote_usr_id`, `vote_date_added`) VALUES
(13, 2, '2010-08-28 14:58:25'),
(11, 2, '2010-08-28 14:59:54'),
(5, 2, '2010-08-28 15:00:59'),
(13, 3, '2010-08-28 15:04:35');
15

have you ever done a mysqldump? or do you perhaps use a front-end tool like phpmyadmin or heidisql or something similar, and done an export operation?

i am looking for you to generate the INSERT statements that will actually contain the data for some of the rows

16

You want the result of the query?

17

no, just the INSERT statements for some rows of data, so that i can confirm the relationships between the columns

18

Adding votes

INSERT IGNORE posts_votes (
	vote_post_id,
	vote_usr_id,
	vote_date_added
) VALUES (
	".$post_id.",
	".$usr_id.",now()
)

Adding comments

INSERT INTO posts_comments (
	com_post_id,
	com_usr_id,
	com_comment
) VALUES (
	".$post_id.",
	".$_SESSION['usr']['id'].",
	'".$com_comment."'
)
19

could you please dump the rows involved here?

i’m not sure i understood the table relationships properly and i’d like to see the actual rows in the tables

20

Thanks Rudy. Unfortunately everything is coming back as ‘yes’. There’s 13 comments and 1 vote for this post. That query gave me 13 total (instead of 14), all listed as voting.