Solved

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

Posted on 2008-10-22
9
215 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
[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
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

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

Suggested Solutions

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

752 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