Help with a query RSS

• • Reply
  

  NoBullMan

  Participant

  1019 Points

  780 Posts

  Help with a query

  Dec 06, 2012 08:57 PM|LINK

  Hi,

  I am trying to do a seemingly simple query and I can;t get it to work (I am not database savvy, obviously!)

  I am giving a simplified version here:

  Customer table with two columns: ID1 and ID2

  IDs table with two columns: ID, Value. ID1 and ID2 are from IDs table's ID coumn.

  Problem is that I have some orphan records in IDs table; rows where the ID is not either in ID1 or ID2 columns of Customer table. I am trying to find those rows in IDs table but keep getting no rows when I know there are about 2,600 of them.

  Can someone provide me qith a query to find those records?

  • Edited by NoBullMan on Dec 06, 2012 08:58 PM
  • Edited by NoBullMan on Dec 06, 2012 08:58 PM
• • Reply
  

  AZMatt

  Star

  10648 Points

  1896 Posts

  Re: Help with a query

  Dec 06, 2012 09:32 PM|LINK

  You can use the NOT IN clause.  Here is an example (see the select statement at the end)...

  declare @idsTable table (id int)
  
  insert into @idsTable values (1)
  insert into @idsTable values (2)
  insert into @idsTable values (3)
  insert into @idsTable values (4)
  insert into @idsTable values (5)
  insert into @idsTable values (6)
  
  declare @custTable table (id1 int, id2 int)
  insert into @custTable values (1,2)
  insert into @custTable values (2,3)
  insert into @custTable values (3,4)
  
  select *
  from @idsTable
  where id not in (select id1 from @custTable) and
  	  id not in (select id2 from @custTable)

  Matt

  If this helps, please mark as Answer.
• • Reply
  

  KashifB

  Member

  503 Points

  403 Posts

  Re: Help with a query

  Dec 07, 2012 10:19 AM|LINK

  SELECT IDs.*
  FROM IDs LEFT JOIN custs ON IDs.id = custs.id1 OR IDs.id = custs.id2
  WHERE custs.id1 IS NULL OR custs.id2 IS NULL

  www.LinkToIslam.net
• • Reply
  

  NoBullMan

  Participant

  1019 Points

  780 Posts

  Re: Help with a query

  Dec 07, 2012 01:24 PM|LINK

  Kashif,

  This returns records where custs.id1 or custs.id2 ARE in IDs table. I am trying to find the records in IDs table where the ID is NOT in custs.id1 or custs.id2.

  • Edited by NoBullMan on Dec 07, 2012 01:25 PM
• • Reply
  

  NoBullMan

  Participant

  1019 Points

  780 Posts

  Re: Help with a query

  Dec 07, 2012 01:25 PM|LINK

  Thanks Matt, but this does not return anything.

• • Reply
  

  Dan Bracuk

  Contributor

  3970 Points

  1096 Posts

  Re: Help with a query

  Dec 07, 2012 01:37 PM|LINK

  NoBullMan

  Hi,

  I am trying to do a seemingly simple query and I can;t get it to work (I am not database savvy, obviously!)

  I am giving a simplified version here:

  Customer table with two columns: ID1 and ID2

  IDs table with two columns: ID, Value. ID1 and ID2 are from IDs table's ID coumn.

  Problem is that I have some orphan records in IDs table; rows where the ID is not either in ID1 or ID2 columns of Customer table. I am trying to find those rows in IDs table but keep getting no rows when I know there are about 2,600 of them.

  Can someone provide me qith a query to find those records?

  You didn't specify the db so I'll assume it's oracle.

  select distinct id from ids

  minus

  (

  select id1 from customer

  union

  select id2 from customer

  )

   

  • Marked as answer by NoBullMan on Dec 07, 2012 06:55 PM
• • Reply
  

  NoBullMan

  Participant

  1019 Points

  780 Posts

  Re: Help with a query

  Dec 07, 2012 06:55 PM|LINK

  Thanks Dan, nice and clean and worked fine.

• • Reply
  

  KashifB

  Member

  503 Points

  403 Posts

  Re: Help with a query

  Dec 08, 2012 05:40 AM|LINK

  NoBullMan

  Kashif,

  This returns records where custs.id1 or custs.id2 ARE in IDs table. I am trying to find the records in IDs table where the ID is NOT in custs.id1 or custs.id2.

  I have tried this query again and it does do what you are after. Anyway, the other solutions looks much simpler.

  www.LinkToIslam.net