sql help table join

Question

sql help table join

SQL_Kills

Hall of Fame

Points: 3154
More actions
November 6, 2013 at 4:01 am

#282079

Hi,
I have the following two tables
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[productOld](
[warehouse] [varchar](2) NULL,
[product] [varchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
insert into productOld values ('ZZ','ABC123')
insert into productOld values ('ZZ','ABC124')
insert into productOld values ('ZZ','ABC125')
insert into productOld values ('ZZ','ABC126')
insert into productOld values ('ZZ','ABC127')
insert into productOld values ('ZZ','ABC128')
insert into productOld values ('ZZ','ABC129')
insert into productOld values ('ZZ','ABC130')
insert into productOld values ('ZZ','ABC131')
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[productNew](
[warehouse] [varchar](2) NULL,
[product] [varchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
insert into productNew values ('ZZ','ABC123')
insert into productNew values ('ZZ','ABC124')
insert into productNew values ('ZZ','ABC125')
insert into productNew values ('ZZ','ABC126')
insert into productNew values ('ZZ','ABC127')
insert into productNew values ('ZZ','ABC128')
insert into productNew values ('ZZ','ABC129')
insert into productNew values ('ZZ','ABC130')
I expected when running the following query the product "ABC131" would return only? Not sure why this hasn't?
Select
t1.warehouse,
t2.warehouse,
t1.product,
t2.product
From productOld t1 full outer join productNew t2
on t1.warehouse = t2.warehouse
and t2.product = t2.product
where
t2.product IS NULL

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

SQL_Kills (11/6/2013)
Select
t1.warehouse,
t2.warehouse,
t1.product,
t2.product
From productOld t1 full outer join productNew t2
on t1.warehouse = t2.warehouse
and t2.product = t2.product
where
t2.product IS NULL

Is that what you meant, or a mistake?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL_Kills Hall of Fame Points: 3154 More actions · Answer 2

such an idiot, yes you have fixed it! lol! It should be the following:

Select

t1.warehouse,

t2.warehouse,

t1.product,

t2.product

From productOld t1 full outer join productNew t2

on t1.warehouse = t2.warehouse

and t2.product = t1.product

where

t2.product IS NULL