Solved

return row only if value not in other row

Posted on 2014-03-11
3
40 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 34

Accepted Solution

by:
Dan Craciun earned 250 total points
Comment Utility
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 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now