MattKenefick
asked on
MySQL: How to INNER JOIN a COUNT with a SELECT statement
I have two tables. One has a bunch of listings.. One has a bunch of votes.. or views or whatever based on Table A.
So Table A has a bunch of listings with Unique ID Numbers.
Table B has information about who voted on table A.
So there can be 4 entries in Table B, and if they all have the "id" of a corresponding id from Table A.. That means the listing in Table A has 4 votes to it.
TABLE A ( listings )
id title description params visible
A1 "Title" "Description" "Params" 1
A2 "Title2" "Description" "Params" 1
A3 "Title3" "Description" "Params" 1
TABLE B ( votes )
uniqID id name browser date
1 "A1" "Matt" "Mozilla" 12/12/12
2 "A1" "JohN" " IE" 10/12/08
3 "A2" "Greg" "Safari" 10/10/10
This shows that A1 listing has two votes..
I wrote a few queries that sort of worked but not totally. I want to get back
id , title, description, params, visible, count( from table b ) per listing
I was using variations of:
SELECT p.id, p.worktitle, p.enddate, p.views, b.allBids
FROM postings p
INNER JOIN (
SELECT pid, COUNT(*) AS allBids
FROM bids
GROUP BY pid
)b ON b.pid = p.id
WHERE p.createdby='4'
ORDER BY p.enddate ASC
LIMIT 0,30
The actual thing I'm using it for is Posts and Bids on those posts.
But that query only returns Posts that have Bids on them. So if a post doesn't have any bids on it, it won't show up with that query. I need everything to show up even if it doesn't have bids, it should say 0.
Anyone have any ideas without separating into two queries?
So Table A has a bunch of listings with Unique ID Numbers.
Table B has information about who voted on table A.
So there can be 4 entries in Table B, and if they all have the "id" of a corresponding id from Table A.. That means the listing in Table A has 4 votes to it.
TABLE A ( listings )
id title description params visible
A1 "Title" "Description" "Params" 1
A2 "Title2" "Description" "Params" 1
A3 "Title3" "Description" "Params" 1
TABLE B ( votes )
uniqID id name browser date
1 "A1" "Matt" "Mozilla" 12/12/12
2 "A1" "JohN" " IE" 10/12/08
3 "A2" "Greg" "Safari" 10/10/10
This shows that A1 listing has two votes..
I wrote a few queries that sort of worked but not totally. I want to get back
id , title, description, params, visible, count( from table b ) per listing
I was using variations of:
SELECT p.id, p.worktitle, p.enddate, p.views, b.allBids
FROM postings p
INNER JOIN (
SELECT pid, COUNT(*) AS allBids
FROM bids
GROUP BY pid
)b ON b.pid = p.id
WHERE p.createdby='4'
ORDER BY p.enddate ASC
LIMIT 0,30
The actual thing I'm using it for is Posts and Bids on those posts.
But that query only returns Posts that have Bids on them. So if a post doesn't have any bids on it, it won't show up with that query. I need everything to show up even if it doesn't have bids, it should say 0.
Anyone have any ideas without separating into two queries?
That should work, but I think he wants to do a left join instead of an inner join since he wants the ones without a corresponding vote record too. Does that sound right?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try changing the INNER JOIN to a LEFT OUTER JOIN. Then your results should include the posts that aren't in bids. allBids would be NULL on those posts that weren't in bids. If you'd rather have 0, change the beginning of your code to:
SELECT p.id, p.worktitle, p.enddate, p.views,
IsNull (b.allBids, 0) as allBids
FWIW, if you wanted to use hernst42's idea, you'd need to make the same change to the JOIN.
James
SELECT p.id, p.worktitle, p.enddate, p.views,
IsNull (b.allBids, 0) as allBids
FWIW, if you wanted to use hernst42's idea, you'd need to make the same change to the JOIN.
James
ASKER
MacAnthony, that worked great!!
Thanks!
---
Hernst:
Yours threw an error because of the b.allBids in the select statement before it was defined.
SELECT p.id, p.worktitle, p.enddate, p.views, b.allBids, count( * ) AS allBids
FROM postings p
INNER JOIN bids b ON ( b.pid = p.id )
WHERE p.createdby = '4'
GROUP BY p.id
ORDER BY p.enddate ASC
LIMIT 0 , 30
MySQL said:
#1054 - Unknown column 'b.allBids' in 'field list'
---
James:
I feel bad because I should have awarded you points too!
Your LEFT OUTER JOIN method on my query worked. :( I awarded them too soon I'm sorry. It does work and thanks a lot for the input!!
I guess I was closer than I thought :)
I should study the differences between JOINs huh?
Thanks!
---
Hernst:
Yours threw an error because of the b.allBids in the select statement before it was defined.
SELECT p.id, p.worktitle, p.enddate, p.views, b.allBids, count( * ) AS allBids
FROM postings p
INNER JOIN bids b ON ( b.pid = p.id )
WHERE p.createdby = '4'
GROUP BY p.id
ORDER BY p.enddate ASC
LIMIT 0 , 30
MySQL said:
#1054 - Unknown column 'b.allBids' in 'field list'
---
James:
I feel bad because I should have awarded you points too!
Your LEFT OUTER JOIN method on my query worked. :( I awarded them too soon I'm sorry. It does work and thanks a lot for the input!!
I guess I was closer than I thought :)
I should study the differences between JOINs huh?
ASKER
For future readers.. this is the query I ended up with:
SELECT p.id, p.worktitle, p.enddate, p.views, IF(ISNULL(b.allBids),0,b.a llBids) AS total
FROM postings p
LEFT OUTER JOIN (
SELECT pid, COUNT(*) AS allBids
FROM bids
GROUP BY pid
)b ON b.pid = p.id
WHERE p.createdby='4'
ORDER BY p.enddate ASC
LIMIT 0,30
SELECT p.id, p.worktitle, p.enddate, p.views, IF(ISNULL(b.allBids),0,b.a
FROM postings p
LEFT OUTER JOIN (
SELECT pid, COUNT(*) AS allBids
FROM bids
GROUP BY pid
)b ON b.pid = p.id
WHERE p.createdby='4'
ORDER BY p.enddate ASC
LIMIT 0,30
Yeah, you were close. And the differences between the types of JOIN is kind of important. :-)
I haven't used MySQL, but the basic difference in MS SQL is that an INNER JOIN only includes rows found in both sides of the join, whereas the LEFT OUTER JOIN will include things that are not found in the right side (the SELECT that gets the counts). For those rows, the columns that would have come from the right side (b.allBids) will be null, which, of course, is where the IsNull test comes in.
Anyway, glad I could help.
James
I haven't used MySQL, but the basic difference in MS SQL is that an INNER JOIN only includes rows found in both sides of the join, whereas the LEFT OUTER JOIN will include things that are not found in the right side (the SELECT that gets the counts). For those rows, the columns that would have come from the right side (b.allBids) will be null, which, of course, is where the IsNull test comes in.
Anyway, glad I could help.
James
SELECT p.id, p.worktitle, p.enddate, p.views, b.allBids, count(*) as allBids
FROM postings p
INNER JOIN bids b on (b.id = p.id )
WHERE p.createdby='4'
GROUP BY p.id
ORDER BY p.enddate ASC
LIMIT 0,30