Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    5

    Red face Unanswered: need a query to join 3 tables

    hi everyone - wondering if anyone can help me in this: i have got 3 tables i.e
    1.Invoice
    2.Customer
    3.Agents
    and i want to generate an invoice using the info from these two tables how can i do that. the scenario is that for one invoice there can be multiple entries

    invID,CustID,AgentID,productPrice |
    1 1 1 40 |
    1 2 2 50 | this is the data for 1 invoice
    1 3 3 70 |
    i want a join query so that i can get the names of the customers and agents on behalf of their ids in the invoice table and all the info from invoice table so that it can show the result like this

    invID,CustName,AgentName,productPrice |
    1 xyz a 40 |
    1 yyy b 50 | this is the data for 1 invoice
    1 zzz c 70 |

    ================================================== =======

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select invID
         , CustName
         , AgentName
         , productPrice
      from Invoice
    inner
      join Customer
        on Invoice.CustID
         = Customer.CustID
    inner
      join Agents
        on Invoice.AgentID
         = Agents.AgentID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2005
    Posts
    5
    thank you thats what i wanted

Posting Permissions

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