Solved

SQL Server subquery expression

Posted on 2011-09-23
8
282 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

919 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

19 Experts available now in Live!

Get 1:1 Help Now