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
Solved

SQL Server subquery expression

Posted on 2011-09-23
8
284 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.

839 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