Succinct way to return logical condition as column?

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: tony@softins.co.uk - http://www.softins.co.uk
Play: tony@mountifield.org - http://tony.mountifield.org

re: Succinct way to return logical condition as column?

In article <gdq3vl$s3d$1@softins.clara.co.uk>,
Tony Mountifield <tony@softins.clara.co.ukwrote: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: tony@softins.co.uk - http://www.softins.co.uk
Play: tony@mountifield.org - http://tony.mountifield.org

re: Succinct way to return logical condition as column?

On Thu, 23 Oct 2008 15:07:33 +0000 (UTC), tony@softins.clara.co.uk
(Tony Mountifield) wrote:
That is about as good as you are going to get.

Roy Harvey
Beacon Falls, CT

re: Succinct way to return logical condition as column?

In article <dm51g4dk3hj7m75nh29n57lknuduuis6mo@4ax.com>,
Roy Harvey (SQL Server MVP) <roy_harvey@snet.netwrote:OK, thanks for the confirmation.

Cheers
Tony
--
Tony Mountifield
Work: tony@softins.co.uk - http://www.softins.co.uk
Play: tony@mountifield.org - http://tony.mountifield.org

re: Succinct way to return logical condition as column?

Tony Mountifield (tony@softins.clara.co.uk) writes: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, esquel@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