Re: in clause problem

hi,

best greetings to Linz!

do you know Christian Redl? he also works for Racon.

what i got so far (it's no homework, but a real problem at work)

SELECT DISTINCT c.id, c.name
FROM c, r
WHERE ((c.id = r.ref_c_id) AND (r.ref_m_id IN (2,3));

actual result:

1, C1  (has M1, M2, M3)
2, C2  (has M2, M3)
3, C3  (has M3, M4, M5)

desired result:
1, C1
2, C2

i think there should be a elegant and simple solution in one sql statement.

best regards + thanks for your help,

   Georg Gruber.

In article <3AE41EE0.1EBC8D1D_at_racon-linz.at>, Heinz Huber <hhuber_at_racon-linz.at> writes:
>Hi,
>
>georg_gruber_at_web.de wrote:
>>
>> hi, my name is Georg Gruber and this is my first posting to this
>> newsgroup.
>>
>> i hope i'm right here with my question.
>>
>> i have the following problem...
>>
>> there are three tables
>> C (id, name)
>> M (id, name)
>> R (id, ref_c_id, ref_m_id)
>>
>> Values are:
>> C
>> ----
>> 1, C1
>> 2, C2
>> 3, C3
>>
>> M
>> ----
>> 1, M1
>> 2, M2
>> 3, M3
>> 4, M4
>> 5, M5
>>
>> R
>> ----
>> 1, 1, 1
>> 2, 1, 2
>> 3, 1, 3
>> 4, 2, 2
>> 5, 2, 3
>> 6, 3, 3
>> 7, 3, 4
>> 8, 4, 5
>>
>> Now i want to select via multiple choise from table M some values
>> and get exactly the name from the according data from table T.
>>
>> For example:
>> i want to get all entries from table T when the have M2 AND M3 set in R.
>> result would be: 1, 2 but not 3 because i doesn't have M2.
>>
>> i tried IN CLAUSE but this won't work right because IN makes a logical OR.
>>
>> does anybody know a good answer? do i have to build subqueries or
 temporary
>> tables or something?
>
>Post what you have so far, and people can criticise it (in a positive
 sense).
>Perhaps you have applied the IN to the wrong columns.
>
>This sounds a lot like homework though.

• Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web ----- http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net