• Misc Help

  • SQL - Exclude customer who bought certain products.

I am trying to query a database and select customers who bought products A, B, but did NOT buy product C. So, even if they did buy products A, B but they also purchase C I want to exclude them from my list. I know I can do this w/ PHP but I am wondering if this is possible w/ SQL alone.

Here is what I have tried. I also tried something similar using EXCEPT. With the code below I am still getting the customer who ordered product "C"

SELECT m.hdcust, m.hditem
FROM   files.histdtl AS m
WHERE  m.hditem IN ('RICOH3300SET1', 'RICOH7700SET1', 'RICOH7700SET2', 'RICOH7000SET1', 'RICOH7100SET1', 'RICOH7100SET2', 'RICOHGXE3300N', 'RICOHGXE7700N', 'RICOHSG3110DN', 'RICOHSG7700DN')
       AND m.hditem NOT IN (SELECT f.hditem
              FROM   files.histdtl AS f
              WHERE  f.hditem IN ('SG400', 'SG800', 'SG400SET1', 'SG800SET1', 'SG800SET2'))
              ORDER BY m.hdcust ASC

Thanks

(Added [code]...[/code] tags ~ MOD)

    At a quick glance, I'm thinking you'd want to replace this...

    AND m.hditem NOT IN (SELECT f.hditem

    ...with this...

    AND m.hdcust NOT IN (SELECT f.hdcust

      aaahhhhh. That was it! Friday. I am calling it a week. Thanks for solving that. Have a good weekend all.

        Not sure if I should start a new thread.... the question adapts to this. I was just wondering how I would apply DISTINCT to the above query on HDCUST.

        Would I just add DISTINCT right after the opening SELECT? I tried adding another subquery but I was getting "multiple column" error(s).

          If the goal is one row only for each m.hdcust/m.hditem pair, then yeah, you should be able to just start the query with SELECT DISTINCT m.hdcust, m.hditem . . .

            Write a Reply...