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

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
LVL 1
jwfranklinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jamesguCommented:
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
brad2575Commented:
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
brad2575Commented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

jwfranklinAuthor Commented:
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
brad2575Commented:
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
jwfranklinAuthor Commented:
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
jamesguCommented:
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
jorgesv13Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jwfranklinAuthor Commented:
Excellent, thanks for your solution worked straight away.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.