November 4, 2008 at 3:34 am
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.
November 4, 2008 at 3:39 am
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.
November 4, 2008 at 3:51 am
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.
November 4, 2008 at 4:03 am
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 followsCREATE 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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply