WEB SQL Crew: Help me out with this query.

Discussion in 'OT Technology' started by Ricky, May 9, 2008.

  1. Ricky

    Ricky █▄ █▄█ █▄ ▀█▄

    Joined:
    Jun 17, 2005
    Messages:
    38,767
    Likes Received:
    6
    Basically i got something like this working.

    Code:
    SELECT * FROM tablenamelulz WHERE ((State = 'NY') OR (State = 'NJ') OR (State = 'CA')) AND ((Lulz = 'hi') OR (Lulz = 'bye'))
    Now basically how do i set it so i dont have to say (State = ) all the time. I just want it to be like (State = 'ny','nj','ca')

    I tried messing around with it at work but gave up :sad2:
     
  2. Ricky

    Ricky █▄ █▄█ █▄ ▀█▄

    Joined:
    Jun 17, 2005
    Messages:
    38,767
    Likes Received:
    6
    also what happens if one of my fields has a space in it.

    Like (State = 'LOL HI')

    the support people for our email program said that it wont be able to work.. and if it does it wont be accurate ?

    Is this true ? Cause i did manage to get it working... and it looks pretty accurate to me.
     
  3. intrktevo

    intrktevo New Member

    Joined:
    Oct 18, 2004
    Messages:
    5,781
    Likes Received:
    0
    Location:
    UCF
  4. Ricky

    Ricky █▄ █▄█ █▄ ▀█▄

    Joined:
    Jun 17, 2005
    Messages:
    38,767
    Likes Received:
    6
  5. Ricky

    Ricky █▄ █▄█ █▄ ▀█▄

    Joined:
    Jun 17, 2005
    Messages:
    38,767
    Likes Received:
    6
    thanks !

    I'll test this out tomorrow.... as you can tell i suck at teh sql but someone has to get this shit working :/

    I appreciate it
     
  6. RaginBajin

    RaginBajin Have you punched a donkey today?

    Joined:
    Dec 24, 2001
    Messages:
    8,740
    Likes Received:
    0
    Location:
    NoVA
    Yeah.. There is "in" as well as "not in". That would be the opposite.
     
  7. Ricky

    Ricky █▄ █▄█ █▄ ▀█▄

    Joined:
    Jun 17, 2005
    Messages:
    38,767
    Likes Received:
    6
    what about my second question with the spaces ?

    It still grabs the information from the database but im not sure if it'll give me other information or some other shit.
     
  8. lukin87

    lukin87 New Member

    Joined:
    Jan 11, 2008
    Messages:
    135
    Likes Received:
    0
    the spaces should be fine.

    but if you want to do partial matches on strings you should look into "like"
     
  9. lukin87

    lukin87 New Member

    Joined:
    Jan 11, 2008
    Messages:
    135
    Likes Received:
    0
    IN a nice little operator... because if you have another table in your database which had a list of all the states in it you could do this:

    SELECT *
    FROM mytablelulz
    WHERE state IN
    (SELECT state_abrv FROM states WHERE population > 5000)


    its very nice :big grin:
     
  10. Ricky

    Ricky █▄ █▄█ █▄ ▀█▄

    Joined:
    Jun 17, 2005
    Messages:
    38,767
    Likes Received:
    6
    IN is awesome :bowdown:
     
  11. Limp_Brisket

    Limp_Brisket New Member

    Joined:
    Jan 2, 2006
    Messages:
    48,422
    Likes Received:
    0
    Location:
    Utah
    yup, very nice.
     
  12. Ricky

    Ricky █▄ █▄█ █▄ ▀█▄

    Joined:
    Jun 17, 2005
    Messages:
    38,767
    Likes Received:
    6
    We're in the proccess of creating a DO NOT EMAIL field in our table.

    Im thinking of doing something like this

    SELECT * FROM dbo.fdsf_table WHERE StateProv IN ('ny','nj','ky','north - ca') AND typeofcontact IN ('candidates','clients','security','sales') AND emailblock NOT IN (*)

    Im not sure if that * will just select everyone whos in that field or am i doing it wrong ?
     
  13. Limp_Brisket

    Limp_Brisket New Member

    Joined:
    Jan 2, 2006
    Messages:
    48,422
    Likes Received:
    0
    Location:
    Utah
    you're doing it wrong
     
  14. RaginBajin

    RaginBajin Have you punched a donkey today?

    Joined:
    Dec 24, 2001
    Messages:
    8,740
    Likes Received:
    0
    Location:
    NoVA
    What's the content in the emailblock column? If there is no content in that column is it NULL or is it blank?

    if it's NULL then you can say emailblock IS NULL which will get everyone who's value in that column is NULL.
     
  15. Ricky

    Ricky █▄ █▄█ █▄ ▀█▄

    Joined:
    Jun 17, 2005
    Messages:
    38,767
    Likes Received:
    6
    well we're going to import a bunch of emails into that field daily and we're going to want to set it so if the criteria matches anything which is in 'emailblock' then it'll exlude it.
     
  16. Vailripper

    Vailripper Daywalkers have feelings too.

    Joined:
    Jan 25, 2006
    Messages:
    6,170
    Likes Received:
    0
    Location:
    Bellingham, WA
    I think you would be better off creating a table which has the states you don't want to send in it, hard coding stuff isn't such a good idea. Then you can join on your table with the banned states.
     
  17. Ricky

    Ricky █▄ █▄█ █▄ ▀█▄

    Joined:
    Jun 17, 2005
    Messages:
    38,767
    Likes Received:
    6
    Well that wont work for us cause we'll be changing it all the time. ( We send out daily emails and want to avoid hitting the same people twice, so the criteria always has to change ).
     
  18. Vailripper

    Vailripper Daywalkers have feelings too.

    Joined:
    Jan 25, 2006
    Messages:
    6,170
    Likes Received:
    0
    Location:
    Bellingham, WA
    How many emails are we talking about?

    If I understand you correctly, you're talking about searching in the emailblock field for a match of items in another field. If you don't have many rows that's fine, but that's going to be incredibly inefficient if you have alot of data, as the engine basically has to go search through the entire string, for each row of data, versus looking for matches on single fields. When you're building databases you want to avoid having concatenated fields as much as possible, things should be broken down as much as possible.
     
  19. RaginBajin

    RaginBajin Have you punched a donkey today?

    Joined:
    Dec 24, 2001
    Messages:
    8,740
    Likes Received:
    0
    Location:
    NoVA
    Is it going to be a lot of values inside that column or are you having multiple records inside the table that can have the emailblock column NULL or NOT NULL.
     
  20. Ricky

    Ricky █▄ █▄█ █▄ ▀█▄

    Joined:
    Jun 17, 2005
    Messages:
    38,767
    Likes Received:
    6
    no its not a match of items.

    Okay this is how it works. We basically get an unsubscribe email every so often. For some reason the company does not want to REMOVE their entry in our database ( contact info, background work etc ) but instead exlude them from our email campaigns.

    What i am planning on doing is getting the list of emails which they want to be excluded and putting it in that fiels.

    And i want to run the sql query like if im normally going to be looking for certain filtered people, but at the end of that query i want it to check to make sure that none are included in the 'emailblock'.

    There are going to be many rows, and will gradually fill up. There's no way i can manually enter in like "NOT LIKE" and then list each email.

    I hope that makes sense :o

    I dont know that much about SQL and stuff but i guess im learning.
     
  21. Ricky

    Ricky █▄ █▄█ █▄ ▀█▄

    Joined:
    Jun 17, 2005
    Messages:
    38,767
    Likes Received:
    6
    and do you mind exlpaining the NULL vs NOT NULL ?
     
  22. RaginBajin

    RaginBajin Have you punched a donkey today?

    Joined:
    Dec 24, 2001
    Messages:
    8,740
    Likes Received:
    0
    Location:
    NoVA
    So I would do it like this then, when the email comes in I would look up the email address in that table you have. If there is a match, then I would update the table with either a Y or a 1 in the emailblock.

    This will let you say give you back all the records where emailblock != 'Y' or emailBlock != 1.

    That would be the most efficient method.
     
  23. Vailripper

    Vailripper Daywalkers have feelings too.

    Joined:
    Jan 25, 2006
    Messages:
    6,170
    Likes Received:
    0
    Location:
    Bellingham, WA
    I concur.
     
  24. Vailripper

    Vailripper Daywalkers have feelings too.

    Joined:
    Jan 25, 2006
    Messages:
    6,170
    Likes Received:
    0
    Location:
    Bellingham, WA
    he meant basically the same thing as a flag. If a field has no value, it's null. So you could check to see if the field is null, if it was, then you would know that the email wasn't blocked, if it wasn't, then you would know that the email was blocked.

    Your best be is just having a bit field which defaults to 0 (not blocked), if someone requests to be removed, set it equal to 1 (blocked). Then when you send out emails, only send emails for those fields where the emailblocked = 0
     
  25. copiertalk

    copiertalk Secure Our Borders! OT Supporter

    Joined:
    Sep 21, 2003
    Messages:
    5,695
    Likes Received:
    0
    Location:
    west texas
    why not add a new boolean field "no_email"?

    if they do not want an email set it to true and exclude that from your search.
     

Share This Page