Succinct way to return logical condition as column?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Tony Mountifield
    #1

    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@mountifiel d.org - http://tony.mountifield.org
    Tags: None
    • Tony Mountifield
      #2
      Re: Succinct way to return logical condition as column?

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

        Comment

        • Roy Harvey (SQL Server MVP)
          #3
          Re: Succinct way to return logical condition as column?

          On Thu, 23 Oct 2008 15:07:33 +0000 (UTC), tony@softins.cl ara.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

            Comment

            • Tony Mountifield
              #4
              Re: Succinct way to return logical condition as column?

              In article <dm51g4dk3hj7m7 5nh29n57lknuduu is6mo@4ax.com>,
              Roy Harvey (SQL Server MVP) <roy_harvey@sne t.netwrote:
              On Thu, 23 Oct 2008 15:07:33 +0000 (UTC), tony@softins.cl ara.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: tony@softins.co .uk - http://www.softins.co.uk
              Play: tony@mountifiel d.org - http://tony.mountifield.org

                Comment

                • Erland Sommarskog
                  #5
                  Re: Succinct way to return logical condition as column?

                  Tony Mountifield (tony@softins.c lara.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, esquel@sommarsk og.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

                    Comment

                    Previous template Next
                    Working...