Solved

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

Posted on 2008-10-22
9
222 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
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!

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

627 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