September 29, 2005 at 10:57 am
I am not an advanced SQL programmer, so i apologize if this question is overly basic. I have a problem that i am having trouble getting my head around.
I have two tables, one mapping. The mapping table contains three fields - a unique identifier, a user id, and a record id for the other table. This other table has a field that contains a digit - either 0, 1, 2, or three to distinguish the record type. I need a query that will check to see if the second table has any records mapped to a user who has a type other then 1 but no type 1. Users may have multiple types - most do.
I have tried the following, but it does not return the desired results:
SELECT * FROM table2
WHERE ID NOT IN
(SELECT DISTINCT table2.ID FROM table2 INNER JOIN table1 map ON map.recID = table2.ID WHERE table2.type = 1 AND table2.Dormant IS NULL)
AND Dormant IS NULL
ORDER BY ID
Any help anyone can offer is appreciated.
September 29, 2005 at 11:17 am
Select distinct Have_Not_Type1.recID
From
(select distinct map.recID
from table1 map
left join table2 ON map.recID = table2.ID AND table2.type=1
WHERE table2.ID IS NULL
) AS Have_Not_Type1
INNER JOIN table2 Have_Other_type on Have_Not_Type1.recID=Have_Other_type.table2.ID
and (Have_Other_type.type =0 OR Have_Other_type.type=2 OR Have_Other_type.type=3)
is one solution
September 29, 2005 at 2:05 pm
Thank you for the reply. I haven't had a chance to use it yet, but I will later.
Thanks,
LK
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply