Remove From My Forums

List from one table not in another

Question
0

Sign in to vote

I am trying to write a query that will give me items from a column in 1 table that are not in the column of another. i.e. I only care about the 1 column from each of the tables below. It doesn't matter what is in the other columns of either table.

Table 1 - ColumnTbl1
A
B
C
D

Table 2 - ColumnTbl2
A
C
E

So I would want B and D returned from table 1

*** Please allow me to mark threads as answered and I will, Thank you ***

Wednesday, May 13, 2009 8:43 PM

Answers

1

Sign in to vote
The predicates in the WHERE clause turn the outer join to inner join,
maybe you mean this:

SELECT item
FROM item
LEFT OUTER JOIN tpackage
ON item.item = tpackage.pkg_id
AND tpackage.product_line = 'PRESSING'
WHERE tpackage.pkg_id IS NULL;

--
Plamen Ratchev
http://www.SQLStudio.com
- Proposed as answer by Paul White (NZ)MVP Thursday, May 14, 2009 1:00 PM
- Marked as answer by ski_freak Thursday, May 14, 2009 2:42 PM
Thursday, May 14, 2009 12:50 PM

All replies

0

Sign in to vote
try this.

select a.ColumnTbl1
FROM Table1 a
LEFT OUTER JOIN Table2 b
on a.ColumnTbl1 = b.ColumnTbl2
WHERE b.ColumnTbl2 IS NULL
- Proposed as answer by sqlmania Wednesday, May 13, 2009 8:58 PM
Wednesday, May 13, 2009 8:47 PM
0

Sign in to vote

You can also use:

select c1
from t1
where not exists (select * from t2 where t2.c1 = t1.c1);

-- or

(select c1 from t1)
except
(select c1 from t2);
go

The second statement will consider NULL values. You will need to re-write the first one if you want to consider NULL values also.

AMB

Wednesday, May 13, 2009 11:50 PM
0

Sign in to vote
Here is the actual query I am working with (thanks to your help) but it is still returning rows I don't want

SELECT item FROM item LEFT OUTER JOIN tpackage ON item.item = tpackage.pkg_id WHERE tpackage.pkg_id IS not NULL AND tpackage.product_line = 'PRESSING'
I checked one of the results I got back
PF_PKG_ID_100

and it was listed in both tables.
*** Please allow me to mark threads as answered and I will, Thank you ***
Thursday, May 14, 2009 12:41 PM
1

Sign in to vote
The predicates in the WHERE clause turn the outer join to inner join,
maybe you mean this:

SELECT item
FROM item
LEFT OUTER JOIN tpackage
ON item.item = tpackage.pkg_id
AND tpackage.product_line = 'PRESSING'
WHERE tpackage.pkg_id IS NULL;

--
Plamen Ratchev
http://www.SQLStudio.com
- Proposed as answer by Paul White (NZ)MVP Thursday, May 14, 2009 1:00 PM
- Marked as answer by ski_freak Thursday, May 14, 2009 2:42 PM
Thursday, May 14, 2009 12:50 PM
0

Sign in to vote

I am ONLY getting null values for Product_line now
*** Please allow me to mark threads as answered and I will, Thank you ***

Thursday, May 14, 2009 1:00 PM
0

Sign in to vote

ski_freak,

Are you having problems marking your questions as answered?

There should be a button next to each post 'Mark As Answer' or something like that...?

Paul

Thursday, May 14, 2009 1:01 PM
0

Sign in to vote

After looking at the data more in the tpackage table there could be multiple rows with the same pkg_id. I don't know if that makes a difference in the query
*** Please allow me to mark threads as answered and I will, Thank you ***

Thursday, May 14, 2009 1:05 PM
0

Sign in to vote

Paul,

No I am not. Thanks for asking.
*** Please allow me to mark threads as answered and I will, Thank you ***

Thursday, May 14, 2009 1:14 PM
0

Sign in to vote

Can you post CREATE TABLE statements, sample data and expected results?
Your request was for items in one table that are not in the other. Why
do you expect the Product_line to be anything other than NULL, those are
the non-matching rows so it is supposed to be NULL.

--
Plamen Ratchev
http://www.SQLStudio.com

Thursday, May 14, 2009 1:14 PM
0

Sign in to vote

Paul,

No I am not. Thanks for asking.
*** Please allow me to mark threads as answered and I will, Thank you ***
Cool. It's just it says so in your signature ^^^^

:c)

