Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Location
    Dallas, TX
    Posts
    77

    Unanswered: how to write this select statement in sql?

    I have 3 tables tbl1, tbl2, tbl3
    Tbl1 contains column: fund, fund name, group (fund is PK)
    Tbl2 contains column: fund, deal, deal name (fund and deal is PK, fund is FK of tbl1)
    Tbl3 contains column: id, fund, deal, price, profit. (id is PK, but fund and deal are fk of the tbl2)

    Now, I have certain criteria value for fund name, deal name.
    I need based on these value to select the fund, deal name, price, profit. Anybody know how to write the query?

    I have written the one as following, but its not correct:
    Select tbl3.fund, tbl3.[deal name], price, profit from tbl2, tbl3
    where tbl2.fund = tbl3. fund and tbl3.deal = tbl2.deal and
    (select fund, deal from tbl1 and tbl2 where tbl1.fund = tbl2.fund and [fund name] = 'XXX" and [deal name] = 'YYY")

    Anybody know how to write this query? Thanks a lot for the help
    Last edited by huela; 10-21-04 at 16:29.

  2. #2
    Join Date
    Sep 2003
    Posts
    83
    try this:

    select tbl1.fund, tbl2.dealname, tbl3.price, tbl3.profit
    from tbl1, tbl2, tbl3
    where tbl1.fund = tbl2.fund
    and tbl1.fund = tbl3.fund
    and tbl2.deal = tbl3.deal
    and tbl1.fundname = "xxx"
    and tbl2.dealname = "yyy"

  3. #3
    Join Date
    Sep 2004
    Location
    Dallas, TX
    Posts
    77
    I use inner join to solve the problem. here is answer:



    SELECT distinct tbl3.Fund, tbl3.Deal, tbl3.[date], tbl3.Profit, tbl3.Source, tbl3.Deal, tbl_irrdeals.Fund
    FROM (tbl1 INNER JOIN tbl1 ON tbl1.Fund = tbl2.Fund)
    INNER JOIN tbl3 ON tbl1.Fund = tbl3.Fund and tbl2.deal = tbl3.deal " _
    Where fundname = xxx and dealname = yyy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •