CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Phill Healey
Phill Healey

Reputation: 3180

Combine SELECT statements. Help Needed

I have the following two T-SQL statements that I really need to combine:

    SELECT Aircraft.Id AS AircraftID, AircraftManufacturers.Name, AircraftModels.ModelName, 
    Aircraft.ModelSuffix, Aircraft.ImageFileName, Aircraft.Year, Aircraft.SerialNo, 
    Locations.DescriptionForSite, Aircraft.Description, Aircraft.Description2, 
    Aircraft.InfoWebAddress, Aircraft.ImageDescription, Advertisers.Id AS AdvertisersID, 
    Advertisers.Name AS AdvertisersName, Aircraft.AircraftDataId, Aircraft.ForSale, Aircraft.ForLease, 
    Aircraft.TTAF, Aircraft.ReSend, Aircraft.ReSendReason, Aircraft.Registration, Aircraft.AdType,
    Aircraft.HasAlternateImage, Aircraft.AlternateImageDescription, 
    Aircraft.Price, AircraftModels.AircraftType, Advertisers.CurrentEMagLink, Aircraft.CurrentEMagLink, 
    Aircraft.Email, Aircraft.IsSold, Aircraft.SoldDate, Aircraft.DateAdded, Aircraft.ExtendedDetails, 
    Aircraft.LastUpdateDate, Aircraft.ImageCount, Aircraft.ContactTelephone, AircraftModels.id, Advertisers.IsPremiumAdvertiser,
    Aircraft.Location, Advertisers.ContactTelephone As AdvertisersTelephone, Aircraft.LastUpdateDate, Aircraft.EndDate, Aircraft.VideoLink
    FROM (((Aircraft 
    INNER JOIN Advertisers ON Aircraft.AdvertiserId = Advertisers.Id) 
    INNER JOIN AircraftModels ON Aircraft.AircraftModelId = AircraftModels.Id) 
    INNER JOIN AircraftManufacturers ON AircraftModels.ManufacturerId = AircraftManufacturers.Id) 
    INNER JOIN Locations ON Aircraft.LocationId = Locations.Id
    JOIN iter$simple_intlist_to_tbl(@ids) i ON AircraftModels.id = i.number
    WHERE (Aircraft.IsActive=1 AND Advertisers.IsActive=1 AND (Aircraft.EndDate>=@Date OR Aircraft.EndDate Is Null) AND Locations.DescriptionForSite LIKE @Location)
    OR (Advertisers.IsActive=1 AND Aircraft.IsSold=1 AND Aircraft.SoldDate>=@Date2 AND Locations.DescriptionForSite LIKE @Location)
    ORDER BY Advertisers.IsPremiumAdvertiser ASC, Aircraft.DateAdded DESC, Aircraft.ListPosition DESC, 
    Aircraft.LastUpdateDate, AircraftManufacturers.Name, AircraftModels.ModelName, Aircraft.ModelSuffix, 
    Aircraft.Id DESC

and

    SELECT TOP (1) dbo.Addresses.Email, dbo.Addresses.Contact, dbo.Addresses.Telephone1
    FROM dbo.AdvertiserAddressLink 
    INNER JOIN dbo.Addresses ON dbo.AdvertiserAddressLink.AddressId = dbo.Addresses.Id
    WHERE (dbo.AdvertiserAddressLink.AdvertiserId = 'AdvertisersID') <--see above 
    AND (dbo.Addresses.AddressType = 1 OR dbo.Addresses.AddressType = 0)
    ORDER BY dbo.Addresses.AddressType DESC, dbo.Addresses.Sequence

Upvotes: 0

Views: 149

Answers (1)

Martin Smith
Martin Smith

Reputation: 453898

It looks like you want to do TOP 1 in the second query based on a correlated parameter from the first?

You can use APPLY for this.

A simple example.

SELECT t.name, ca.name
FROM sys.tables t
CROSS APPLY (SELECT TOP 1 * 
             FROM sys.columns c 
             WHERE c.object_id=t.object_id 
             ORDER BY name) ca

Upvotes: 2

Related Questions