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
GROUP BY pid
)b ON b.pid = p.id
ORDER BY p.enddate ASC
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?