Haveing Problem With Inner Join

[LiquidSoftware]

SELECT DISTINCT
Insurance.InsuranceCode,
Insurance.InsuranceID,
FeeSchedule.InsuranceID,
FeeSchedule.ProcedureID,
FeeSchedule.Amount,
FeeSchedule.DiscountPercent,
FeeSchedule.DiscountAmount
FROM
Insurance LEFT JOIN FeeSchedule
ON Insurance.InsuranceID = FeeSchedule.InsuranceID
WHERE
(FeeSchedule.ProcedureID = rocedureID OR
FeeSchedule.ProcedureID IS NULL)
ORDER BY
Insurance.InsuranceCode

I have a table for Insurance Company info and then a Fee Schedule table.

There is a third table not used in this query with Medical Procedure information.

The FeeSchedule table links the Insurance table and the Procedure table so that every insurance company can have a fee for every procedure.

This query does not work as I desire.

I want to ALLWAYS see the complete list of insurance companies even if there is no entry in the Fee Schedule table. However with this query the LEFT JOIN only works if I have no other entry in the Fee Schedule table.

Once I add just one entry for an insurance company in the Fee Schedule table I no longer see that insurance company in my result set (unless it is the exact procedure specified in my rocedureID parameter).

Ok, I think my question is probably not really clear, but if anyone understands I would be gratefull for some help! Let me know if I can explain something further if you think you could help.

Thanks!

 

[PHV]

You may try something like this:
SELECT DISTINCT I.InsuranceCode, I.InsuranceID,
F.InsuranceID, F.ProcedureID, F.Amount, F.DiscountPercent, F.DiscountAmount
FROM Insurance I LEFT JOIN (
SELECT * FROM FeeSchedule WHERE ProcedureID = rocedureID
) F ON I.InsuranceID = F.InsuranceID
ORDER BY
I.InsuranceCode

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[LiquidSoftware]

Thanks for the reply!

I tried this query, and it returns "Invalid use of Token "Select" in line 4'...I'm using Paradox (with the BDE, for a Delphi application) right now, but will be upsizing it to Interbase, so maybye I'll be able to use it when I move to Interbase.

Anyway, do you have any idea of another way to do it? Also, I would appreciate if you can explain your query and how the subselect works for my own edification.

Thanks again!

 

[swampBoogie]

Move the restrictions to the on-clause.

SELECT DISTINCT
  Insurance.InsuranceCode,
  Insurance.InsuranceID,
  FeeSchedule.InsuranceID,
  FeeSchedule.ProcedureID,
  FeeSchedule.Amount,
  FeeSchedule.DiscountPercent,
  FeeSchedule.DiscountAmount
FROM
  Insurance LEFT JOIN FeeSchedule
    ON Insurance.InsuranceID = FeeSchedule.InsuranceID
and
  (FeeSchedule.ProcedureID = :ProcedureID OR
  FeeSchedule.ProcedureID IS NULL)
ORDER BY
  Insurance.InsuranceCode

 

[LiquidSoftware]

Sweet!

Thank you so much. I'm delivering a version as I type this, you made my day!

That was a huge help, thank you!