RE: Double Joins

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Tom Tait (tomtait_at_excite.com)
Date: 02/12/04 

Date: Thu, 12 Feb 2004 11:56:06 -0800

Pik,
   You have to nest queries to get this done. One top level query will pull it all together for you. Have one query called qryClient with the SQL Statement:
     SELECT invoice_id,client_name FROM invoices INNER JOIN clients ON invoices.client_id=clients.client_id
and another query called qryConsign with the SQL:
     SELECT invoice_id,Consign_name FROM invoices INNER JOIN clients ON invoices.client_id=clients.Consign_id
     
Then add both of those queries into another query called "qryFinalCall". Join the two queries on the clientid. Now you are set.

     ----- Pikabu wrote: -----
     
     I have an invoices database with three fields:
     
     Invoice_id Integer
     Client_id Integer
     Consignee_id Integer
     
     I also have a clients database with two fields:
     
     Client_id Integer
     Client_name char(100)
     
     Both the client_id and the consignee_id refer to records of the
     clients table (clients and consignees are stored in the clients
     table), so for each invoice I have two related records in the clients
     table.
     
     The problem I have is how to retrieve the invoice_id and the name of
     both the client and the consignee using a single query.
     
     I can use the following statement to get the invoice id and the client
     name, but how do I retrieve the consignee name in the same select?
     
     SELECT invoice_id,client_name FROM invoices INNER JOIN clients ON
     invoices.client_id=clients.client_id
     
     Thanks for any help!
     
     Luis
     
     
     

Relevant Pages

  • Re: How To Steamline My Queries
    ... Just select a month in the listbox, and the query will "read" ... > different queries which return anniversary dates. ... > FROM Clients ...
    (microsoft.public.access.formscoding)
  • Re: SQL wizards
    ... You can make queries with subqueries if you ... produces a count of all clients already attending the class by session ... FROM bookings INNER JOIN ON bookings.Session = count.Session ... i suspect these could be condensed into one simply query - but i cant ...
    (microsoft.public.access.queries)
  • Re: Summarizing records in one table that match a different table
    ... the report. ... using "Language" as the example (i had to add a field to my Clients test ... i created a report based on the query with the joins ...
    (microsoft.public.access.reports)
  • SQL wizards
    ... I'm quite good at queries, but i'm crap at agregate queries - I'm ... produces a count of all clients already attending the class by session ... FROM bookings INNER JOIN ON bookings.Session = count.Session ... i suspect these could be condensed into one simply query - but i cant figure ...
    (microsoft.public.access.queries)
  • Re: SP3 Inventory causing 100% CPU issues
    ... This new class added in SP3 collects auto run software information ... This class only collects auto run software information. ... can use wbemtest to find out which query caused the problem. ... at 100% cpu for hours causing the clients to be unusable since ...
    (microsoft.public.sms.admin)