Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2014
    Posts
    8

    Unanswered: Joining 1 Table To 2

    I have 3 tables: TableA, TableB, TableC.

    I have a SELECT query that needs the following data: TableA.Field1, TableA.Field2, TableA.Field3, TableB.Field4 WHERE TableC.Field5 is between a range of values.

    I've only gotten as far as the following:

    SELECT TableA.Field1, TableA.Field2, TableA.Field3, Table4.Field4 FROM TableA
    INNER JOIN TableB ON TableA.Field1=TableB.Field6


    But to get the ability to set the criteria from TableC I need to join TableA.Field3 to TableC.Field7. How do I do that?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I am going to step you through a bit of this. It may seem a tad painful, but you will end up learning more than you realize.

    First thing, think about how you would get the following in your SELECT clause:
    Code:
    SELECT TableA.Field1, TableA.Field2, TableA.Field3, Table4.Field4, TableC.Field5
    What would your FROM clause look like?

  3. #3
    Join Date
    Dec 2014
    Posts
    8
    Quote Originally Posted by MCrowley View Post
    I am going to step you through a bit of this. It may seem a tad painful, but you will end up learning more than you realize.

    First thing, think about how you would get the following in your SELECT clause:
    Code:
    SELECT TableA.Field1, TableA.Field2, TableA.Field3, Table4.Field4, TableC.Field5
    What would your FROM clause look like?
    Hm, you have 3 tables in the SELECT so I'm really not sure what you would use as a FROM clause. Wouldn't it depend on how you are joining them?

    I'm actually starting to rethink how to structure this, and think I might have to have the criteria table be my driving force.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Quote Originally Posted by JeffGeorge
    Wouldn't it depend on how you are joining them?
    If you mean whether it is an inner or an outer join, that could matter. For this question, let's just assume everything is an inner join. You already have most of the FROM clause yourself. You just need a little bit more.

  5. #5
    Join Date
    Dec 2014
    Posts
    8
    Quote Originally Posted by MCrowley View Post
    If you mean whether it is an inner or an outer join, that could matter. For this question, let's just assume everything is an inner join. You already have most of the FROM clause yourself. You just need a little bit more.
    Do I need to add TableC to the FROM clause?

    I've done some individual testing and I know that there are 2369 records that meet the criteria in TableC. So far I haven't been able to get my query with joins to duplicate that number.

  6. #6
    Join Date
    Dec 2014
    Posts
    8
    I've revised my query to the following:

    SELECT TableC.Field5, TableC.Field7, TableB.Field4, TableA.Field2, TableA.Field3 FROM Table3
    JOIN TableA on TableC.Field5=TableA.Field1
    JOIN TableB on TableA.Field1=TableB.Field6
    WHERE TableC.Field5 >=Value1 and TableC.Field5 <=Value2
    ORDER BY TableC.Field5,TableC.Field7

    I've also done the following to get test samples:

    SELECT * FROM Table3
    WHERE TableC.Field5 >=Value1 and TableC.Field5 <=Value2 and TableC.Field7 = 'TestCriteria'

    This example returns 21 records (2369 without the last AND clause).

    However, when I add TableC.Field7 ='TestCriteria' to the main query I get 1953 records (almost 200,000 without it).

    This tells me that I'm not joining the data correctly. My final query should give me only 2369 records.

  7. #7
    Join Date
    Dec 2014
    Posts
    8
    Based on the data that is required from TableA I think that either I can't get what I with my existing query or I have to join the fields I'm joining on. The data from TableA has to somehow relate to the criteria in TableC and my current join doesn't limit it to that.

    Bottom line? No matter how many times I try to understand JOINs it just eludes me. I'm never sure what I'm supposed to be getting by the definitions.
    Last edited by JeffGeorge; 12-08-14 at 13:37.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    JOINs present a pretty difficult concept for a lot of people, so you are hardly alone at that. Now, a few questions for you:

    1) how many rows does this query (your first example) return?
    Code:
    SELECT TableA.Field1, TableA.Field2, TableA.Field3, Table4.Field4 
    FROM TableA INNER JOIN 
          TableB ON TableA.Field1=TableB.Field6
    I am assuming that the final query will return something less than this number.

    2) For TableC, is the relationship with TableA a 1-to-1 relationship, or a 1-to-many relationship?

    3) If the relationship to TableA is a 1-to-many relationship, does the final query need to return only a single instance of each TableA record, or an instance of a record from TableA for each row in TableC that satisfies the conditions?

  9. #9
    Join Date
    Dec 2014
    Posts
    8
    So as to avoid confusing myself I am going to summarize in less-generic terms what I am trying to do here.

    TableA is an Accounts Payable Ledger which holds a record for every invoice created. The ultimate goal is pull a subset of data from this table. Unfortunately, TableA does not include a date that the invoice was paid.

    TableC is a transactional table which includes payment date. The payment date is what determines which records from TableA we wish to see.

    TableB is simply an address book table which will provide the name of the vendor to the data, linked by vendor # from TableA.

    The only field I've currently found in common between TableA and TableC is also the vendor #. So what I'm trying to do is use TableC to determine which vendor #s I'm looking for based on payment date and then pull similar data from TableA. However, I've realized that TableA doesn't care about dates so when I do the joins it's just pulling the matching vendor records. I can't quite figure out how to get the specific records I want from TableA.

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Now it is becoming more clear. When you get too many records, you are in reality getting all of the transactions that happened (regardless of type) for those vendors. If your range of dates is a month, then you get all transactions that happened in that month. Now, we go back to the original question. Do you want a list of all vendors that had at least one transaction, or do you want a listing of how many transactions (and maybe their total value) for each vendor that had at least one transaction? If you want the latter, then this progresses on to the GROUP BY clause, and aggregate functions.
    Code:
    SELECT TableA.Field1, TableA.Field2, TableA.Field3, Table4.Field4, count(*)
    FROM TableA INNER JOIN 
         TableB ON TableA.Field1=TableB.Field6 INNER JOIN
         TableC on TableA.Field7 = TableC.Field7
    WHERE TableC.Field5 BETWEEN value1 AND value2
    GROUP BY TableA.Field1, TableA.Field2, TableA.Field3, Table4.Field4

  11. #11
    Join Date
    Dec 2014
    Posts
    8
    I want a list of all invoices from TableA that were paid within a date range based on dates in TableC. The problem as I see it is that TableC will return records that meet the criteria, but the only thing that TableA gets out of this is the vendor #.

    For example, I might be able to determine that TableC contains 10 payments to Vendor #1234 between Day 1 and Day 30 but TableA only knows that it's now looking for Vendor #1234, of which there might actually be 1000 invoices, with no logical relationship to determine which of those 1000 invoices those 10 payments coincide with.

    Does what I'm saying make any sense?

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    It does. And without knowing more about the tables, I may not be able to help you much further. Without being able to relate the invoices directly to the payments, you may have to go through the records by hand (which will be a chore). Do you have a vendor/programmer/support person you can ask for direction on how to relate an invoice to associated payments? Someone designed this database, and they must have had a thought about how to derive which invoices are not entirely paid off.

  13. #13
    Join Date
    Dec 2014
    Posts
    8
    Quote Originally Posted by MCrowley View Post
    It does. And without knowing more about the tables, I may not be able to help you much further. Without being able to relate the invoices directly to the payments, you may have to go through the records by hand (which will be a chore). Do you have a vendor/programmer/support person you can ask for direction on how to relate an invoice to associated payments? Someone designed this database, and they must have had a thought about how to derive which invoices are not entirely paid off.
    Thanks for your help. As I looked at this as it evolved I realized that it didn't matter how much I knew or didn't know about joins as they may not be the key to getting the data I wanted. I currently am trying to find out why they can't just use the application software that runs off this database to get the information they are looking for. So far, I haven't gotten an answer back. My guess is that they might not even know they can do it with their own software.

Posting Permissions

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