Query Help - 1

Question

Post reply

Query Help - 1

karthik M

One Orange Chip

Points: 29139
More actions
April 11, 2008 at 6:08 am

#185314

Hi,
I have one senario.
People_Number Web_Role
100 3
100 7
200 5
200 3
300 8
450 8
250 3
250 8
I want to retireve the records which have webrole 3 and 8.
Method 1:
select * into #t1 from where web_role = 3
select * into #t2 from where web_role = 8
select people_name,web_role from #t1 a,#t2 b where a.people_numbber = b.people_number
Method 2:
select people_number,web_role
from
where web_role = 3
and people_number in ( select people_number from where web_role = 8)
Any other way to achieve the same task ? Inputs are welcome !
karthik

Viewing 13 posts - 1 through 12 (of 12 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

select people_number,web_role from

where web_role = 3

union

select people_number,web_role from

where web_role = 8

this may not be the most efficient way though.

karthik M One Orange Chip Points: 29139 More actions · Answer 2

karthik M

One Orange Chip

Points: 29139

April 11, 2008 at 6:42 am

#801431

which one is the efficient way ?

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 3

steveb,

i think your query will display 200 and 250.

But the expected output is 250 only.

Please check it.

karthik

tbeadle SSCommitted Points: 1635 More actions · Answer 4

[font="Arial"]

Hello,

How about :

select people_number,web_role

into #temp1

from tableName

where web_role in ( 3, 8 )

order by people_number, web_role

Regards,

Terry

[/font]

steveb. SSC-Forever Points: 46733 More actions · Answer 5

karthikeyan (4/11/2008)
steveb,
i think your query will display 200 and 250.
But the expected output is 250 only.
Please check it.

Okay sorry i misread the question.

the examples you gave are probably the best, do some tests and check the execution plan to see what give the best performance..

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

karthikeyan (4/11/2008)
which one is the efficient way ?

Ok... I admit it... I'm being a bit nasty... A "Senior Software Engineer" should know this. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

karthik M One Orange Chip Points: 29139 More actions · Answer 7

Ok... I admit it... I'm being a bit nasty... A "Senior Software Engineer" should know this.

I know the second method is efficient.Because it doesn't use Temp tables.

Every Database developer should have some thoughts on performance.some people might have worked/faced this kind of situation,if they share their real experience then people like me will get some good food for our thought.That why i asked "which one is the efficient way?".

I hope you also come to front row to give some good,tasty and delicious food for our thought.:P

karthik

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

karthikeyan (4/14/2008)
Every Database developer should have some thoughts on performance.some people might have worked/faced this kind of situation,if they share their real experience then people like me will get some good food for our thought.That why i asked "which one is the efficient way?".

Here's another way:

SELECT a.people_number, a.web_role, d.web_role

FROM TheTable a

INNER JOIN (SELECT people_number, web_role

FROM TheTable

WHERE web_role = 8) d

ON d.people_number = a.people_number

WHERE a.web_role = 3

Karthik, the best way to learn is to experiment. How about you set up tests for the different methods which have been posted here? Then find some more! Then, not only will you never forget, but you will be able to share with others what you've found. You might well learn another useful lesson - that the most obvious way of writing a query is not always the best way: try alternatives. Every time.

Cheers

ChrisM

Junior Apprentice Software Engineer

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sergiy SSC Guru Points: 110208 More actions · Answer 9

[Code]

SELECT a.people_number

FROM TheTable a

INNER JOIN TheTable b ON d.people_number = a.people_number

WHERE a.web_role = 3 AND b.web_role = 8

GROUP BY a.people_number

[/Code]

_____________
Code for TallyGenerator

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

CREATE TABLE #TheTable (People_Number int, Web_Role int)

INSERT INTO #TheTable (People_Number, Web_Role)

SELECT 100, 3 UNION ALL

SELECT 100, 7 UNION ALL

SELECT 200, 5 UNION ALL

SELECT 200, 3 UNION ALL

SELECT 300, 8 UNION ALL

SELECT 450, 8 UNION ALL

SELECT 250, 3 UNION ALL

SELECT 250, 8

SELECT People_Number FROM (

SELECT People_Number, Web_Role

FROM #TheTable

WHERE Web_Role = 3 OR Web_Role = 8) d

GROUP BY People_Number

HAVING COUNT(*) = 2

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

karthik M One Orange Chip Points: 29139 More actions · Answer 11

Thanks Chris. I Welcome your suggestions.

I have modified your code slightly and found a new way to acheive the same task.

select People_Number,count(*)

into #Cnt

from #TheTable

group by People_Number

having count(*) = 2

select a.People_Number,a.Web_Role

into #Web_Role3

from #TheTable a,#Cnt b

where a.People_Number = b.People_Number

and a.Web_Role = 3

select a.People_Number,a.Web_Role

from #TheTable a,#Web_Role3

where a.People_Number = b.People_Number

and a.Web_Role = 8

karthik

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

If you don't like temp. tables, this is the same code using derived tables:

select a.People_Number, a.Web_Role, d.Web_Role

from #TheTable a, (select a.People_Number,a.Web_Role

from #TheTable a, (select People_Number

from #TheTable

group by People_Number

having count(*) = 2) b

where a.People_Number = b.People_Number

and a.Web_Role = 3) d

where a.People_Number = d.People_Number

and a.Web_Role = 8

It's similar to Sergiy's method but less efficient.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden