Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2010
    Posts
    7

    Unanswered: Looking for help with an unmatched record query

    Essentially I am linking two tables, although others will be joined in. The main tables look like this:


    Table 1 (c=column)
    c1 | c2 | c3 | c4
    c1 | cA | cy | cc
    c2 | cA | cn | ch
    c3 | cB | cy | cc
    c4 | cC | cy | ch
    c5 | cD | cn | cc
    c6 | cE | cy | cc

    Table 2
    c1 | c2 | c3
    c1 | c1 | cA
    c2 | c1 | cB
    c3 | c1 | cE
    c4 | c2 | cA
    c5 | c2 | cE
    c6 | c3 | cB

    T1 has records that, if c4 = "Y" and c4 = "c" I want to include in the result. The records colored green meet those conditions.
    T1.c2 and t2.c3 are the field that links the two tables.
    I want to generate a list of the T1.c2 values that are missing from T2.c3.
    This is fairly straightforward except that T2.c3 can contain more than one occurance of value found in T1.c2. I have colored the occurances above.
    Each occurance is distinct based upon the value in T2.c2.
    So I need to create a query that asks: if I looking at the records in T2 and filter those records based on the value in T2.c2, which records from T1.c2, when they meet the conditions noted above, do not appear in T2.c3.


    The results of the query would look like this:
    T2.c2 filtered on 1: empty list
    T2.c2 filtered on 2: B
    T2.c2 filtered on 3: A E

    I have made several attempts at getting somewhere but whenever I attempt to apply a filter t2 I end up with an empty dataset. I know the answer... I just can't figure out how to ask the question.

    Hope I had better luck explaining than doing. Any help would be greatly appreciated!

    PS, if it is a greater help for me to explain what it is i am exactly up to... table one contains a list of sites that need to be scheduled in a route. Table two is the schedule... and the twist is, it contains various scenarios... so in scenario 1, site 1,2,3 are done in this order, in scenario 2, sites 1,2,3 are done in that order. In need to pull a list that tells me that, for instance, scenario 2 is missing site 3.

    Thanks a heap in advance!
    Last edited by spm; 01-13-10 at 22:25. Reason: confusion

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by spm View Post
    T1 has records that, if the values in c4 = "Y" and in c5 = "c", I want place into T2.
    your example is way too confusing

    i don't even see c5

    and first you want to "place into t2", but then you want to "generate a list of the T1 rows that are missing from T2.c3." but rows can't be missing from a column (T2.c3 is a column)

    the part that ~really~ confused me is what you said here --
    The results would look like this:
    T2.c2 =1:
    -empty list
    T2.c2 =2:
    B
    T2c2 =3:
    A
    E
    could you try your explanation again please?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2010
    Posts
    7
    Thanks for the reply r937! Wish the query results were so quick!

    The column 5 was a typo... I meant c4. My apologies for seeming to be looking to do two different things with the query as well. I will go back and try to re-phrase what I am after...which is ... The query will show me which values in T1.c2 (based on the conditions mentioned) are not in T2.c3 when T2 is filtered on T2.c2.

    Hope that makes some sense.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t1.* -- might as well get all columns
      FROM t1
    LEFT OUTER
      JOIN t2
        ON t2.c3 = t1.c2
       AND t2.c2 = 'something'
     WHERE t2.c3 IS NULL
    the "something" is the filter you mentioned (sorry, i don't know what that means)

    IS NULL is the technique used to find unmatched rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2010
    Posts
    7
    Thanks again for the reply Rudy (?)

    I will give that a go. It looks close to what I have already tried but likely it is not.
    By "filter" I mean parameter, I suppose. My db "programming" background is using Delphi.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hang on a sec

    your "re-colouring" has cleared it up for me

    let's first of all make sure we're dealing with the correct t1 rows --
    Code:
    SELECT t1.c2
      FROM t1
     WHERE t1.c3 = 'y' 
       AND t1.c4 = 'c'
    this should yield A, B, E

    now let's try the filter on t2.c2 --
    Code:
    SELECT t1.c2
      FROM t1
    LEFT OUTER
      JOIN t2
        ON t2.c3 = t1.c2
       AND t2.c2 = 1   -- this is the filter
     WHERE t1.c3 = 'y' 
       AND t1.c4 = 'c'
       AND t2.c3 IS NULL
    this should give no results

    change the filter to 2 and 3 and let me know if it works

    be careful transcribing this back to your real column names

    all the c2s and c3s are very confusing, so don't make a typo
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2010
    Posts
    7
    Thanks Rudy.

    I ill not be able to give this a go for an hour or so. I will let you know how it went.

    Cheers!

  8. #8
    Join Date
    Jan 2010
    Posts
    7
    Hi Rudy. Gave the code a whirl. Came back with the error "invalid filename". My guess is that the error arises because the query hasn't returned any columns from T2 other than c2.
    Would it be possible at this point to add in a subquery that would retrieve the field so a filter can then be applied?
    Last edited by spm; 01-14-10 at 00:56.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by spm View Post
    Hi Rudy. Gave the code a whirl. Came back with the error "invalid filename". My guess is that the error arises because the query hasn't returned any columns from T2 other than c2.
    i have no idea what this means

    sql doesn't use filenames

    maybe it's time for you to be a bit less secretive about the table and column names, and which database system you're using?


    Would it be possible at this point to add in a subquery that would retrieve the field so a filter can then be applied?
    sorry, no idea what this means, either
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2010
    Posts
    7
    Thanks for sticking with it Rudy.

    I am not trying to be secretive. Actually I am trying to be be as generic and plain as possible.
    The database I am using is Paradox, although I get similar problems when I try this using Access.

    The actual table names and field names and types (set up in Access) are

    Table1 (ProId -number, SiteNo -number, Sign -Yes/No, Age -text)
    Table2 (SceDetNo -number, SceNo -number, SiteNo -number)

    I set up these tables in Access and ran the following code, which is based on the code sample you wrote:

    Code:
    SELECT Table1.siteno
      FROM Table1
    LEFT OUTER
      JOIN Table2
        ON Table2.siteno = Table1.siteno
       AND Table2.sceno = 1 
     WHERE  (((Table1.sign)=Yes) AND ((Table1.age)="cur") AND ((Table2.sceno) Is Null))
    All the bracketing in the WHERE clause is based on what the Access query wizard came up with.
    Access comes back with the error message "Join expression not supported". As I mentioned, BDE (Borland Database Engine) returned the error "invalid filename" when I ran the query last night on Paradox Tables.

    I am pretty sure that what I've coded above is the same as what you coded. I could try this out with in MYSQL tonite.

    Thanks,
    Scott

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, no experience with paradox

    for ms access you can try this --
    Code:
    SELECT Table1.siteno
      FROM Table1
    LEFT OUTER
      JOIN Table2
        ON ( Table2.siteno = Table1.siteno
       AND Table2.sceno = 1 
           )
     WHERE ...
    if that doesn't work, try this --
    Code:
    SELECT Table1.siteno
      FROM Table1
    LEFT OUTER
      JOIN ( SELECT siteno
               FROM Table2
              WHERE sceno = 1 ) AS try2
        ON try2.siteno = Table1.siteno
     WHERE ...
    and if that doesn't work, save the subquery as a query (the ms access equivalent of a view) and then run this --
    Code:
    SELECT Table1.siteno
      FROM Table1
    LEFT OUTER
      JOIN subqry2
        ON subqry2.siteno = Table1.siteno
     WHERE ...
    i guess i can see now what you were thinking about using a subquery...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2010
    Posts
    7

    Smile

    Thanks Rudy, you are a wiz!

    The first code worked:

    Code:
    SELECT Table1.siteno
      FROM Table1
    LEFT OUTER
      JOIN Table2
        ON ( Table2.siteno = Table1.siteno
       AND Table2.sceno =2  )
     WHERE  (((Table1.sign)=Yes) AND ((Table1.age)="cur") AND ((Table2.sceno) Is Null))
    Very grateful and I will take a look at your book!

    Cheers

Posting Permissions

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