Complicated Query Trouble

[bard777]

I hope I can describe what I am trying to do here...

I have 3 tables

LC_case
LC_ID
LC_name

LC_loans
LC_LN_ID
LC_LN
LC_LN_2

LC_loss
LS_LN
LS_amt

I am passing a value from a text box called fld_lc_id for LC_ID.

I need something like:

Select LC_ID, LC_name, LC_LN, LC_LN_2, LS_LN, LS_amt
FROM <BIG FAT JOIN>
WHERE LC_LN_ID = LC_ID AND (LS_LN = LC_LN OR LS_LN = LC_LN_2)
 

What I can't figure out is how to join them. LC_case is the top category that will contain multiple LC_loans. Some of the LC_loss records will match only LC_LN and some will only match LC_LN_2.

Should this be 2 queries with one calling another (which I have no idea how to do) or should it be one query?

Hope this makes at least a little sense.

Thanks in advance.

[NeoPa]

Thank goodness for your WHERE clause. I think I now get what you're after. I assume that it's possible to have a situation where LC_LN AND LC_LN_2 both match records in LC_Loss?

This involves adding the LC_Loss table into the query twice. We use aliases to specify which of the two we're referring to.

I will set up a template which links the tables together for you. You will have to fill in the SELECT fields required later (I'll include some examples to illustrate though).

SELECT LC.LC_ID,
       LC.LC_Name,
       LN.LC_LN,
       LS1.LS_Amt,
       LN.LC_LN_2,
       LS2.LS_Amt
 
FROM ((LC_Case AS LC INNER JOIN LC_Loans AS LN
  ON   LC.LC_ID=LN.LC_LN_ID) LEFT JOIN LC_Loss AS LS1
  ON   LN.LC_LN=LS1.LS_LN) LEFT JOIN LC_Loss AS LS2
  ON   LN.LC_LN=LS2.LS_LN)