Solved

return row only if value not in other row

Posted on 2014-03-11
3
45 Views
Last Modified: 2016-06-04
Have the following subset of tblMember:

mem_num     product_type         address
1                          A                      main st
1                          C                      main st                      
2                          A                      1st street
2                          C                      1st street
2                          D                      1st street
3                          A                      2nd street
3                          B                      2nd street
4                          A                      3rd street
4                          C                      3rd street

I want to select only rows with product_type 'A', if mem_num does not have product type 'B' in another row or

mem_num       product_type         address
1                          A                         main
2                          A                         1st Street
4                          A                         3rdStreet

Thanks!
0
Comment
Question by:needplease
3 Comments
 
LVL 35

Accepted Solution

by:
Dan Craciun earned 250 total points
ID: 39919841
SELECT * FROM tblMember 
    WHERE product_type='A' AND mem_num NOT IN (
    SELECT mem_num FROM tblMember WHERE product_type='B'
)

Open in new window


HTH,
Dan
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 39919901
I prefer for this kind of queries the NOT EXISTS syntax, especially if the number of rows having the product_type='B' here to be huge
SELECT m.*
  FROM tblMember  m
    WHERE m.product_type='A' 
AND NOT EXISTS( 
    SELECT null 
   FROM tblMember o
   WHERE o.product_type='B'
       and o.mem_num  = m.mem_num 
) 

Open in new window

you shall have a (single) index on the columns mem_num + product_type to ensure the query runs as fast as possible
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question