JOIN ISSUE

[Crazy Horse]

Hi, thanks in advance for any help

 

I have the following 2 statements which I need to combine, however I need to show all records (10) from the first statement and 0 if the count returns nothing from the second statement.

I can get them working fine and even combined working fine - except i always need to show 10 records even if count is empty (so show 0), but i only currently show 9 because one team has no count total

 

This statement gets the list of teams from one table and returns 10 records

 

SELECT teamname

FROM miteams

WHERE deptcode LIKE 'LEGSERV%' AND groupcode IS NULL

ORDER BY teamname ASC

 

This statement counts all the records from another table

 

SELECT feteamname, COUNT(fileopened)

FROM midata

WHERE feteamname NOT IN ('Bike','Golf','Pretend','Contact Centre','Legal Services','Multi Track','Small Claims','') AND fileopened >= '2008-04-01' AND fileopened <= '2008-04-30' AND scotdesc !='Yes' AND sourcename = 'ACM ULR Ltd' AND rejectedreasons !='MIC' AND (smclaim = 'No' OR (smclaim = 'Yes' AND rejectedreasons !='TBA')) AND prodclassdesc !='BIKE' AND (rejectedreasons = 'D - Reject - MIB Untraced' OR rejectedreasons = 'A - Accepted English' OR rejectedreasons LIKE '%B - Minster%')

GROUP BY feteamname

ORDER BY feteamname ASC

 

The common factor to join on is midata.feteamname and miteams.teamname

 

I have tried left joins and left outer joins but i think i need brackets in the right places

 

Hope this makes sense, thanks again

[obsidian]

Try something like this:

SELECT t.teamname, COUNT(d.fileopened)
FROM miteams t
INNER JOIN midata d ON d.feteamname = t.teamname
WHERE t.deptcode LIKE 'LEGSERV%' 
AND t.groupcode IS NULL
AND d.feteamname NOT IN ('Bike','Golf','Pretend','Contact Centre','Legal Services','Multi Track','Small Claims','')
AND d.fileopened BETWEEN '2008-04-01' AND '2008-04-30' 
AND d.scotdesc != 'Yes' 
AND d.sourcename = 'ACM ULR Ltd' 
AND d.rejectedreasons != 'MIC' 
AND (d.smclaim = 'No' OR (d.smclaim = 'Yes' AND d.rejectedreasons !='TBA')) 
AND d.prodclassdesc != 'BIKE' 
AND (d.rejectedreasons = 'D - Reject - MIB Untraced' OR d.rejectedreasons = 'A - Accepted English' OR d.rejectedreasons LIKE '%B - Minster%')
GROUP BY teamname
ORDER BY teamname ASC

 

The INNER JOIN should restrict  your records as you're wanting. Otherwise, it's practically identical to smashing your two queries together.

[Crazy Horse]

Thanks for the prompt reply!

 

I tried your suggested statement but it still only shows 9 results as before, rather than the 10 i need from miteams - any ideas?

 

 

[Crazy Horse]

I have tried your statement also with left outer join, same result - any ideas anyone?

 

Thanks