CODEWITHSUNDEEP
mysqlsqljoin
AndrewB
AndrewB

Reputation: 836

SQL Query Assistance Needed - Joining Information

I have two tables, one holds a primary key "id" and "web_id" which holds a varchar of a url, table is named "SG".

In the other table has a primary key "id", "web_id", "votetype" and "userID". Table is named "Votes".

I want to end up with a table that when I search for "www.facebook.com"

I get a single row table with this:

Column names: web_id, likes, dislikes

Where likes is votetype = 0, and dislikes is votetype = 1.

Here is what I have however I am inexperienced with Joins and the error messages don't give any information.

SELECT web_id 
FROM `SG` 
WHERE web_id="www.facebook.com" 
LEFT OUTER JOIN
  (SELECT COUNT(*) AS likes 
   FROM `Votes` WHERE web_id="www.facebook.com" AND votetype=0 
   LEFT OUTER JOIN
       (SELECT COUNT(*) AS dislikes 
        FROM `Votes`
        WHERE web_id="www.facebook.com" AND votetype=1
       )
   )

Any help with correcting my sql would be greatly appreciated! :)

Upvotes: 1

Views: 31

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

I guess you need the count of likes and dislikes of single web_id 

SELECT s.web_id ,SUM(v.votetype = 0) AS `likes` ,SUM(v.votetype = 1) AS `dislikes`
FROM `SG`  s
INNER JOIN Votes v ON (s.web_id =v.web_id )
WHERE  s.web_id='www.facebook.com'
GROUP BY  v.web_id

Upvotes: 1

Related Questions