CODEWITHSUNDEEP
sqloracle11g
Jabsy
Jabsy

Reputation: 171

Join Two Tables where join may not exist

I'm having a couple of issues writing up a join statement for Oracle. I think I want to be using a left join but I'm not 100% sure.

Here are my two tables

**VisibilityTable**
Industry
ProductID
Visibility
etc

**ItemTable**
ProductID
Other Info
etc

Here's the issue. I have ~15 Industries. Each industry wants to be able to mark a product(~15000) as Public or Private based on their preferences. I have the query working for when you want to view a specific industry's products where it's set to public or private.

The issue I'm running into is after a product is marked public or private for one industry, it's not longer appearing for the industries it has yet to be marked for. This is my query so far:

SELECT v.*, NVL(b.VISIBILITY,'Not Marked') Visibility 
FROM ItemTable v 
LEFT OUTER JOIN VisibilityTable b ON b.ProductID = v.ProductID
WHERE (
(v.STATUS LIKE 'Filter' or 'Filter' LIKE 'All') AND 
(v.MODEL LIKE 'Filter' or 'Filter' LIKE 'All') AND 
(v.DUTY LIKE 'Filter' or 'Filter' LIKE 'All') AND 
(v.CERTIFICATION LIKE 'Filter' or 'Filter' LIKE 'All')
)

This works for getting me the items that are marked as "Not Marked", "Private", "Public" but I can't get it to pull back ones that haven't been marked in the selected industry.

EDIT: Added the full where clause. This is being generated dynamically and then being passed in based on values coming from a config file.

For example, ProductID 85322 has been marked Private for Industry EPGBio but when I select Industry EPGMethane it doesn't show up as 'Not Reviewed'. It shows up as marked Private.

Upvotes: 0

Views: 209

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think that is because you are filtering out the results where they don't appear in the where clause.

When you refer to the "b" table in the where clause, you implicitly change the left outer join to an inner join. (Unless you happen to be doing is null.)

Move the conditions into the on clause.

I think I finally understand. You are trying to distinguish between b.Visibility being NULL and the record not existing. For this, you need a case statement:

select v.*,
       (case when b.ProductId is null then 'Not Marked at All'
             when b.Visibility is null then 'Marked as NULL'
             else b.Visibility'
        end) as Visibility

I changed the strings that are produced to make clear what is happening. You can make them whatever you like. The key is using the condition on the join key to whether the record exists, and then looking at the value, to check whether or not the value exists.

Upvotes: 2

Related Questions