CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlstored-procedures
Phill Healey
Phill Healey

Reputation: 3180

Combine 3 SQL SELECT's into 1

* UPDATE *

Sorry! I was in a bit busy and in a hurry, so didnt clear this up properly.

Essentially the 3 queuries all relate to a users details. I am updating a site for a new client, where i am restricted by the current database structure. As such my aim is to try and get all this info in one db calll rather than 3 seperate ones, as this would appear to be the most efficient way of the two.

I was hoping someone smarter than me might know of some ninja moves that could retrieve this in one go. I really should have spent more time trying this myself before posting.

Thanks for your time.

The common value is @dealerId - which is an int value.

Thanks

    SELECT TOP(100) PERCENT dbo.ReferenceItems.Title 
    FROM dbo.AdvertiserLanguageLink 
    INNER JOIN dbo.ReferenceItems 
    ON dbo.AdvertiserLanguageLink.LanguageId = dbo.ReferenceItems.Id
    WHERE (dbo.AdvertiserLanguageLink.AdvertiserId = @dealerId) 
    ORDER BY dbo.ReferenceItems.Title



    SELECT TOP (100) PERCENT dbo.AircraftTypes.AircraftTypeDescription, dbo.AircraftTypes.AircraftTypeId 
    FROM dbo.AdvertiserAircraftTypeLink 
    INNER JOIN dbo.AircraftTypes 
    ON dbo.AdvertiserAircraftTypeLink.AircraftTypeId = dbo.AircraftTypes.AircraftTypeId 
    WHERE (dbo.AdvertiserAircraftTypeLink.AdvertiserId = @dealerId) 
    ORDER BY dbo.AircraftTypes.SortSequence



    SELECT TOP (1) dbo.Addresses.Country, dbo.Addresses.Telephone1 
    FROM dbo.AdvertiserAddressLink 
    INNER JOIN dbo.Addresses 
    ON dbo.AdvertiserAddressLink.AddressId = dbo.Addresses.Id 
    WHERE (dbo.AdvertiserAddressLink.AdvertiserId = @dealerId) 
    AND (dbo.Addresses.AddressType = 1 OR dbo.Addresses.AddressType = 0) 
    ORDER BY dbo.Addresses.Sequence

Upvotes: 1

Views: 228

Answers (2)

MatBailie
MatBailie

Reputation: 86798

I'm just making an inference here, but you either...
1. Just want to make the code more concise.
2. Want the output to have a consentant signature (same fields, etc).

In case of 1., I'd leave your code as it is. Each query is different, and seperating them out using IF statements makes it readable and obvious that there are three execution paths.

The latter is often desired in the case of data-binding frameworks that automate part of the programmers job. If this is the case, I'd still leave the code with the IF statements. If you then explicitly CAST each field to the same type (such as INT, or VARCHAR(256), etc) and then ensure the same field names are used every time (such as ID, or Name, etc), then you'll get the same signature for each execution path.

EDIT:

I'm still not completely sure as to what you want to achieve and, more specifically, what issues you're facing.

If you're just trying to execute one DB call, and get back three sets of data, having a stored procedure with your 3 SELECT statements will do exactly that. Depending on how you're accessing the database, you should then be able to itterate through the three record sets.

So, I suspect that the issues you are facing are not SQL related at all, but rather in your application. What language are you using? How are you currently accessing the records set(s)? What problems are you facing when the SP returns 3 records sets? Etc, etc...

Upvotes: 1

JNK
JNK

Reputation: 65217

You can't/shouldn't combine them.

You are selecting very different data from different tables in all 3. It looks like they are completely unrelated. What would you expect the "combined" result set to look like? You are combining books, aircraft, and phone information?

What exactly is your goal? If you tell us that we can maybe give you a different (correct) path to it.

Upvotes: 5

Related Questions