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

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!
0
sfletcher1959
Asked:
sfletcher1959
  • 6
  • 5
  • 3
2 Solutions
 
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
 
sfletcher1959Author 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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
 
sfletcher1959Author 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
 
sfletcher1959Author 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
 
sfletcher1959Author 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
 
sfletcher1959Author 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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now