Re: Query Help - Stumped...

How about this:

SELECT A1
FROM Z z1
WHERE B1 IN ('b','g')
OR (SELECT count(*) FROM Z z2

    WHERE z2.A1 = z1.A1
    AND z2.B1 in ('a','c')
   ) = 2

Or this:

SELECT A1
FROM Z
WHERE B1 IN ('b','g')
UNION
SELECT A1
FROM Z
WHERE B1 IN ('a','c')
GROUP BY A1
HAVING count(*) = 2

On Wed, 17 May 2000 16:52:47 GMT, thecake_at_my-deja.com wrote:

>I appologize for the confusion and my notation.
>
>The reason why 2 is not in my result is because the criteria is it must
>have AT LEAST BOTH 'a' and 'c'. 2 only has 'a' and 'd'.
>
>"Which table contains the rows (a, c) and (b, g) or are these two rows
>(a), (c) and (b), (g)?"
>
>It is the latter in that each row contains a single
>letter 'a', 'b', 'c' and TABLE Z is the relationship table linking
>TABLE A to TABLE B.
>
>Any clue on how to solve this?
>
>
>In article <390B7B44.A97B609_at_0800-einwahl.de>,
> Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de> wrote:
>> I do not understand your question.
>>
>> Why is 2 not in your result set? It also has 'a'.
>>
>> What ist the connection between your combinations (a, c) and (b, g)
 and the
>> result? Which table contains the rows (a, c) and (b, g) or are these
 two rows
>> (a), (c) and (b), (g)? It's a bit confusing because normally you use
 your tuple
>> notation (a, c) for one row, not for several. You should write {(a),
 (c)} and
>> {(b), (g)}.
>>
>> For what do you need table a and table b?
>>
>> Martin
>>
>> thecake_at_my-deja.com wrote:
>> >
>> > Please Help! I have a relational database question regarding how to
>> > get the information I need out of the database with 1 single line of
>> > SQL that answers the following question:
>> >
>> > Using TABLE Z, find all TABLE A records that have ANY of these
 TABLE B
>> > records (b, g) OR ALL of these TABLE B records (a,c). The result
>> > should return:
>> >
>> > 1 (because it has BOTH A,C)
>> > 3 (because it has b)
>> > 4 (because it has b – also happens to have BOTH A,C)
>> > 5 (because it has g)
>> > 6 (because it has BOTH A,C)
>> >
>> > PLEASE KEEP IN MIND that the two sets above (b, g) and (a, c) could
>> > actually contain many more items in the real world! Feel free to
 reply
>> > here or email at:
>> >
>> > thecake_at_writeme.com - THANKS in Advance!
>> >
>> > Here is a simple example of the data (THIS IS ONLY AN EXAMPLE. I
 used
>> > numbers and letters for in the hopes of being clear, the real data
 is
>> > obviously primary keys of INT).
>> >
>> > TABLE A
>> > COL A1
>> > 1
>> > 2
>> > 3
>> > 4
>> > 5
>> > 6
>> >
>> > TABLE B
>> > COL B1
>> > a
>> > b
>> > c
>> > d
>> > e
>> > g
>> >
>> > TABLE Z
>> > COL A1 COL B1
>> > 1 a
>> > 1 c
>> > 2 a
>> > 2 d
>> > 3 a
>> > 3 b
>> > 3 e
>> > 4 a
>> > 4 b
>> > 4 c
>> > 5 g
>> > 6 a
>> > 6 c
>> >
>> > Sent via Deja.com http://www.deja.com/
>> > Before you buy.
>>
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Wed May 17 2000 - 00:00:00 CDT