• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

Help with SQL Syntax (Join Issue)

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
greddin
Asked:
greddin
1 Solution
 
simonpaul64Commented:
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
 
greddinAuthor Commented:
Thanks for the help! Works great now.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now