Mysql Query question

Question

I'm trying to work out the following query:

             SELECT did_numbers.did_dialstring,
          netareas.netarea_name,
          did_numbers.did_size,              
          CASE WHEN (reseller_id < 1) IS NULL THEN 'F'
          ELSE 'T'
          END as reseller,
          COUNT(*) AS aantal
         FROM did_numbers
         LEFT JOIN reseller_numbers ON (did_numbers.did_number = reseller_numbers.did_number AND reseller_numbers.did_number NOT IN(
         SELECT did_number
    FROM reseller_numbers
    WHERE did_number
    REGEXP  '^31([0-9]{2,3})71([0-9]{4,5})$'
         ))
         LEFT JOIN netareas ON did_numbers.did_dialstring = netareas.netarea_code 
         WHERE did_numbers.did_number NOT IN (
          SELECT did_number
    FROM did_numbers
    WHERE did_number
    REGEXP  '^31([0-9]{2,3})71([0-9]{4,5})$'
         )          
         GROUP BY did_numbers.did_dialstring, did_numbers.did_size, reseller
         ORDER BY did_numbers.did_dialstring, reseller ASC

However, it does not seem to work. What happens is the numbers that are found in the subquery's are still counted. What am i doing wrong?

Thanks guys.

OMG Ponies · Accepted Answer · 2010-10-20 17:01:47Z

1

Here's my re-write of your query:

   SELECT a.did_dialstring,
          na.netarea_name,
          a.did_size,              
          CASE 
            WHEN reseller_id IS NULL THEN 'F'
            ELSE 'T'
          END as reseller,
          COUNT(*) AS aantal
     FROM DID_NUMBERS a
LEFT JOIN RESELLER_NUMBERS rn ON rn.did_number = a.did_number
                             AND rn.did_number NOT REGEXP '^31([0-9]{2,3})71([0-9]{4,5})$'
LEFT JOIN NETAREAS na ON na.netarea_code = a.did_dialstring
LEFT JOIN DID_NUMBERS dn ON dn.did_number = a.did_number
                        AND dn.did_number NOT REGEXP  '^31([0-9]{2,3})71([0-9]{4,5})$'
    WHERE dn.did_number IS NULL          
 GROUP BY a.did_dialstring, a.did_size, reseller
 ORDER BY a.did_dialstring, reseller ASC

If that's still returning the numbers you don't want to see, you're going to have to review the regex you're using to filter the numbers out.

answered Oct 20, 2010 at 17:01

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

thecodeassassin Over a year ago

The rewrite is good, except that the numbers are are taken (Marked as T) are now no longer shown :(.

OMG Ponies Over a year ago

@Stephen: reseller_id is the only column you didn't provide a table alias, so I don't know which table is not returning a non-NULL reseller_id value to determine if your did_number filtration is also returning reseller_id values that are only NULL. You need to review your data.

Collectives™ on Stack Overflow

Mysql Query question

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related