August 20, 2008 03:12am pdt

Member Login

Hall of Fame

Yearly

1chapmandew 1,838,691
2angelIII 1,445,073
3aneeshatti... 550,115
4mark_wills 544,656
5ScottPletc... 367,386
6ee_rlee 348,824
7dtodd 278,051
8acperkins 258,489
9EugeneZ 247,314
10Thomasian 153,038
11cs97jjm3 149,400
12appari 139,648
13matthews... 138,203
14BrandonG... 137,837
15dbaSQL 124,394
16ptjcb 119,637
17brejk 118,990
18rickchild 116,275
19momi_sa... 111,935
20bhess1 110,305
21dportas 109,530
22dqmq 108,874
23MohammedU 105,175
24asvforce 103,642
25nmcdermaid 100,768

	[x] Attachment Details

How to rewrite multiple subqueries as one statement

Zones: MS SQL Server, Databases Miscellaneous

Tags: Microsoft, SQL Server, 2000, T-SQL statement

Hello, I have a query which involves multiple IN statements. I have tried rewriting this with Joins instead but to no avail. Any ideas?

FYI - recno is the equivalent of a table's id field (increment INT) and is the PK in the table while any other field with 'recno' in its title is a FK.

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:

           
           
             DELETE 
FROM content..z_fare_code_rates
WHERE sailingrecno IN 
	(
	select	recno 
	from 	content..sailings
	where 	shiprecno IN 
		(
		select	recno 
		from 	content..ships 
		where 	cruiselinerecno IN 
			(
			select	cruiselinerecno 
			from 	content..ships 
			where 	recno IN 
				(
				select	shiprecno 
				from 	working.dbo.z_pricing
				)
			)
		)
	)
AND farecode IN 
	(
	select	farecode
	from	working.dbo.z_pricing
	)

           
         

Start your free trial to view this solution

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

9.8

Question Stats

Zone: Microsoft

Question Asked By: SWRO

Solution Provided By: BriCrowe

Participating Experts: 2

Solution Grade: A

Translate:

Loading Advertisement...

20080723-EE-VQP-34 / EE_QW_2_20070628