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.
| Author |
Topic |
|
ljp099
Yak Posting Veteran
79 Posts |
 Posted - 2004-02-18 : 22:04:53
|
| 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 |
|
|
samrat
Yak Posting Veteran
94 Posts |
Posted - 2004-02-18 : 22:11:08
|
| Try This....SELECT Name, City, State, AnythingElse, (CASE WHEN EXISTS(SELECT * FROM Offers WHERE IDBIZ = A.IDBIZ) THEN 'Yes' ELSE 'No' END) AS Offer_Status FROM BusinessOwners AI think joins gonna be more efficent then the above mentoined method. Is there a particular reason why you dont want to do a join?Samrat |
 |
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-02-19 : 09:21:44
|
It isn't that they don't want to do a JOIN but that they want an OUTER JOIN...quote:
Originally posted by ljp099
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".
Therefore the statement will something like:SELECT a.Name , a.City , a.State , max(CASE WHEN b.BusinessOwnerID is not NULL then 'Yes' ELSE NULL end) 'IsOffer'FROM BusinessOwnerTable a LEFT OUTER JOIN Offers b ON a.BusinessOwnerID = b.BusinessOwnerIDGROUP BY a.Name, a.City, a.State |
|
|
|
|
|
|
|
|
Need help with SQL Select statement