Join 3 tables 4 times

[The Little Guy]

I have 3 tables I need to join together:

- contestants

- votes

- vs

 

the table contestants has two fields that I need:

- cont1

- cont2

 

the table votes has a count of all the votes that I want to do the grouping with

 

and table vs has the title where id = cont1 or id = cont2

 

So... Here is what I would like to return:

 

- total

- vsid

- cont1 title

- cont2 title

 

Here is what I have

SELECT COUNT(*) as total, c1.vid, vs1.vsid, c1.title as title1, c2.title as title2
FROM
contestants c1 JOIN vs vs1 ON(c1.id = vs1.cont1)
JOIN contestants c2 JOIN vs vs2 ON(c2.id = vs2.cont2)

GROUP BY vs1.vsid LIMIT 100

[The Little Guy]

Okay, the follow query works other than the total column:

 

SELECT COUNT(*) as total, vs.vsid, c1.title as title1, c2.title as title2

FROM
vs LEFT JOIN contestants c1 ON(vs.cont1 = c1.id)
LEFT JOIN contestants c2 ON(vs.cont2 = c2.id)

GROUP BY vs.id

 

Somehow I need to add the table votes in there, and it should total the vsid column. I should get 3 rows back, with the totals column as follows:

 

5, 1, 2

 

right now they are all 1's

[fenway]

You can't use GROUP BY and those title fields together.