June 4, 2017 at 12:03 am
Hi,
I'm trying to compare two tables, find results from T2 not in T1 with conditions.
Below is what I have:
--create table--
create table test_table1 (ID varchar(100), Supplier varchar(100), Stock nvarchar (100), Brand varchar(255),Product varchar(255), Condition varchar (255), Description varchar (255))
create table test_table2 (ID varchar(100), Supplier varchar(100), Stock nvarchar (100), Brand varchar(255),Product varchar(255), Condition varchar (255))
--inserting values in tables--
Insert into test_table1 values ('1234','S1', 'no', 'Philips', 'TV', 'New','nice')
Insert into test_table1 values ('5678','S1', 'no', 'Mitsubishi', 'TV', 'New','very nice')
Insert into test_table1 values ('9012','S2', 'yes', 'Sony','Mp3','Used','very very nice')
Insert into test_table2 values ('1234', 'S1', 'no', 'Philips', 'TV', 'New')
Insert into test_table2 values ('5678', 'S1', 'no', 'Mitsubishi', 'TV', 'New')
Insert into test_table2 values ('9012', 'S1', 'yes', 'Mitsubishi', 'TV', 'New')
Insert into test_table2 values ('101912r', 'S1', 'yes', 'Sony', 'Mp3', 'Used')
Insert into test_table2 values ('101913r', 'S2', 'yes', 'Sanyo', 'USB', 'Used')
Insert into test_table2 values ('101914r', 'S2', 'no', 'Sanyo', 'USB', 'Used')
Insert into test_table2 values ('101915r', 'S2', 'no', 'Sanyo', 'USB', 'New')
--sql query--
select t2.[id], t2.[Brand], t2.[condition]
from [dbo].[test_table2] as T2
where not exists
(select t1.[id]
from [dbo].[test_table1] as T1
where t1.[id]=t2.[id]
and t2.[brand]='sanyo' and t2.[condition]='used')
the output I am getting is:
id Brand condition
1234 Philips New
5678 Mitsubishi New
9012 Mitsubishi New
101912r Sony Used
101913r Sanyo Used
101914r Sanyo Used
101915r Sanyo New
the output I am expecting is:
id Brand condition
101912r Sony Used
101913r Sanyo Used
101914r Sanyo Used
Basically comparing table 2 against table 1, joining them both with the id and putting conditions i.e. and filtering table 2 with condiitons, t2.[brand]='sanyo' and t2.[condition]='used, so end results should be all the id's in table 2 not found in table 1, with conditions of table 2.
Thanks in advance for any help on this.
June 4, 2017 at 12:28 am
This appears to work for the data provided:SELECT *
FROM test_table2 t2
WHERE t2.ID NOT IN (SELECT ID FROM test_table1)
AND t2.condition = 'Used';
June 4, 2017 at 10:52 am
The problem with your query as it stands is the scope of your t2.[brand]='sanyo' and t2.[condition]='used'
Right now you have that as part of the WHERE clause in the NOT EXISTS subquery, when it should be in the WHERE clause of the top-level query.
With its being in the WHERE clause of the NOT EXISTS subquery, it means that the NOT EXISTS will be true not just for T2 items that don't have matching ID in T1.
It will also be true for T2 items that DO have a matching ID in T1, but have the rows excluded from the subquery's results because the brand in T2 is something other than Sanyo, or the condition in T2 is something other than used.
Just put the T2 conditions in the WHERE clause of the top-level query, not the subquery, like this:
SELECT t2.[id],
t2.[Brand],
t2.[condition]
FROM [dbo].[test_table2] as T2
WHERE t2.[brand]='sanyo'
AND
t2.[condition]='used'
AND
NOT EXISTS (
SELECT t1.[id]
FROM [dbo].[test_table1] as T1
WHERE t1.[id]=t2.[id]
)
Further, your desired results conflict with the stated conditions filtering T2.
They include a Sony item, when you said you wanted to filter on used Sanyo products. Do you actually want the Sony product included?
Cheers!
EDIT: Fixed a couple typos.
EDIT 2: Also, a general point, be very aware of the differences between NOT IN, used in pietlinden's query, and NOT EXISTS.
For a fun example of that, run pietlinden's query both before and after this insert:
INSERT INTO test_table1 VALUES (NULL,'S2', 'yes', 'Sony','Mp3','Used','very very nice')
Gail Shaw has a nice writeup on NOT IN vs NOT EXISTS at http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/.
June 4, 2017 at 3:14 pm
Thanks guys. Both of you're queries was very helpful as well as the explanation...really appreciate it!
June 6, 2017 at 1:14 pm
Could you post a "not in" version of the query you wrote please?
Thx
June 6, 2017 at 2:37 pm
Jacob may not have seen your reply. Here's what you need (all credit to him for doing the heavy lifting):
SELECT t2.[id],
t2.[Brand],
t2.[condition]
FROM [dbo].[test_table2] as T2
WHERE t2.[brand]='sanyo'
AND
t2.[condition]='used'
AND
t2.[id] NOT IN (
SELECT t1.[id]
FROM [dbo].[test_table1] as T1
WHERE t1.[id]=t2.[id]
)
June 8, 2017 at 8:53 pm
Thank You Michael & all.
So I tried the Not In, but it returned null values, this is because the original data had hardcoded 'null'. so the workaround I found was to use in the where clause NULLIF (t2.[new condition], 'null') is not null, as i wanted to not return null values, and that seemed to work. gibing expected result
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply