By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,837 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Succinct way to return logical condition as column?

P: n/a
I have a table with an Id column as the primary key, and a number of other
tables that have a foreign key pointing to the first table.

I'm looking for the most efficient/elegant/succinct way to see whether
each row is actually referenced from the other tables.

My first try was:

select distinct a.id, b.id as bid, c.id as cid
from a
left join b on b.id=a.id
left join c on c.id=a.id;

which works, but is fetching a lot of rows from b and c and then discarding
them for the "distinct" qualifier.

My second try was better, but felt rather verbose:

select a.id,
case when exists(select * from b where b.id=a.id) then 1 else 0 end as bid,
case when exists(select * from c where c.id=a.id) then 1 else 0 end as cid
from a;

Is there a better or tidier way to express it?

Cheers
Tony
--
Tony Mountifield
Work: to**@softins.co.uk - http://www.softins.co.uk
Play: to**@mountifield.org - http://tony.mountifield.org
Oct 23 '08 #1
Share this Question
Share on Google+
4 Replies


P: n/a
In article <gd**********@softins.clara.co.uk>,
Tony Mountifield <to**@softins.clara.co.ukwrote:
I have a table with an Id column as the primary key, and a number of other
tables that have a foreign key pointing to the first table.

I'm looking for the most efficient/elegant/succinct way to see whether
each row is actually referenced from the other tables.

My first try was:

select distinct a.id, b.id as bid, c.id as cid
from a
left join b on b.id=a.id
left join c on c.id=a.id;

which works, but is fetching a lot of rows from b and c and then discarding
them for the "distinct" qualifier.

My second try was better, but felt rather verbose:

select a.id,
case when exists(select * from b where b.id=a.id) then 1 else 0 end as bid,
case when exists(select * from c where c.id=a.id) then 1 else 0 end as cid
from a;

Is there a better or tidier way to express it?
Just for the avoidance of doubt, b.id and c.id are not b's and c's primary
keys, but just foreign keys to a.id.

Cheers
Tony
--
Tony Mountifield
Work: to**@softins.co.uk - http://www.softins.co.uk
Play: to**@mountifield.org - http://tony.mountifield.org
Oct 23 '08 #2

P: n/a
On Thu, 23 Oct 2008 15:07:33 +0000 (UTC), to**@softins.clara.co.uk
(Tony Mountifield) wrote:
>select a.id,
case when exists(select * from b where b.id=a.id) then 1 else 0 end as bid,
case when exists(select * from c where c.id=a.id) then 1 else 0 end as cid
from a;

Is there a better or tidier way to express it?
That is about as good as you are going to get.

Roy Harvey
Beacon Falls, CT
Oct 23 '08 #3

P: n/a
In article <dm********************************@4ax.com>,
Roy Harvey (SQL Server MVP) <ro********@snet.netwrote:
On Thu, 23 Oct 2008 15:07:33 +0000 (UTC), to**@softins.clara.co.uk
(Tony Mountifield) wrote:
select a.id,
case when exists(select * from b where b.id=a.id) then 1 else 0 end as bid,
case when exists(select * from c where c.id=a.id) then 1 else 0 end as cid
from a;

Is there a better or tidier way to express it?

That is about as good as you are going to get.
OK, thanks for the confirmation.

Cheers
Tony
--
Tony Mountifield
Work: to**@softins.co.uk - http://www.softins.co.uk
Play: to**@mountifield.org - http://tony.mountifield.org
Oct 23 '08 #4

P: n/a
Tony Mountifield (to**@softins.clara.co.uk) writes:
I'm looking for the most efficient/elegant/succinct way to see whether
each row is actually referenced from the other tables.
...
select a.id,
case when exists(select * from b where b.id=a.id) then 1 else 0 end
as bid,
case when exists(select * from c where c.id=a.id) then 1 else 0 end
as cid
from a;

Is there a better or tidier way to express it?
Well, if you only want to see the non-references rows, you could do:

SELECT a.id
FROM a
WHERE NOT EXISTS (SELECT * FROM b WHERE a.id = b.id)
AND NOT EXISTS (SELECT * FROM c WHERE a.id = c.id)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Oct 23 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.