Thursday, May 14, 2009 1:18 PM

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[item](
	[item] [dbo].[ItemType] NOT NULL
	
 CONSTRAINT [PK_item] PRIMARY KEY CLUSTERED 
(
	[item] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 25) ON [PRIMARY],
 CONSTRAINT [IX_item_item_lowdate] UNIQUE NONCLUSTERED 
(
	[item] ASC,
	[lowdate] ASC,
	[rcpt_rqmt] ASC,
	[RowPointer] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 25) ON [PRIMARY],
 CONSTRAINT [IX_item_level] UNIQUE NONCLUSTERED 
(
	[low_level] ASC,
	[item] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 25) ON [PRIMARY],
 CONSTRAINT [IX_item_plan] UNIQUE NONCLUSTERED 
(
	[plan_code] ASC,
	[item] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 25) ON [PRIMARY],
 CONSTRAINT [IX_item_product] UNIQUE NONCLUSTERED 
(
	[product_code] ASC,
	[item] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 25) ON [PRIMARY],
 CONSTRAINT [IX_item_RowPointer] UNIQUE NONCLUSTERED 
(
	[RowPointer] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 25) ON [PRIMARY],
 CONSTRAINT [IX_item_serial] UNIQUE NONCLUSTERED 
(
	[serial_tracked] ASC,
	[item] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 25) ON [PRIMARY],
 CONSTRAINT [IX_item_use_reorder_point] UNIQUE NONCLUSTERED 
(
	[use_reorder_point] ASC,
	[item] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 25) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING ON
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tpackage](
	[pkg_id] [dbo].[ItemType] NOT NULL,	
	[product_line] [char](20) NOT NULL,
	[active] [char](1) NULL,
	[container_type] [nvarchar](30) NULL
	
 CONSTRAINT [PK_tpackage] PRIMARY KEY CLUSTERED 
(
	[pkg_id] ASC,
	[pkg_rev] ASC,
	[product_line] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 25) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING ON

Item column in item table
PF_PKG_ID_100
PF_PKG_ID_1000
PF_PKG_ID_1001
PF_PKG_ID_1002
PF_PKG_ID_1003
PF_PKG_ID_1004
PF_PKG_ID_1005
PF_PKG_ID_1007
PF_PKG_ID_1008
PF_PKG_ID_1009

pkg_id, product line columns in tpackage table
PF_PKG_ID_100     PRESSING
PF_PKG_ID_1002   PRESSING
PF_PKG_ID_1003   PRESSING
PF_PKG_ID_1003   PRESSING
PF_PKG_ID_1004   PRESSING
PF_PKG_ID_1005 PRESSING
PF_PKG_ID_1007   PRESSING
PF_PKG_ID_1007   PRESSING

In this scenario I would only want
PF_PKG_ID_1000
PF_PKG_ID_1001
returned as they are in the item table but not in the tpackage table

I hope this helps.

*** Please allow me to mark threads as answered and I will, Thank you ***

Thursday, May 14, 2009 1:30 PM

Well now I'm confused.

Given that data, what output do you get from Plamen's query:

SELECT item
FROM item
LEFT OUTER JOIN tpackage
ON item.item = tpackage.pkg_id 
AND tpackage.product_line = 'PRESSING'
WHERE tpackage.pkg_id IS NULL;

???

Thursday, May 14, 2009 1:46 PM

0

Sign in to vote
The query that I posted returns these rows:

item
--------------------
PF_PKG_ID_1000
PF_PKG_ID_1001
PF_PKG_ID_1008
PF_PKG_ID_1009

It is two more rows that you expect, but the last two are really correct
because they do no exist in the second table.

--
Plamen Ratchev
http://www.SQLStudio.com
- Proposed as answer by Paul White (NZ)MVP Thursday, May 14, 2009 1:49 PM
Thursday, May 14, 2009 1:46 PM
0

Sign in to vote
Yes I missed that 1008 and 1009 are supposed to be returned as well.

Ok after playing some more myself. When I run both of these queries

SELECT DISTINCT item FROM item WHERE item LIKE 'PF_PKG_ID_%' SELECT DISTINCT pkg_id FROM tpackage WHERE pkg_id LIKE 'PF_PKG_ID_%'

I get 10 more rows from the item table than the tproduct table. (Those are the rows I am trying to return)

This query gives me 2 of those. I think were close (the like statement is the same as product_line = 'PRESSING' as PF stands for PRESSING)

SELECT item , product_line FROM item LEFT OUTER JOIN tpackage ON item.item = tpackage.pkg_id WHERE tpackage.pkg_id IS NULL AND item LIKE 'PF_PKG_ID_%'
*** Please allow me to mark threads as answered and I will, Thank you ***
Thursday, May 14, 2009 2:26 PM
0

Sign in to vote

OK I just compared the data side by side and 2 rows is all I am supposed to get. Thanks for all the help!
*** Please allow me to mark threads as answered and I will, Thank you ***

Thursday, May 14, 2009 2:42 PM
0

Sign in to vote

Try this:

SELECT item
FROM item
WHERE item LIKE 'PF_PKG_ID_%'
EXCEPT
SELECT pkg_id
FROM tpackage
WHERE pkg_id LIKE 'PF_PKG_ID_%';

or this:

SELECT DISTINCT item
FROM item
WHERE item LIKE 'PF_PKG_ID_%'
AND NOT EXISTS(SELECT *
FROM tpackage
WHERE item = pkg_id);

The column product_line does not make sense in the SELECT list as it
will be always NULL (it is from the table your are excluding).

--
Plamen Ratchev
http://www.SQLStudio.com

Thursday, May 14, 2009 2:42 PM

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support options

More support

Not an IT pro?

Answered by:

List from one table not in another

Question

Answers

All replies