Looking for help with an unmatched record query

spm · #1 (**permalink**) 01-13-10, 20:40

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!

r937 · #2 (**permalink**) 01-13-10, 20:45

Quote:

Originally Posted by spm

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 --

Quote:

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?

spm · #3 (**permalink**) 01-13-10, 21:03

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.

r937 · #4 (**permalink**) 01-13-10, 21:15

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

spm · #5 (**permalink**) 01-13-10, 21:28

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.

r937 · #6 (**permalink**) 01-13-10, 21:37

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

spm · #7 (**permalink**) 01-13-10, 21:46

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!

spm · #8 (**permalink**) 01-13-10, 23:01

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?

r937 · #9 (**permalink**) 01-13-10, 23:57

Quote:

Originally Posted by spm

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?

Quote:

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

spm · #10 (**permalink**) 01-14-10, 10:58

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

r937 · #11 (**permalink**) 01-14-10, 11:06

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...

spm · #12 (**permalink**) 01-14-10, 11:19

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

Internet Services

Web Development

Digital Marketing

Consumer Tech