By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,150 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Complicated Query Trouble

bard777
P: 23
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:

Expand|Select|Wrap|Line Numbers
  1. Select LC_ID, LC_name, LC_LN, LC_LN_2, LS_LN, LS_amt
  2. FROM <BIG FAT JOIN>
  3. WHERE LC_LN_ID = LC_ID AND (LS_LN = LC_LN OR LS_LN = LC_LN_2)
  4.  
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.
Aug 5 '08 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,186
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).
Expand|Select|Wrap|Line Numbers
  1. SELECT LC.LC_ID,
  2.        LC.LC_Name,
  3.        LN.LC_LN,
  4.        LS1.LS_Amt,
  5.        LN.LC_LN_2,
  6.        LS2.LS_Amt
  7.  
  8. FROM ((LC_Case AS LC INNER JOIN LC_Loans AS LN
  9.   ON   LC.LC_ID=LN.LC_LN_ID) LEFT JOIN LC_Loss AS LS1
  10.   ON   LN.LC_LN=LS1.LS_LN) LEFT JOIN LC_Loss AS LS2
  11.   ON   LN.LC_LN=LS2.LS_LN)
Aug 7 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.