Link to home
Start Free TrialLog in
Avatar of MattKenefick
MattKenefickFlag for United States of America

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?
Avatar of hernst42
hernst42
Flag of Germany image

Why not use a simple join and group by

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
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
Avatar of MacAnthony
MacAnthony
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of James0628
James0628

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
Avatar of MattKenefick

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?
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.allBids)  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
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