Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two tables.

1st table:

called kimlik

  id        ad
 ---------------
 1         ahmet
 2         mehmet
 3         ali

2nd table:

called siparis

 id        kimlikid     tarih                      miktar
 ------------------------------------------------------------
 1         1              22.09.2011           10 
 1         2              22.09.2011           100

I want to list via SQL query to persons who doesn't give an order on 22.09.2011.

Result:

 ad             tarih    
 --------------------------
 ali           22.09.2011  
share|improve this question
add comment

3 Answers 3

up vote 5 down vote accepted

Some methods:

NOT IN

DECLARE @NoDateWanted date = '22.09.2011';

SELECT ad, @NoDateWanted as tarih
FROM
    kimlik k
WHERE
    k.ID NOT IN (SELECT kimlikid FROM siparis s WHERE s.tarih = @NoDateWanted)

NOT EXISTS

DECLARE @NoDateWanted date = '22.09.2011';

SELECT ad, @NoDateWanted as tarih
FROM
    kimlik k
WHERE
    NOT EXISTS(SELECT 'kimlik' FROM siparis s 
               WHERE k.id = s.kimlikid 
                     and s.tarih = @NoDateWanted)

LEFT JOIN

DECLARE @NoDateWanted date = '22.09.2011';

SELECT ad, @NoDateWanted as tarih
FROM
    kimlik k
    left join siparis s 
         on s.kimlikid = k.id
         and s.tarih = @NoDateWanted
WHERE s.id IS NULL

The method you choose is usually down to personal preference and performance (they can produce different query plans depending on your data volumes), ie: NOT EXISTS is frequently faster than LEFT JOIN if siparis has 1,000s of rows per kimlik id.

EDIT: Added on variable to compare and display in result set

share|improve this answer
2  
+1 FYI NOT EXISTS is safest because of NULLs and NOT IN. And quicker because it is a semi-join rather then join with filter. See dba.stackexchange.com/questions/4009/… –  gbn Oct 4 '11 at 7:57
1  
+1 good answer (although none of the 3 give the result in the Q) –  Jack Douglas Oct 4 '11 at 8:49
1  
thanks @JackDouglas now fixed :-) –  Andrew Bickerton Oct 4 '11 at 9:21
add comment

Following on from Andrew Bickerton's answer...

The 4th way is to use EXCEPT where the RDBMS supports it
This should give the same execution plan as NOT EXISTS

DECLARE @NoDateWanted date = '20110922';

SELECT id, @NoDateWanted AS tarih
FROM kimlik
EXCEPT
SELECT id, @NoDateWanted 
FROM siparis
WHERE tarih = @NoDateWanted;

Edit: Now with added corrections.

You'll have to add the date filter in as a constant.
When you check for "no rows" there is no row to pull the data from of course

share|improve this answer
1  
do you mean select id from kimlik except select select id from siparis where tarih = '22.09.2011'? –  Jack Douglas Oct 4 '11 at 8:42
    
@Jack Douglas: thanks. Yes I did. However, it was wrong in other ways –  gbn Oct 4 '11 at 8:43
    
+1 except aka minus –  Jack Douglas Oct 4 '11 at 8:53
    
+1 I like that additional option –  Andrew Bickerton Oct 4 '11 at 9:22
add comment
SELECT k.ad, s.tarih
FROM kimlik k
JOIN siparis s on k.kimlikid = s.id
WHERE k.id NOT IN (
         SELECT si.kimlikid 
         FROM si.siparis
         WHERE si.tarih = '22.09.2011')
share|improve this answer
1  
-1 in general this would return too many rows and will give the 'wrong' tarih - it should always be '22.09.2011' according to the sample result –  Jack Douglas Oct 4 '11 at 8:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.