E
Earl Takasaki
I am trying to perform a couple of tough queries (for me)...
First Q:
Suppose we have a table with two columns Col1 and Col2.
I would like to find all of the records where there is no matching record
for Col1 in Col2 of all of the records, or no matching record for Col2 in all
of the records in Col1. For example, let's say we had the following data:
Table
Col1 Col2
A B
B C
C B
E A
A F
G H
Query result:
E A
A F
G H
In this table the query would return records 4 because the is no E in Col2,
5 because there is no F in Col1, and 6 because there is neither a G in col2
nor an H in Col1.
Second Q:
I'm also trying to produce the "Complement" of a join.
Let's say the there is a table with a key field and another table with the
same field. If perform a join on the key field, then display the fields of
the first table, i will get all of the records where the first table has a
match in the second field. However, i would like to return all of the records
in which the key field does NOT have a matching record in the second field.
Can I do this?
Finally, I want to write an expression that will return TRUE if a value is
within a table field. For example, suppose we have the following table:
TABLE1
Col1
A
B
C
I would like to write an expression (say in another query) that looks
something like
iif(myChar is a subset of (Table1.Col1), "Yes", "No")
Can this be done?
First Q:
Suppose we have a table with two columns Col1 and Col2.
I would like to find all of the records where there is no matching record
for Col1 in Col2 of all of the records, or no matching record for Col2 in all
of the records in Col1. For example, let's say we had the following data:
Table
Col1 Col2
A B
B C
C B
E A
A F
G H
Query result:
E A
A F
G H
In this table the query would return records 4 because the is no E in Col2,
5 because there is no F in Col1, and 6 because there is neither a G in col2
nor an H in Col1.
Second Q:
I'm also trying to produce the "Complement" of a join.
Let's say the there is a table with a key field and another table with the
same field. If perform a join on the key field, then display the fields of
the first table, i will get all of the records where the first table has a
match in the second field. However, i would like to return all of the records
in which the key field does NOT have a matching record in the second field.
Can I do this?
Finally, I want to write an expression that will return TRUE if a value is
within a table field. For example, suppose we have the following table:
TABLE1
Col1
A
B
C
I would like to write an expression (say in another query) that looks
something like
iif(myChar is a subset of (Table1.Col1), "Yes", "No")
Can this be done?