CODEWITHSUNDEEP
sqlsql-serverxor
KastelA Kastel
KastelA Kastel

Reputation: 83

SQL XOR syntax isn't working

I noticed there is not XOR function in SQL SERVER EXPRESS so I tried to do it the old way.

select NCLI
from   CLIENT
where  NCLI in (select NCLI
            from   COMMANDE
       where  NCOM in (select NCOM
            from   DETAIL
            where  ((NPRO = 'PA60' and NPRO <> 'PA45')
           or (NPRO <> 'PA60' and NPRO = 'PA45'))))

Now, I'm not a mathematics pro but from what I know, this should give me the Number of the clients (NCLI) that only order PA60 or PA45 but not both of them.

The result is that it does also give the NCLI of persons who order both.

I checked my good old math course again but I have no idea what I'm missing here.

Upvotes: 0

Views: 259

Answers (4)

Bacon Bits
Bacon Bits

Reputation: 32180

There's several ways to write this, but this is probably the most straightforward from what you have.

SELECT NCLI
FROM CLIENT
WHERE NCLI IN (
    SELECT NCLI
    FROM COMMANDE
    WHERE NCOM IN (
        SELECT NCOM
        FROM DETAIL
        WHERE NPRO IN ('PA60', 'PA45')
            AND NCOM NOT IN (
                SELECT NCOM
                FROM DETAIL
                WHERE NPRO = 'PA60'

                INTERSECT

                SELECT NCOM
                FROM DETAIL
                WHERE NPRO = 'PA45'
                )
        )
    )

Upvotes: 0

user330315
user330315

Reputation:

A where condition is applied to the columns of a single row, not to a set of rows.

The condition:

the clients (NCLI) that only order PA60 or PA45 but not both of them.

can be solved using a group by clause:

select NCOM
from  DETAIL
where NPRO IN ('PA60','PA45')
group by ncom
having count(distinct NPRO) = 1

The distinct NPRO is only necessary if the same client can appear more than once in the detail table for the same product. Otherwise a count(*) will do as well (but the distinct doesn't do any harm here).

The above will also return clients that order (only) PA60 and something else. If you also want to exclude those you need to add another condition to include only those where the number of ordered products is equal to the number of the 

select NCOM
from  DETAIL
where NPRO IN ('PA60','PA45')
group by ncom
having count(NPRO) = 1
   and count(NPRO) = (select count(*) 
                      from  DETAIL d2
                      where d2.ncom = detail.ncom)

The second condition could also be put into the where part.

Upvotes: 2

Joel Coehoorn
Joel Coehoorn

Reputation: 416049

That big conditional expression is only looking at data in the same row, not every row.

To do what you want, you need to compare (JOIN) the table with itself, so that every row can be compared with ever other row. You can do this with an exclusion join, where you do a left join to a table looking for some condition, and then in the where clause exclude all the matches by limiting the results to NULL records for a field from the 2nd table that should never be NULL.

Apply this to your problem, and then inner-most select query should look like this:

select d1.NCOM
from   DETAIL d1
left join DETAIL d2 on d2.NCOM = d1.NCOM and d2.NPRO <> d1.NPRO and d2.NPRO IN ('PA60', 'PA45')
where d1.NPRO IN ('PA60', 'PA45') and d2.NCOM IS NULL

It's also worth nothing that you can sometimes get this to perform better with a NOT EXISTS query instead of an exclusion join, but I find the exclusion joins much easier to work with.

With that out of the way, we should also write the other nested selects as JOINs:

select cl.NCLI
from   DETAIL d1
left join DETAIL d2 on d2.NCOM = d1.NCOM and d2.NPRO <> d1.NPRO and d2.NPRO IN ('PA60', 'PA45')
inner join COMMANDE cm on cm.NCOM = d1.NCOM
inner join CLIENT cl on cl.NCLI = cm.NCLI
where d1.NPRO IN ('PA60', 'PA45') and d2.NCOM IS NULL

Depending on the relationships in your tables, you may also need to add a distinct to that.

Upvotes: 1

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

where  NCOM in 
(select NCOM from   DETAIL where  (
(NPRO = 'PA60' and NPRO <> 'PA45')
or (NPRO <> 'PA60' and NPRO = 'PA45')
)

You should notice that this WHERE is processed for each row, meaning that you have redundant checking inside your clause.

Whenever NPRO value holds PA60 it's obvious that it won't hold any other value inside the same row (since it's not an ARRAY).

The same rule apply for second condition.

Your WHERE actually looks like

WHERE NCOM IN ( SELECT NCOM FROM DETAIL WHERE NPRO = 'PA60' OR NPRO = 'PA45' )

So this subquery gives you NCOM for all NPRO that are IN ('PA60','PA45').

And every step higher would produce a false result for the case you want.

Upvotes: 0

Related Questions