April 3, 2007 at 4:15 pm
This has been baffling us for some time.Having difficulty figuring the right query to achieve this. Trying to list all rows in table1 where a user did not make a log in table2.
we have two tables. Sample rows below:
Table 1 (this table lists a bunch of widgits aka caches that a user can then go and post a log against in table 2 below)
CacheID.....CacheName.....Cachetype.....CacheDiff
--------------------------------------------------
43......... Bluegill......Traditional...Easy
44..........Loser.........Multi.........Easy
Table 2 (users log whether they FOUND or did NOTFIND the a cache):
LogID.....CacheID.....Logtype.....LogOwner
-------------------------------------------
1.........43..........Found.......Roy
2.........43..........Found.......Tim
3.........43..........NoFind......Sue <---knowning Sues id = 76
4.........44..........Found.......Roy
5.........44..........Found.......Sue
SELECT
d.cache_diff,
d.cache_name,
d.cache_type,
d.cache_last_found
FROM table1 d
LEFT JOIN table2 ON d.cache_id=table2.cache_id
WHERE table2.log_type = 'Found' && table.log_owner_id = 76
This returns a row for cache id 44 as expected.
How do we alter this query to only show rows from Table1 where SUE did not log a FIND in TABLE2
So, basically if SUE logged against 3 caches and there are 10 caches available to log against then we want to display only the remaining 7.
Is JOIN the way to go on this one? Temp table?
thanks
April 4, 2007 at 3:35 am
Try something like:
SELECT T1.*
FROM Table1 T1
LEFT JOIN Table2 T2
ON T1.CacheID = T2.CacheID
AND T2.LogType = 'Found'
AND T2.LogOwner = 'Sue'
WHERE T2.CacheID IS NULL
Putting the filters in the ON clause means they are applied before the JOIN.
April 10, 2007 at 9:56 am
Ken...Thank you! that did the trick.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply