Solved

SQL Server subquery expression

Posted on 2011-09-23
8
281 Views
Last Modified: 2012-05-12
Hi Experts,

I'm using SQL 2005/2008 Express Edition and PUBS database.
 would like the following

the following LEFT join returns:

SELECT     dbo.titles.title_id, dbo.titles.title, dbo.sales.stor_id
FROM         dbo.titles LEFT OUTER JOIN
                      dbo.sales ON dbo.titles.title_id = dbo.sales.title_id

But I would like to incorporate this into:
e.g.

SELECT     dbo.titles.title_id, dbo.titles.title, (Using title_id CASE WHEN NO matching record is found in dbo.sales THEN 0 ELSE 1) AS Field1,
FROM         dbo.titles



Current results:

PC1035	But Is It User Friendly?	8042
PS1372	Computer Phobic AND Non-Phobic Individuals: Behavior Variations	7131
BU1111	Cooking with Computers: Surreptitious Balance Sheets	8042
PS7777	Emotional Security: A New Algorithm	7131
TC4203	Fifty Years in Buckingham Palace Kitchens	7067
PS2091	Is Anger the Enemy?	6380
PS2091	Is Anger the Enemy?	7066
PS2091	Is Anger the Enemy?	7067
PS2091	Is Anger the Enemy?	7131
PS2106	Life Without Fear	7131
PC9999	Net Etiquette	NULL
TC3218	Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean	7067
PS3333	Prolonged Data Deprivation: Four Case Studies	7131
PC8888	Secrets of Silicon Valley	7066
MC2222	Silicon Valley Gastronomic Treats	7896
BU7832	Straight Talk About Computers	7896
TC7777	Sushi, Anyone?	7067
BU1032	The Busy Executive's Database Guide	6380
BU1032	The Busy Executive's Database Guide	8042
MC3021	The Gourmet Microwave	7131
MC3021	The Gourmet Microwave	8042
MC3026	The Psychology of Computer Cooking	NULL
BU2075	You Can Combat Computer Stress!	7896

Results I want returned:

PC1035	But Is It User Friendly?	1
PS1372	Computer Phobic AND Non-Phobic Individuals: Behavior Variations	1
BU1111	Cooking with Computers: Surreptitious Balance Sheets	1
PS7777	Emotional Security: A New Algorithm	1
TC4203	Fifty Years in Buckingham Palace Kitchens	1
PS2091	Is Anger the Enemy?	1
PS2091	Is Anger the Enemy?	1
PS2091	Is Anger the Enemy?	1
PS2091	Is Anger the Enemy?	1
PS2106	Life Without Fear	1
PC9999	Net Etiquette	0
TC3218	Onions, Leeks, and Garlic: Cooking Secrets of Mediterranean	1
PS3333	Prolonged Data Deprivation: Four Case Studies	1
PC8888	Secrets of Silicon Valley	1
MC2222	Silicon Valley Gastronomic Treats	1
BU7832	Straight Talk About Computers	1
TC7777	Sushi, Anyone?	1
BU1032	The Busy Executive's Database Guide	1
BU1032	The Busy Executive's Database Guide	1
MC3021	The Gourmet Microwave	1
MC3021	The Gourmet Microwave	1
MC3026	The Psychology of Computer Cooking	0
BU2075	You Can Combat Computer Stress!	1

Open in new window

0
Comment
Question by:Jimmy_inc
  • 4
  • 3
8 Comments
 
LVL 5

Expert Comment

by:eridanix
ID: 36585484
Hi, try this:

SELECT     dbo.titles.title_id, dbo.titles.title, CASE WHEN dbo.sales.stor_id IS NOT NULL THEN 1 ELSE 0 END as Field1
FROM         dbo.titles LEFT OUTER JOIN
                      dbo.sales ON dbo.titles.title_id = dbo.sales.title_id
0
 

Author Comment

by:Jimmy_inc
ID: 36585622
thanks but I might be joining to many other different tables, this was just an example, can it be done? or should I try work with your example?
0
 
LVL 12

Expert Comment

by:Ramkisan Jagtap
ID: 36585627
Hi,

SELECT     dbo.titles.title_id, dbo.titles.title, (CASE dbo.sales.stor_id WHEN NULL THEN 0 ELSE 1 END)as Field1
FROM         dbo.titles LEFT OUTER JOIN  dbo.sales ON dbo.titles.title_id = dbo.sales.title_id

Hope It will Help.


Regards,
Ram
0
 
LVL 5

Expert Comment

by:eridanix
ID: 36585660
I mean, that nothing prevent to use other left or inner joins in query.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Jimmy_inc
ID: 36591930
This what I found:

 CASE WHEN (SELECT COUNT(*) FROM dbo.sales WHERE dbo.sales.stor_id = dbo.titles.stor_id)>0 THEN 1 ELSE 0 END As Field1


0
 
LVL 5

Accepted Solution

by:
eridanix earned 500 total points
ID: 36591987
This is good solution, if you don't need use table dbo.sales more in whole sql query.

In other cases the solution with inner join or left join is better.

It's not error to have more joins in one query.
I don't know your database, but the query should be for example:

SELECT     T.title_id, T.title, CASE WHEN S.stor_id IS NOT NULL THEN 1 ELSE 0 END as Field1, C.name, C.surname,
                 CA.street, CA.city, CA.zip
FROM         dbo.titles T LEFT JOIN
                      dbo.sales S ON S.title_id = T.title_id INNER JOIN
                         dbo.customer C ON S.customer_id =  C.customer_id INNER JOIN
                           dbo.customer_address CA ON C.customer_id = CA.customer_id
0
 

Author Comment

by:Jimmy_inc
ID: 36926926
Sorry. I have been distracted by other things in my life.
Thanks EE
0
 

Author Comment

by:Jimmy_inc
ID: 36926934
And thanks eridanix
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Pivot Query Problem 9 31
monitor queries that use too much tempdb log 20 35
query question 13 41
SQL Backup skipping a few tables 7 28
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now