Tough SQL

• kknudson

  SSCarpal Tunnel

  Points: 4986

  More actions

  February 12, 2003 at 1:57 pm

  #174905

  First this is actually for a UDB DB, I support both SQL and UDB. But I really really need to develop a fancy select, and this is the best place I know to get help.

  I have 3 tables, let's call them a, b, c all related by a common key.

  I need to put out a row

  key, a data, b data, c data.

  Easy right, no here's the problem there will be varying numbers each of a/b/c from none to ???

  I don't care if the first 5 rows for a key have a, b, and c. Then No A's or B's or ...

  See the problem.

  KlK, MCSE

  
  KlK

• Greg Larsen

  SSC-Insane

  Points: 20961

  More actions

  February 12, 2003 at 2:11 pm

  #448789

  Are you looking for something like this:

  set nocount on

  create table a (keyf int, a int)

  create table b (keyf int, b int)

  create table c (keyf int, c int)

  insert into a values (1,1)

  insert into a values (2,1)

  insert into a values (3,1)

  insert into a values (5,1)

  insert into b values (1,2)

  insert into b values (4,2)

  insert into b values (2,2)

  insert into c values (1,3)

  insert into c values (4,3)

  insert into c values (5,3)

  insert into c values (6,3)

  select COALESCE (a.keyf,b.keyf,c.keyf) as jkeyf, a, b, c

  from a full join b on a.keyf=b.keyf full join c on (a.keyf=c.keyf or b.keyf=c.keyf)

  order by jkeyf

  drop table a

  drop table b

  drop table c

  Gregory Larsen, DBA

  If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

  Gregory A. Larsen, MVP