loop to search for values in a table

• martina.kenny

  Grasshopper

  Points: 16

  More actions

  November 4, 2008 at 3:34 am

  #96717

  I have two tables in SQL Enterprise. I need to check if the data in the first table is contained within the second table.

  I need to take the data based on a combination of two column values in the first table and check if it is in the second table. Is a loop the best way to do this. A simple example would be appreciated.

• steveb.

  SSC-Forever

  Points: 46733

  More actions

  November 4, 2008 at 3:39 am

  #893501

  A loop is not the best way to do this, the best way would be to use a join between the two tables based upon a primary key -> foreign key relationship.

  something like

  select table1.data, table2.data

  from table1 inner join table2 on table1.pk = table2.fk

  post your table structures if you would like a better answer.

• martina.kenny

  Grasshopper

  Points: 16

  More actions

  November 4, 2008 at 3:51 am

  #893503

  The table structures are as follows

  CREATE TABLE [CustomerMapping] (

  [PKid] [int] IDENTITY (1, 1) NOT NULL ,

  [ClientNo] [int] NULL ,

  [Customer] [int] NULL ,

  ) ON [PRIMARY]

  GO

  CREATE TABLE [CustomerMapping_Stage] (

  [ClientNo] [int] NULL ,

  [Customer] [int] NULL ,

  ) ON [PRIMARY]

  GO

  I need to check if the combined value of ClientNo and Customer on the CustomerMapping_Stage table is contained within the CustomerMapping table, thanks.

• Lowell

  SSC Guru

  Points: 323522

  More actions

  November 4, 2008 at 4:03 am

  #893507

  SELECT *

  FROM CustomerMapping_Stage

  LEFT OUTER JOIN CustomerMapping

  ON CustomerMapping_Stage.ClientNo = CustomerMapping.ClientNo

  AND CustomerMapping_Stage.Customer= CustomerMapping.Customer

  --this finds what is NT in CustomerMapping YET:

  --AND CustomerMapping.PKid IS NULL

  martina.kenny (11/4/2008)

  ---

  The table structures are as follows

  CREATE TABLE [CustomerMapping] (

  [PKid] [int] IDENTITY (1, 1) NOT NULL ,

  [ClientNo] [int] NULL ,

  [Customer] [int] NULL ,

  ) ON [PRIMARY]

  GO

  CREATE TABLE [CustomerMapping_Stage] (

  [ClientNo] [int] NULL ,

  [Customer] [int] NULL ,

  ) ON [PRIMARY]

  GO

  I need to check if the combined value of ClientNo and Customer on the CustomerMapping_Stage table is contained within the CustomerMapping table, thanks.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!