• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.

SQL Query help

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    SQL Query help

    Would anyone be able to advise on this, im not sure how to do what I want to do.

    I have a table CUSTOMER which contains 10,000 rows and has a column MOBILE_NUMBER

    I have an OUTGOING_SMS table which contains 3,500 rows and also has a column MOBILE_NUMBER

    I'm looking to find all the customers who have not had an SMS which should be 6,500

    I know how to find all the customer who have had an SMS, its a simple join but I'm not sure how to find the inverse.

    Any help would be fantastic. cheers

    DB, is MySQL.

    #2
    Originally posted by minestrone View Post
    Would anyone be able to advise on this, im not sure how to do what I want to do.

    I have a table CUSTOMER which contains 10,000 rows and has a column MOBILE_NUMBER

    I have an OUTGOING_SMS table which contains 3,500 rows and also has a column MOBILE_NUMBER

    I'm looking to find all the customers who have not had an SMS which should be 6,500

    I know how to find all the customer who have had an SMS, its a simple join but I'm not sure how to find the inverse.

    Any help would be fantastic. cheers

    DB, is MySQL.
    select * from CUSTOMER where customerid not in (select customerid using the query you have)

    Its hackville and there will be more optimized versions of it but that will work as a one off...
    merely at clientco for the entertainment

    Comment


      #3
      I think the answer is a NOT IN

      Comment


        #4
        Originally posted by eek View Post
        select * from CUSTOMER where customerid not in (select customerid using the query you have)

        Its hackville and there will be more optimized versions of it but that will work as a one off...
        Cheers

        Comment


          #5
          Great, that works a treat

          Comment


            #6
            Left join where the join returns null is probably more efficient, but for a one off it matters not. Is it a one off?

            Comment


              #7
              Originally posted by mudskipper View Post
              Left join where the join returns null is probably more efficient, but for a one off it matters not. Is it a one off?
              +1

              Comment


                #8
                Originally posted by minestrone View Post
                I think the answer is a NOT IN
                NOT EXISTS would be more performant than NOT IN in MySQL.
                Best Forum Advisor 2014
                Work in the public sector? You can read my FAQ here
                Click here to get 15% off your first year's IPSE membership

                Comment


                  #9
                  Originally posted by TheFaQQer View Post
                  NOT EXISTS would be more performant than NOT IN in MySQL.
                  ...and Oracle.
                  Also, it should perform as good as the NOT IN with the NULL, that was mentioned.
                  Put an index on the mobile number column in the second table if there's not already one there.
                  Don't believe it, until you see it!

                  Comment


                    #10
                    Originally posted by darrylmg View Post
                    ...and Oracle.
                    Also, it should perform as good as the NOT IN with the NULL, that was mentioned.
                    Put an index on the mobile number column in the second table if there's not already one there.
                    Assuming its a non-clustered index, you should be careful whether you add indexes or not. For something with alot of inserts/updates and few index uses it might not be worthwhile.

                    Comment

                    Working...
                    X