Show columns from both tables when id is empty

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Show columns from both tables when id is empty

Author

Topic

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-03-04 : 10:32:48

I'm grouping 2 tables that have a one to many relationship. I want to show the amount of times a record appears with a particular foreign key in TABLE 2, but I also want to just display 3 other columns from its related TABLE 1. My dilemma is that I want to show all columns even if the count comes up empty meaning there's no id found.
Right now my query is only coming up when there is a match of ids. Do I just need to do another query to display the 3 columns from TABLE 1.

SELECT COUNT(emails.id) as emailcount, ch.btitle, ch.countup, ch.countdown
FROM emails, ch
WHERE emails.id = @parameter AND emails.chid = ch.chid
group by ch.btitle, ch.countup, ch.countdown

I want to show emailcount and the ch columns even if it doesn't show up in the emails table. Right now its showing only when chid is found in the emails table.

Hope that makes sense

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 10:37:41

[code]
SELECT COALESCE(e.emailcount,0), ch.btitle, ch.countup, ch.countdown
FROM ch
LEFT JOIN (SELECT chid,COUNT(id) as emailcount
FROM emails
WHERE id = @parameter
GROUP BY chid)e
ON e.chid = ch.chid
[/code]

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-04 : 10:38:21

[code]SELECT COUNT(emails.id) as emailcount, ch.btitle, ch.countup, ch.countdown
FROM emails right join ch
on emails.id = @parameter AND emails.chid = ch.chid
group by ch.btitle, ch.countup, ch.countdown[/code]

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-03-04 : 10:51:33

Thank you both! I want to display only if the COUNT is greater than 0? can I do this in the SQL as opposed to the server side

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 11:10:41

quote:
Originally posted by monaya

Thank you both! I want to display only if the COUNT is greater than 0? can I do this in the SQL as opposed to the server side

this is opposite to what you suggested earlier. count will be 0 only if you dont have any record in emails table

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-03-04 : 11:42:25

quote:
Originally posted by visakh16
this is opposite to what you suggested earlier. count will be 0 only if you dont have any record in emails table

Not exactly. Before, the 3 columns in TABLE 1 only show up when the id was found in TABLE 2. I need to check the values of the 3 columns, even if the id was not found which the join now allows me to do.

Then I want to check the values of all columns. The logical problem is that I want this output to display only if the count equals more than 0 AND the values of the other columns equals more than 0. Before the other columns werent even showing up if the count was 0. The idea is that the other columns may equal more than 0 even if the count is at 0 which is why I needed the columns to show up regardless of the count, but the issue now is that I only want the record to show if either the count, or any other column equals more than 0.

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-03-04 : 12:23:33

I guess another way to put it simpler is all I want to do is display the count if it has a certain value. Is that possible? Like say only display if the count is more than 3 or something? Id rather do this in the SQL than the server side language.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources