By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,347 Members | 2,113 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,347 IT Pros & Developers. It's quick & easy.

sql help - should be simple, but can't get it.

P: n/a
I tried a bunch of sql queries, but can't get this sql correct. Any
help would be appreciated. Thanks.

I have a two field table,

key1, key2

key1 field can have dupes
key2 field is unique

Sample Data:
key1 key2
10 1
10 2
10 11
10 12
11 1
11 11
12 1
12 11
12 2
13 1
13 11
14 1
14 2
14 12
15 1
16 1

Need to select from the table where key1 has 1 and 11 combined, if it
doesn't, exclude from result query

Result of Query should be:

10 1
10 11
11 1
11 11
12 1
12 11
13 1
13 11

Note that (14,15,16) are excluding from the result even though they
contain 1 but not 11.

select key1,key2 from table where key2 in (1,11) gives me 14,15,16
records.


Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
I think this needs to be done as more than one query. The first query
selects all of the key1's where key2 = 1, the second where key2=11. Inner
join the 2 queries to retrieve all key1's that have both 1 and 11 for a
key2. If you really want all of the key1's returned twice, you'll probably
have to use a union query.
"firewire888" <fi**@hotmail.com> wrote in message
news:5m********************************@4ax.com...
I tried a bunch of sql queries, but can't get this sql correct. Any
help would be appreciated. Thanks.

I have a two field table,

key1, key2

key1 field can have dupes
key2 field is unique

Sample Data:
key1 key2
10 1
10 2
10 11
10 12
11 1
11 11
12 1
12 11
12 2
13 1
13 11
14 1
14 2
14 12
15 1
16 1

Need to select from the table where key1 has 1 and 11 combined, if it
doesn't, exclude from result query

Result of Query should be:

10 1
10 11
11 1
11 11
12 1
12 11
13 1
13 11

Note that (14,15,16) are excluding from the result even though they
contain 1 but not 11.

select key1,key2 from table where key2 in (1,11) gives me 14,15,16
records.


Nov 12 '05 #2

P: n/a
TC
How is the key2 field unique? There are 7 records with key2=1; 3 records
with key2=2; ...

TC
"firewire888" <fi**@hotmail.com> wrote in message
news:5m********************************@4ax.com...
I tried a bunch of sql queries, but can't get this sql correct. Any
help would be appreciated. Thanks.

I have a two field table,

key1, key2

key1 field can have dupes
key2 field is unique

Sample Data:
key1 key2
10 1
10 2
10 11
10 12
11 1
11 11
12 1
12 11
12 2
13 1
13 11
14 1
14 2
14 12
15 1
16 1

Need to select from the table where key1 has 1 and 11 combined, if it
doesn't, exclude from result query

Result of Query should be:

10 1
10 11
11 1
11 11
12 1
12 11
13 1
13 11

Note that (14,15,16) are excluding from the result even though they
contain 1 but not 11.

select key1,key2 from table where key2 in (1,11) gives me 14,15,16
records.


Nov 12 '05 #3

P: n/a
Thanks Randy, here is the sql view of what u suggested. would have
thought it could have been done without creating multiple queries,
hmmm...still trying.

SELECT [Query for One].key1, [Query for One].key2
FROM [Query for One] INNER JOIN [Query for Eleven] ON [Query for
One].key1 = [Query for Eleven].key1
union SELECT Working.key1, Working.key2
FROM Working
WHERE (((Working.key2)=11));
On Sat, 20 Dec 2003 05:56:15 GMT, "Randy Harris" <ra***@SpamFree.com>
wrote:
I think this needs to be done as more than one query. The first query
selects all of the key1's where key2 = 1, the second where key2=11. Inner
join the 2 queries to retrieve all key1's that have both 1 and 11 for a
key2. If you really want all of the key1's returned twice, you'll probably
have to use a union query.
"firewire888" <fi**@hotmail.com> wrote in message
news:5m********************************@4ax.com.. .
I tried a bunch of sql queries, but can't get this sql correct. Any
help would be appreciated. Thanks.

I have a two field table,

key1, key2

key1 field can have dupes
key2 field is unique

Sample Data:
key1 key2
10 1
10 2
10 11
10 12
11 1
11 11
12 1
12 11
12 2
13 1
13 11
14 1
14 2
14 12
15 1
16 1

