Conditional Join

Based on the Company_id I need to either Inner Join or skip an Inner Join (no join at all). If the Company_id is 999999999 I don't want to Inner Join to the Division table but if it is anything else I do.

I am new to SQL and I haven't seen any examples of this type of join statement. Would it be a CASE statement of some kind?

CASE When Company_id=999999999 THEN Inner Join Division on oh.Company_id = division.Company_id and oh.Division_id = division.Division_id ELSE 1=1 END


Thanks!
sfletcher1959VPAsked:
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.

HoggZillaCommented:
This is in your ON statement with a LEFT JOIN. In other words, grab all rows from oh and include data for division if..
LEFT JOIN Division ON oh..Company_id = division.Company_id and oh.Division_id = division.Division_id
0
sdstuberCommented:
Sorry, no such thing.  You're either joined or you're not. You can do an outer join and then filter the results somehow.  Or you could build 2 queries.  One with join and one without and union them together
0
sfletcher1959VPAuthor Commented:
Hello HoggZilla,

I don't even want the join to Division to occur if the Company_id = 999999999.

oh is the FROM (main table).

Thanks!
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

HoggZillaCommented:
The problem is that you do not know the CompanyID until each row is encountered. So based on each row, can you Join or not Join? No, not possible. Your options are:
1. Use a LEFT JOIN which will return NULLS for the Division columns if the Company_ID = 999999999
2. Use a CURSOR to loop through the main table and perform a different SQL statement based on each row.
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
sdstuberCommented:
what is it you're trying to do?  what are your tables, data and expected results.

First, there is no such thing as a conditional join so trying to pursue the conditions to make it work doesn't help
Second, I'm sure it's not necessary.  Your description so far is pretty vague but it doesn't sound like you would need anything special anyway
0
HoggZillaCommented:
SQL
SELECT MainTable.Company_ID, Division.Division_ID
FROM MainTable
   LEFT JOIN Division
   ON MainTable.Company_ID = Division.Company_ID
   AND MainTable.Division_ID = Division.Division_ID
   AND MainTable.Company_ID <> 999999999

Open in new window

0
sfletcher1959VPAuthor Commented:
If the Company_id is NOT 999999999 then I want to enforce the link (INNER Join) if it is  equal to 999999999 then there will NOT be a corresponding row in the Division table and I don't need to enforce the link (I don't care that there is not a corresponding row).

Thanks!
0
sdstuberCommented:
You're describing a technical condition you're trying to achieve.  And it's not possible.

Reading between the lines, I think HoggZilla is on the right track with an outerjoin, but you need to explain what is the real functionality you want?  Please post some sample data and expected output.
0
sfletcher1959VPAuthor Commented:
I don't think I could be more specific as to what I am trying to achieve. I think the answer is to conditionally pass in my entire SQL statement. I can do this one level up from where I need this SQL statement in my application.


Thanks!
0
sdstuberCommented:
what I mean is,  quit trying to explain the problem in terms of SQL operations.  A conditional SQL join simply doesn't exist.

So, instead, explain what you want to happen.  Pretend you only know the data and don't know what a select statement is.  Now explain the problem.

You want data from A and lookup of data from B.  Illustrate with some sample data.
What do you have and what do you want?


0
sfletcher1959VPAuthor Commented:
sdstuber,


What would be the syntax for the two queries with a UNION?



Business Case #1:  One of our own Employees is logged in looking at data across all customers (we don't have divisions)

Business Case #2: One of our Customers is logged in looking at ONLY their data by Division (Customers have divisions)
0
sdstuberCommented:
select col1,col2,col3 from yourtable
where id != 999999999
union
select col1,col2,col3 from yourtable inner join your_other_table
on somecolumn=someothercolumn
where id = 999999999
0
sdstuberCommented:
note, the UNION with join and non-join is effectively the same thing as an outerjoin
0
sfletcher1959VPAuthor Commented:
I don't think that will work in this case. I believe you are right in that two queries are needed; one for our employees and one for customers. I should be able to pass the right query into the process step based on customer_id.

Thanks!
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.