Need help with a query retreiving data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with a query retreiving data

Author

Topic

raghav_sai2002
Starting Member

18 Posts

Posted - 2009-11-05 : 10:07:43

HI,

i need help with a query, sample table data would look something like this

CREATE TABLE #tbl1( ID INT IDENTITY PRIMARY KEY,
ProgramID VARCHAR(12),
KeywordID VARCHAR(25),
MatchType VARCHAR(50))

INSERT INTO #tbl1(ProgramID,KeywordID,MatchType) VALUES ( '88357645361', '1683276357', 'BroadMatch')
INSERT INTO #tbl1(ProgramID,KeywordID,MatchType) VALUES ( '88357645361', '1683276357', 'ExactMatch')
INSERT INTO #tbl1(ProgramID,KeywordID,MatchType) VALUES ( '88357645361', '1683276357', 'PhraseMatch')

CREATE TABLE #tbl2( ID INT IDENTITY PRIMARY KEY,
ProgramID VARCHAR(12),
KeywordID VARCHAR(25),
MatchType VARCHAR(50))

INSERT INTO #tbl2(ProgramID,KeywordID,MatchType) VALUES ( '88357645361', '1683276357', 'BroadMatch')
INSERT INTO #tbl2(ProgramID,KeywordID,MatchType) VALUES ( '89357645362', '2683276357', 'ExactMatch')
INSERT INTO #tbl2(ProgramID,KeywordID,MatchType) VALUES ( '99357645362', '3683276357', 'ExactMatch')
INSERT INTO #tbl2(ProgramID,KeywordID,MatchType) VALUES ( '88357645361', '1683276357', 'ExactMatch')

what i need is i need to retrieve all the records from #tbl1 where "KeywordID" And "MatchType" doesn't exists in a single row in #tbl2

... expected output is '88357645361', '1683276357', 'PhraseMatch'
the third row from #tbl1

please help me out on this one ,

thank you

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-05 : 10:29:27

try this

Select KeywordID, MatchType from #tbl1
except
select KeywordID, MatchType from #tbl2

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

raghav_sai2002
Starting Member

18 Posts

Posted - 2009-11-06 : 08:10:23

hi,
thank you,
i will giv a try with that.

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-11-06 : 09:06:51

Hi Raghav,

Hope this will aslo work for you..
select a.ProgramID,a.KeywordID,a.MatchType from #tbl1 a
inner join
#tbl2 b
on a.id=b.id
where a.KeywordID <> b.KeywordID
and a.MatchType <> b.MatchType

raghav_sai2002
Starting Member

18 Posts

Posted - 2009-11-10 : 04:28:46

hi,

thank you,

i have tried with the except option.. nd it worked , i had to play with it for a while as i was getting duplicate records, but at the end i was able to solve with except.

with inner join i tried but it didnt work, i tried to join them on programid....

thank you all for your help

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-11-10 : 10:15:11

I think using exists (or not exists) would be a good option. It is something that is familiar and it translates well from your original requirements.

select 
  * 
from 
  #tbl1
where
  not exists
  (select 1 from #tbl2
   where #tbl2.KeywordID = #tbl1.KeywordID and 
     #tbl2.MatchType = #tbl1.MatchType)

An advantage of this approach over except is for the scenario that you add columns to #tbl1 and want to return those additional columns in the query.

Usually 'select *' is not a good practice in SQL code (unless it is an ADHOC query). But I think in this case it is OK, when you are referring to a temp. table.

raghav_sai2002
Starting Member

18 Posts

Posted - 2009-11-13 : 06:17:12

Hi,

Thank you,

i guess even not exists will work.. i tried it and i got the same results, for EXCEPT and NOT EXISTS.
but i just wanted to know which one is faster in terms of execution.. is it EXCEPT or NOT EXISTS.
because as soon as i deployed my SP, using EXCEPT in it the execution time increased from 3 to 30 secs, which i am expecting they will get back to me asking me to tune it.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-11-13 : 10:58:11

quote:
Originally posted by raghav_sai2002

but i just wanted to know which one is faster in terms of execution.. is it EXCEPT or NOT EXISTS.

I'm not sure which one is faster, could depend, but I think it is worth a try with NOT EXISTS.

You can add indexes to your Temp Tables. If a lot of rows get in there, that could help speed up the select statment(s). For every record in #tbl1, SQL Server has to search in #tbl2, adding the index (KeywordID, MatchType) could speed that up. Probably add that to both tables (a non-unique index).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources