Solved

Help with SQL Syntax (Join Issue)

Posted on 2011-03-22
2
284 Views
Last Modified: 2012-05-11
I need some help wtih the following SQL query. It works find if there is at least one row/result in each of the joined tables. I discovered that on the first two joins dbo.objects_geography and dbo.object_geography can have nulls on a given o.id in the WHERE clause.

How do I fix this so that even if dbo.objects_geography and dbo.object_geography have nulls in them, it will still return the rest of the data?

I'm thinking I need to modify my joins, but realize I need some help in this area.

Thanks.
SELECT o.id, o.title, o.display_date, o.medium, o.dimensions, o.credit_line, o.invno, 
og2.country, og2.city, m.filename, m.primary_display
FROM dbo.objects o
INNER JOIN dbo.objects_geography og1 ON o.id = og1.object_id 
INNER JOIN dbo.object_geography og2 ON og2.id = og1.geography_id
INNER JOIN dbo.objects_media om ON o.id = om.object_id
INNER JOIN dbo.media m ON om.media_id = m.id
WHERE o.id = 4250893

Open in new window

0
Comment
Question by:greddin
[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
2 Comments
 
LVL 5

Accepted Solution

by:
simonpaul64 earned 500 total points
ID: 35194028
SELECT o.id, o.title, o.display_date, o.medium, o.dimensions, o.credit_line, o.invno,
og2.country, og2.city, m.filename, m.primary_display
FROM dbo.objects o
LEFT OUTER JOIN dbo.objects_geography og1 ON o.id = og1.object_id
LEFT OUTER JOIN  dbo.object_geography og2 ON og2.id = og1.geography_id
INNER JOIN dbo.objects_media om ON o.id = om.object_id
INNER JOIN dbo.media m ON om.media_id = m.id
WHERE o.id = 4250893
0
 

Author Closing Comment

by:greddin
ID: 35194134
Thanks for the help! Works great now.
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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. …
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

726 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