Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server subquery expression

Posted on 2011-09-23
8
Medium Priority
?
289 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

618 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