Need to select from the table where key1 has 1 and 11 combined, if it
doesn't, exclude from result query

Result of Query should be:

10 1
10 11
11 1
11 11
12 1
12 11
13 1
13 11

Note that (14,15,16) are excluding from the result even though they
contain 1 but not 11.

select key1,key2 from table where key2 in (1,11) gives me 14,15,16
records.



Nov 12 '05 #4

P: n/a
Yep key2 is not unique. I guess I meant to say unique with respect to
key1, but that is the same as saying no duplicate records in the
table.
On Sat, 20 Dec 2003 16:43:44 +1030, "TC" <a@b.c.d> wrote:
How is the key2 field unique? There are 7 records with key2=1; 3 records
with key2=2; ...

TC
"firewire888" <fi**@hotmail.com> wrote in message
news:5m********************************@4ax.com.. .
I tried a bunch of sql queries, but can't get this sql correct. Any
help would be appreciated. Thanks.

I have a two field table,

key1, key2

key1 field can have dupes
key2 field is unique

Sample Data:
key1 key2
10 1
10 2
10 11
10 12
11 1
11 11
12 1
12 11
12 2
13 1
13 11
14 1
14 2
14 12
15 1
16 1

Need to select from the table where key1 has 1 and 11 combined, if it
doesn't, exclude from result query

Result of Query should be:

10 1
10 11
11 1
11 11
12 1
12 11
13 1
13 11

Note that (14,15,16) are excluding from the result even though they
contain 1 but not 11.

select key1,key2 from table where key2 in (1,11) gives me 14,15,16
records.



Nov 12 '05 #5

P: n/a
SELECT Key.Key1, Key.Key2
FROM [Key]
WHERE ((((Key.Key1) In (select key1 from key where key2=1)) And ((Key.Key1)
In (select key1 from key where key2=11)))and (key2=1 or key2=11));

This also works (I tried it), and may not be quite as convoluted.

"firewire888" <fi**@hotmail.com> wrote in message
news:qj********************************@4ax.com...
Thanks Randy, here is the sql view of what u suggested. would have
thought it could have been done without creating multiple queries,
hmmm...still trying.

SELECT [Query for One].key1, [Query for One].key2
FROM [Query for One] INNER JOIN [Query for Eleven] ON [Query for
One].key1 = [Query for Eleven].key1
union SELECT Working.key1, Working.key2
FROM Working
WHERE (((Working.key2)=11));
On Sat, 20 Dec 2003 05:56:15 GMT, "Randy Harris" <ra***@SpamFree.com>
wrote:
I think this needs to be done as more than one query. The first query
selects all of the key1's where key2 = 1, the second where key2=11. Innerjoin the 2 queries to retrieve all key1's that have both 1 and 11 for a
key2. If you really want all of the key1's returned twice, you'll probablyhave to use a union query.
"firewire888" <fi**@hotmail.com> wrote in message
news:5m********************************@4ax.com.. .
I tried a bunch of sql queries, but can't get this sql correct. Any
help would be appreciated. Thanks.

I have a two field table,

key1, key2

key1 field can have dupes
key2 field is unique

Sample Data:
key1 key2
10 1
10 2
10 11
10 12
11 1
11 11
12 1
12 11
12 2
13 1
13 11
14 1
14 2
14 12
15 1
16 1

Need to select from the table where key1 has 1 and 11 combined, if it
doesn't, exclude from result query

Result of Query should be:

10 1
10 11
11 1
11 11
12 1
12 11
13 1
13 11

Note that (14,15,16) are excluding from the result even though they
contain 1 but not 11.

select key1,key2 from table where key2 in (1,11) gives me 14,15,16
records.


Nov 12 '05 #6

P: n/a
firewire888 wrote:
I tried a bunch of sql queries, but can't get this sql correct. Any
help would be appreciated. Thanks.

I have a two field table,

key1, key2

key1 field can have dupes
key2 field is unique


I don't know howfast this will be but...
Select Key1, key2 From TableX Where
(Key1 = 1 or key1 = 11) And
Dlookup([Key1],"Table1","Key2 = :" & [Key2] & " And [Key1] = 1)
Is Not Null And
Dlookup([Key1],"Table1","Key2 = :" & [Key2] & " And [Key1] =
11) Is Nul Null

First, you selecting records where Key1 is 1 or 11 and that a 1 and 11 is
in that table for that key2 value.
Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.