?
Solved

SQL Server subquery expression

Posted on 2011-09-23
8
Medium Priority
?
287 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 5

Expert Comment

by:eridanix
ID: 36585660
I mean, that nothing prevent to use other left or inner joins in query.
0
 

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 2000 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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

800 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