Author |
Topic |
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2008-07-29 : 12:47:44
|
Hi,I have a table such named tblExtraPhotos. Users can have up to 10 photos each, which is denoted by the photoID column. I am looking to select a list of NOT the photos they have, but instead a unique list of the photos they dont have.For example. If the query SELECT photoID from tblPhotos WHERE userID=100brought back1,2,3,4,5,6,7,8 as results. I would want to write a query that brought back only 9,10 as results.I could of course handle this in the front end too. Just wondering which way is easier as the front end seems to be a bit of a pain as well.Any help is much appreciated!!thanks once again,mike123 CREATE TABLE [dbo].[tblExtraPhotos]( [counterID] [int] IDENTITY(1,1) NOT NULL, [photoID] [tinyint] NOT NULL, [userID] [int] NOT NULL ) |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-29 : 12:51:04
|
[code]SELECT t.photoIDFROM tblPhotos tLEFT JOIN(SELECT photoID from tblPhotos WHERE userID=100)t1ON t.photoID=t1.photoIDWHERE t1.photoID IS NULL[/code]OR [code]SELECT photoIDFROM tblPhotos WHERE photoID NOT IN(SELECT photoID from tblPhotos WHERE userID=100)[/code] |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2008-07-29 : 13:25:40
|
Hi visakh16,Sorry but neither of those are bringing back the desired results.Let me clarify, since there are 10 photo slots, this query should never bring back more than 10 rows. Currently I'm bringing back 1000's.Any further help is much appreciated.Thxmike123 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-29 : 13:32:04
|
quote: Originally posted by mike123 Hi visakh16,Sorry but neither of those are bringing back the desired results.Let me clarify, since there are 10 photo slots, this query should never bring back more than 10 rows. Currently I'm bringing back 1000's.Any further help is much appreciated.Thxmike123
try with distinct then SELECT DISTINCT t.photoIDFROM tblPhotos tLEFT JOIN(SELECT photoID from tblPhotos WHERE userID=100)t1ON t.photoID=t1.photoIDWHERE t1.photoID IS NULL SELECT DISTINCT photoIDFROM tblPhotos WHERE photoID NOT IN(SELECT photoID from tblPhotos WHERE userID=100) |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-07-29 : 13:36:48
|
[code]SELECT v.NumberFROM master..spt_values AS vLEFT JOIN tblExtraPhotos AS ep ON ep.photoID = v.Number AND ep.userID = 100WHERE v.Type = 'P' AND v.Number BETWEEN 1 AND 10 AND ep.photoID IS NULL[/code] E 12°55'05.25"N 56°04'39.16" |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2008-07-30 : 16:37:59
|
quote: Originally posted by Peso
SELECT v.NumberFROM master..spt_values AS vLEFT JOIN tblExtraPhotos AS ep ON ep.photoID = v.Number AND ep.userID = 100WHERE v.Type = 'P' AND v.Number BETWEEN 1 AND 10 AND ep.photoID IS NULL E 12°55'05.25"N 56°04'39.16"
Hi Peso,This worked perfectly! I couldn't quite follow this query but after I broke it down was able to see what your doing.What do you think of this modification? It brings back the same results, not sure if there is any performance benefit or hit. One possible upside would be if I added more than X # of rows it would auto adjust.Very much appreciatedThanks again!mike123SELECT DISTINCT(v.photoID) FROM tblExtraPhotos AS vLEFT JOIN tblExtraPhotos AS ep ON ep.photoID = v.photoID AND ep.userID = 100WHERE AND ep.photoID IS NULL |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-30 : 16:43:16
|
Compare the execution plans to determine which one is better. Run them in the same batch and make sure to select the option to include the actual execution plan. Then it's as simple as looking at the query cost to see which one is more efficient.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-07-31 : 04:29:36
|
You query will not work 100%.Your query relies that among all users, someone has all ten photoID.If, for example, photoID 9 is missing for all users, your query will not return photoID 9 ever, until first user get a photoID 9.Self joins are handy but should be used with care. E 12°55'05.25"N 56°04'39.16" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-07-31 : 04:31:03
|
If you don't like using the master..spt_values table, you can make your own tally table on-the-fly like thisSELECT v.NumberFROM ( SELECT 1 AS Number UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 ) AS vLEFT JOIN tblExtraPhotos AS ep ON ep.photoID = v.Number AND ep.userID = 100WHERE ep.photoID IS NULL E 12°55'05.25"N 56°04'39.16" |
|
|
|