Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How can I show whether a JOIN is successful or not in my SELECT statement

Posted on 2008-10-22
9
Medium Priority
?
229 Views
Last Modified: 2012-05-05
Hello

I have a simple LEFT JOIN SQL statement which I want to improve on so I don't have to do multiple SQL calls:

SELECT m.id, m.name
FROM modules m
LEFT JOIN file_allocations fa ON m.moduleid = fa.moduleid
WHERE fa.fileid=20

This is fine, it brings back one record because that's what's in the database, but at the moment there are 4 modules in the database. What I'd like to do is bring all 4 modules back from the database and mark each row stating whether it was a match or not, for example:

id: 1, name: news, exists: yes
id: 2, name: sport, exists: no
id: 3, name: weather, exists: no
id: 4, name: business, exists: no

In a nutshell, I'd like a way to fill the exists.

I hope I've explained that well enough.

Regards

Jonathan
0
Comment
Question by:jwfranklin
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 9

Expert Comment

by:jamesgu
ID: 22779300
SELECT m.id, m.name, case fa.moduleid is null then 'no' else 'yes' as exists
FROM modules m
LEFT JOIN file_allocations fa ON m.moduleid = fa.moduleid
WHERE fa.fileid=20
0
 
LVL 16

Expert Comment

by:brad2575
ID: 22779312
SELECT m.id, m.name, Case WHEN FA.Moduleid IS NULL Then 'NO' ELSE 'YES' END as ExistYN
FROM modules m
LEFT JOIN file_allocations fa ON m.moduleid = fa.moduleid
WHERE fa.fileid=20
0
 
LVL 16

Expert Comment

by:brad2575
ID: 22779325
using "exists" as a column alias will cause issues with SQL because it is a reserved word.  Either name it something else, or do
as [Exists]
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 1

Author Comment

by:jwfranklin
ID: 22779405
I ran brad2575's SQL but that just brings back one row, I need it to bring back all the rows from the modules table with a No next to the ones it doesn't match on.
0
 
LVL 16

Expert Comment

by:brad2575
ID: 22779491
did you take off the where clause?
WHERE fa.fileid=20

try running just this one:
SELECT m.id, m.name, Case WHEN FA.Moduleid IS NULL Then 'NO' ELSE 'YES' END as ExistYN
FROM modules m
LEFT JOIN file_allocations fa ON m.moduleid = fa.moduleid
0
 
LVL 1

Author Comment

by:jwfranklin
ID: 22779536
I did and that did produce 4 results, but I need that where clause in there to help determine whether the ExistYN column should be YES or NO.
0
 
LVL 9

Expert Comment

by:jamesgu
ID: 22779576
SELECT m.id, m.name, case fa.moduleid is null then 'no' else 'yes' as [exists]
FROM modules m
LEFT JOIN file_allocations fa ON m.moduleid = fa.moduleid
WHERE fa.moduleid is null
--for all 'no' records


SELECT m.id, m.name, case fa.moduleid is null then 'no' else 'yes' as [exists]
FROM modules m
LEFT JOIN file_allocations fa ON m.moduleid = fa.moduleid
WHERE fa.moduleid is not null
--for all 'yes' records
0
 
LVL 8

Accepted Solution

by:
jorgesv13 earned 500 total points
ID: 22780057
Try this one:

SELECT m.id, m.name, Case WHEN FA.Moduleid IS NULL Then 'NO' ELSE 'YES' END as ExistYN
FROM modules m
LEFT JOIN file_allocations fa ON m.moduleid = fa.moduleid AND fa.fileid=20
0
 
LVL 1

Author Closing Comment

by:jwfranklin
ID: 31508868
Excellent, thanks for your solution worked straight away.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

783 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