CODEWITHSUNDEEP
sqlsql-servert-sqljoin
eirishainjel
eirishainjel

Reputation: 600

Having problems with SQL Joins

Table A

enter image description here

Table B

enter image description here

I tried to use LEFT OUTER JOIN but it seems not working..

enter image description here

I want the query to extract all data from Table A with 0 as average score if there is no data yet for the specified parameter. Meaning, in Figure 3, it should have shown ID 2 with 0 on s. Can anyone help me figure out the solution?

Upvotes: 0

Views: 46

Answers (1)

BateTech
BateTech

Reputation: 6496

You have the table names switched in the join. To keep all of Table A then it needs to be the table listed on the left side of the left join. Also anything that you want to only affect the output of table B, and not filter the entire results, should be moved to the left join on clause. Should be:

SELECT a.id, 
       Avg(Isnull(b.score, 0)) AS s 
FROM   a 
        LEFT OUTER JOIN b 
                     ON a.id = b.id 
                        AND b.kind = 'X' 
GROUP  BY a.id

Upvotes: 1

Related Questions