Get Extra Columns From SubQuery

Question

Post reply

Get Extra Columns From SubQuery

TriggerMe

SSChampion

Points: 11752
More actions
April 8, 2008 at 7:34 pm

#188791

I need to retrieve the CustomerType using the query below, but how do you do that when I cannot reference the last subquery (The EXISTS one)?
SELECT A.CustomerID, A.SurName AS 'CurrentSurName', NULL AS 'ArchiveSurName'
FROM Customer.Customer A
WHERE NOT EXISTS (SELECT *
FROM CustomerArchive.Customer
WHERE A.CustomerID = CustomerID)
AND EXISTS (SELECT CustomerID, CustomerType
FROM Customer.CustomerMaster
WHERE A.CustomerID = CustomerID)
Kindest Regards,

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

I would suggest that you study up on JOINS..... 😀

SELECT

A.CustomerID

,A.SurName AS 'CurrentSurName'

,NULL AS 'ArchiveSurName'

FROM

Customer.Customer AS A

LEFT JOIN CustomerArchive.Customer AS b

ON a.CustomerID = b.CustomerID

INNER JOIN Customer.CustomerMaster AS m

ON a.CustomerID = m.CustomerID

AND a.CustomerType = m.CustomerType

WHERE

b.customerID IS NULL

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

TriggerMe SSChampion Points: 11752 More actions · Answer 2

I should have said this in the initial post.

I wanted to see if it was possible to retrieve the CustomerType without using JOINS.

I already have written it to use JOINS but wish to find out how to do this using Subqueries

Kindest Regards,

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 3

Yep, you should have said that. :w00t:

But as far as I know, the answer is "no" you can't do it with EXISTS.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

I am not sure why you would be trying to avoid using joins while working with a SQL Server, but I suppose you could do something rediculous like this:

SELECT A.CustomerID, A.SurName AS 'CurrentSurName', NULL AS 'ArchiveSurName'

, (SELECT TOP 1 S1.CustomerType FROM Customer.CustomerMaster S1 WHERE S1.CustomerID = A.CustomerID) AS CustomerType

FROM Customer.Customer A

WHERE NOT EXISTS (SELECT *

FROM CustomerArchive.Customer

WHERE A.CustomerID = CustomerID)

AND EXISTS (SELECT CustomerID, CustomerType

FROM Customer.CustomerMaster

WHERE A.CustomerID = CustomerID)

With a bit more work, I may be able to help you make this a little more inefficient. Possibly with some UDF's.