troubleshooting Question

MySQL: How to INNER JOIN a COUNT with a SELECT statement

Avatar of MattKenefick
MattKenefickFlag for United States of America asked on
Microsoft SQL ServerMySQL ServerSQL
7 Comments1 Solution26279 ViewsLast Modified:
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?
ASKER CERTIFIED SOLUTION
MacAnthony

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros