Need help with Select Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need help with Select Statement

Author

Topic

ljp099
Yak Posting Veteran

79 Posts

Posted - 2004-02-17 : 22:08:47

I've got a table of Business Owners Information (name, city, state...). I've got another table which hold the Offers Business have placed. I want to display a list of all the Business Owner Information AND if the Business has an Offer in the database, I want to display the Offer Name.

The problem is that I dont want to do a Join where I only show Business Owners info if they have an offer in the database. I want to show all the Business owner info AND IF they have an offer, then simply display the text "YES". How do I write the SQL statement to do this (If a Biz has an offer, I insert the idBiz (primary key in Business Owner table, into the offers table)

Thanks for any help

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2004-02-17 : 22:27:13

If you only want to show the value 'Yes' then:


SELECT whatever,(CASE WHEN EXISTS(SELECT * FROM Offers WHERE idBiz = B.ID) THEN 'Yes' ELSE 'No' END) AS HasOffer FROM BusinessOwners B

To show the offer name:


SELECT whatever,(SELECT TOP 1 Name FROM Offers WHERE idBiz = B.ID) AS Offer FROM BusinessOwners B

Please note this code is technically off because it randomly chooses 1 offer for each business. If a business has more than one offer, you don't know which one you'll get.

Sarah Berger MCSD

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-02-17 : 23:57:30

Sarah is absolutely correct, you won't know which Offer Name is returned if there is more than one offer for the business. This can be easily remedied by adding an ORDER BY to the subquery.

But keep in mind that subqueries tend to get inefficient for larger recordsets since it is evaluated for each row individually...after a few hundred records, a JOIN will outperform a subquery. So if you are expecting larger volumes, you might want to consider joining the main query to a derived table instead of using a subquery.

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources