CODEWITHSUNDEEP
sql
Chandana De Silva
Chandana De Silva

Reputation: 27

SQL AND operator not working properly

I have following two tables

LandParcels Table

Blockid ParcelNo storPri
======= ======== =======
52000105   3      State
52000105   4      Private
52000105   5      State

Actions Table

Blockid ParcelNo ActionTaken
======= ======== ===========
52000105   3      Received
52000105   3      Send to Computer
52000105   4      Received
52000105   5      Received

I want to find the records Received but not Send to Computer

Here is my query

select 
    l.blockid, l.parcelno 
from 
    landparcels l 
left join 
    actions ac on l.blockid = ac.blockid and l.parcelno = ac.parcelno 
where 
    ac.actiontaken = 'Received' 
    and ac.actiontaken <> 'Send to Computer'  
    and ac.blockid = 52000105

The result is

Blockid ParcelNo 
======= ======== 
52000105   3
52000105   4
52000105   5

I want ParcelNo 4 and 5

Upvotes: 2

Views: 10164

Answers (6)

onedaywhen
onedaywhen

Reputation: 57023

A further variation on @Kirakun's 'row constructors' answer, is still ISO/ANSI syntax but this is indeed supported in SQL Server:

SELECT blockid, parcelno
  FROM landparcels
INTERSECT 
SELECT blockid, parcelno
  FROM actions
 WHERE actiontaken = 'Received'
EXCEPT 
SELECT blockid, parcelno
  FROM actions
 WHERE actiontaken = 'Send to Computer';

Upvotes: 0

kirakun
kirakun

Reputation: 2790

If your database supports tuple in where clauses, try

select *
from landparcels
where (blockid, parcelno) in
(
    select blockid, parcelno
    from actions
    where actiontaken = 'Received'
)
and  (blockid, parcelno) not in
(
    select blockid, parcelno
    from actions
    where actiontaken = 'Send to Computer'
)

Edit in response to marc_s: Otherwise, try this version, which I think is ANSI, instead

select *
from landparcels as p
where exists
(
    select 1
    from actions
    where actiontaken = 'Received'
    and blockid = p.blockid
    and parcelno = p.parcelno
)
and not exists
(
    select 1
    from actions
    where actiontaken = 'Send to Computer'
    and blockid = p.blockid
    and parcelno = p.parcelno
)

Upvotes: 1

Larry Lustig
Larry Lustig

Reputation: 50970

Please, it's extremely unlikely that you'll find a simple bug in the AND operator in any commonly used database product. The problem here is not that the database engine isn't producing the correct results, it's that you don't understand what the AND operator does.

Look at your condition ac.actiontaken = 'Received' AND ac.actiontaken <> 'Send to Computer'. What's going to happen is that the engine is going to examine every possible row in the output and decide whether it meets the condition that you specified. So, for the first row is it true that actiontaken is 'Received'? Yes. Is it is also true that actiontaken is not 'Send to Computer'? Sure. So the row qualifies.

In fact, any row with actiontaken of 'Received' will qualify since, by definition, it is also true that actiontaken for that row is not 'Send to Computer'.

There are a number of ways to get the answer that you want. Here's my preferred one:

SELECT DISTINCT a.blockid, a.parcelno FROM actions a 
WHERE a.blockid = 52000105 AND a.actiontaken = 'Received' AND NOT EXISTS
   (SELECT * FROM actions a2 WHERE a2.blockid = a.blockid AND 
                                   a2.parcelNo = a.parcelNo AND 
                                   a2.actiontaken = 'Send to Computer')

Upvotes: 1

Abu Muhammad
Abu Muhammad

Reputation: 1185

SELECT Blockid, PacelNo FROM LandParcels
WHERE ( ParcelNo IN ( SELECT ParcelNo FROM Actions WHERE ActionTaken = 'Received')
AND ( ParcelNo NOT IN ( SELECT ParcelNo FROM Actions WHERE ActionTaken = 'Send to Computer')))

Upvotes: 0

Ronnis
Ronnis

Reputation: 12833

select a.blockid
      ,a.parcelno
  from landparcels a
  join actions     b on(a.blockid = b.blockid and a.parcelno = b.parcelno)
 where b.action_taken in('Received', 'Send to Computer')
   and a.blockid = 52000105
 group 
    by a.blockid
      ,a.parcelno
having count(*) = 1
   and min(b.action_taken) = 'Received';

or something less fancy, that can be extended outside the special case of looking for 1 out of two:

select a.blockid
      ,a.parcelno
  from landparcels a
  join actions     b on(a.blockid = b.blockid and a.parcelno = b.parcelno)
 where b.blockid = 52000105
   and b.action_taken = 'Received'
   and not exists(
      select 'x'
        from actions x
       where x.action_taken = 'Send to Computer'
         and b.blockid  = x.blockid 
         and b.parcelno = x.parcelno
   );

I just realized that you don't need to join with landparcels, given your requirements, but I leave it as is, in case your real requirement requires data from that table.

update Added blockid filter

Upvotes: 0

marc_s
marc_s

Reputation: 754538

You didn't say what database system you're using - but on SQL Server, you could use this query here:

SELECT 
    lp.*
FROM 
    dbo.LandParcels lp
WHERE
    EXISTS(SELECT * FROM abo.ActionTaken a 
           WHERE a.blockid = lp.blockid 
             AND a.parcelno = lp.parcelno 
             AND a.ActionTaken = 'Received')
    AND NOT EXISTS(SELECT * FROM dbo.ActionTaken a 
                   WHERE a.blockid = lp.blockid 
                     AND a.parcelno = lp.parcelno 
                     AND a.ActionTaken = 'Send to Computer')

This results in this output here:

blockid parcelno    storePrio
52000105       4          Private
52000105       5          State

Upvotes: 0

Related Questions