do i need two queries

[thompom]

Hi - have the following query that displays site.site in one column followed by target in next column and then dealcount in the last column - this works fine but if dealcount isnull then the site names arent shown - i need this so that a blank table can be shown to add data.

SELECT site.site, count(CB.dealid) AS dealcount, site.siteid, tarr.target, tarr.targetid 
FROM deal AS CB 
LEFT JOIN user ON user.userid = CB.userid 
LEFT JOIN site ON site.siteid = user.site 
LEFT JOIN (SELECT tar.target, tar.siteid, tar.targetid FROM target AS tar ) AS tarr ON tarr.siteid = user.site 
WHERE (cb.bookst BETWEEN '20080301' AND '20080631') 
GROUP BY site.siteid 
ORDER BY site.siteid

i could do this by creating a 'dummy' table filled with 0
but i would need to execute two queries [one, if RS is EOF] but this seems bad practice - the problem is because the query is FROM deal not site and i dont know how to swap them over

thanks MG

 

[r937]

the only way dealcount could be NULL is if COUNT(CB.dealid) is NULL

i just do not see how that could possibly happen (unless there are 0 rows in the CB table, but in that case your query would return 0 rows)

would you like to try stating your question a different way?

r937.com | rudy.ca

 

[thompom]

hi,

your right i didnt mean null - what i meant to say is there are no rows in CB table and query returns 0 rows

sorry MG

 

[r937]

so could you give another shot at asking your real question?

r937.com | rudy.ca

 

[thompom]

sure...

the following query displays sitesite in one column followed by tarr.target in next column and then dealcount in the last column - this works fine but if there are no records in the deal table then no sites are shown
i want the query to display all sites regardless if there are records in the deal table.

However - whilst playing around have come up with the following that 'seems' to have cracked it - doesnt look too pretty!

SELECT site.site, count(CBB.dealid) AS dealcount, site.siteid, tarr.target, tarr.targetid 
FROM site
LEFT JOIN user ON user.site = site.siteid 
LEFT JOIN (SELECT CB.dealid, CB.bookst, CB.userid FROM deal AS CB WHERE (CB.bookst BETWEEN '20080301' AND '20080631')) AS CBB ON user.userid = CBB.userid
LEFT JOIN (SELECT tar.target, tar.siteid, tar.targetid FROM target AS tar WHERE (tar.date BETWEEN '20080301' AND '20080631')) AS tarr ON tarr.siteid = user.site 
GROUP BY site.siteid 
ORDER BY site.siteid

 

[r937]

what's the relationship between tarr and user?

if a user can have multiple tarrs, your query is going to show only one of them, and there's no way to tell which one

r937.com | rudy.ca

 

[thompom]

thanks rudy - should be

LEFT JOIN (SELECT tar.target, tar.siteid, tar.targetid FROM target AS tar WHERE (tar.date BETWEEN '20080301' AND '20080631')) AS tarr ON tarr.siteid = [COLOR=#ff0000]site.siteid[/color]

 

[r937]

what's the relationship between tarr and site?

if a site can have multiple tarrs, your query is going to show only one of them, and there's no way to tell which one



r937.com | rudy.ca

 

[thompom]

hi - a site can only have 1 tarr for a given date range

looks like im sorted - thanks for your support...again ;-)