?
Solved

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

Posted on 2008-10-22
9
Medium Priority
?
226 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
Linux Academy Android App Now Supports Chromecast

We have some fantastic news for our Android fans. We’re so excited to announce that the Linux Academy Android app is now available with Chromecast support. That’s right – simply download the latest update of the Linux Academy App and start casting your favorite course videos!

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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

718 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