Select Where In?

I'm looking at a SQL statement on a page i'm working on and the SQL statement is using a IN. I have never seen this done before and its not working correctly. I'm wondering if it is correct and what the syntax for it is.
[code]
SELECT * FROM Table WHERE Column_Name IN ('ABC','DEF','GHI')
[/code]
Each record has within the Column_Name has one of those 3 values or it could have another value and the select wouldn't pick that record.

David

Comments

  • : I'm looking at a SQL statement on a page i'm working on and the SQL
    : statement is using a IN. I have never seen this done before and its
    : not working correctly. I'm wondering if it is correct and what the
    : syntax for it is.
    : [code]
    : SELECT * FROM Table WHERE Column_Name IN ('ABC','DEF','GHI')
    : [/code]
    Syntax looks OK to me.

    : Each record has within the Column_Name has one of those 3 values or
    : it could have another value and the select wouldn't pick that record.
    You say it has one of those "within" the value. Note that for IN to work it should have that value exactly. Column_Name IN ('ABC','DEF','GHI') is the same as saying:-

    Column_Name = 'ABC' OR Column_Name = 'DEF' OR Column_Name = 'GHI'

    Jonathan

    ###
    for(74,117,115,116){$::a.=chr};(($_.='qwertyui')&&
    (tr/yuiqwert/her anot/))for($::b);for($::c){$_.=$^X;
    /(p.{2}l)/;$_=$1}$::b=~/(..)$/;print("$::a$::b $::c hack$1.");

  • [b][red]This message was edited by davidrtg at 2003-11-18 9:32:13[/red][/b][hr]
    : : I'm looking at a SQL statement on a page i'm working on and the SQL
    : : statement is using a IN. I have never seen this done before and its
    : : not working correctly. I'm wondering if it is correct and what the
    : : syntax for it is.
    : : [code]
    : : SELECT * FROM Table WHERE Column_Name IN ('ABC','DEF','GHI')
    : : [/code]
    : Syntax looks OK to me.
    :
    : : Each record has within the Column_Name has one of those 3 values or
    : : it could have another value and the select wouldn't pick that record.
    : You say it has one of those "within" the value. Note that for IN to work it should have that value exactly. Column_Name IN ('ABC','DEF','GHI') is the same as saying:-
    :
    : Column_Name = 'ABC' OR Column_Name = 'DEF' OR Column_Name = 'GHI'
    :
    Yah, what you are saying here is what I ment =) I'll try the IN again.

    I'm also trying to get it to work with multiple LIKE OR statements and no go.

    This works:
    [code]
    $sth = $dbh->prepare("SELECT Product FROM Dist_Products WHERE Dist = '$thisid' AND (Product LIKE 'EZT' OR Product LIKE 'CHP' OR Product LIKE 'CSR' OR Product LIKE 'PTO' OR Product LIKE 'SGS' OR Product LIKE 'TRL')");
    [/code]
    This doesn't work:
    [code]
    #$sth = $dbh->prepare("SELECT Product FROM Dist_Products WHERE Dist = '$thisid' AND '$products_offerable_search{$site}'");
    [/code]
    Print out of the variable:
    [code]
    print "
    ";


    [/code]

    Thanks again Jonathan!

    David



  • [b][red]This message was edited by davidrtg at 2003-11-18 9:52:35[/red][/b][hr]
    : [b][red]This message was edited by davidrtg at 2003-11-18 9:32:13[/red][/b][hr]
    : : : I'm looking at a SQL statement on a page i'm working on and the SQL
    : : : statement is using a IN. I have never seen this done before and its
    : : : not working correctly. I'm wondering if it is correct and what the
    : : : syntax for it is.
    : : : [code]
    : : : SELECT * FROM Table WHERE Column_Name IN ('ABC','DEF','GHI')
    : : : [/code]
    : : Syntax looks OK to me.
    : :
    : : : Each record has within the Column_Name has one of those 3 values or
    : : : it could have another value and the select wouldn't pick that record.
    : : You say it has one of those "within" the value. Note that for IN to work it should have that value exactly. Column_Name IN ('ABC','DEF','GHI') is the same as saying:-
    : :
    : : Column_Name = 'ABC' OR Column_Name = 'DEF' OR Column_Name = 'GHI'
    : :
    : Yah, what you are saying here is what I ment =) I'll try the IN again.

    Same problem with IN = Works fine when I have it fully written out but when I use the variable it doesn't run.
    [code]
    $sth = $dbh->prepare("SELECT Product FROM Dist_Products WHERE Dist = '$thisid' AND Product IN ('EZT','CHP','CSR','PTO','SGS','TRL')");

    #$sth = $dbh->prepare("SELECT Product FROM Dist_Products WHERE Dist = '$thisid' AND Product IN ($products_offerable{$site})");
    [/code]
    [code]
    print "
    ";


    [/code]

    :
    : I'm also trying to get it to work with multiple LIKE OR statements and no go.
    :
    : This works:
    : [code]
    : $sth = $dbh->prepare("SELECT Product FROM Dist_Products WHERE Dist = '$thisid' AND (Product LIKE 'EZT' OR Product LIKE 'CHP' OR Product LIKE 'CSR' OR Product LIKE 'PTO' OR Product LIKE 'SGS' OR Product LIKE 'TRL')");
    : [/code]
    : This doesn't work:
    : [code]
    : #$sth = $dbh->prepare("SELECT Product FROM Dist_Products WHERE Dist = '$thisid' AND '$products_offerable_search{$site}'");
    : [/code]
    : Print out of the variable:
    : [code]
    : print "
    ";
    :
    :
    : [/code]
    :
    : Thanks again Jonathan!
    :
    : David
    :
    :
    :
    :



  • [b][red]This message was edited by infidel at 2003-11-18 11:47:40[/red][/b][hr]
    The problem is that you're returning a string via a stored procedure. A string result is not an SQL expression. What is your function doing? Perhaps we can help you integrate the logic of the function into the SELECT.

    Also, unless you're going to use pattern matching, there's no reason to use LIKE instead of equality.

    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]



  • : The problem is that you're returning a string via a stored
    : procedure. A string result is not an SQL expression. What is your
    : function doing? Perhaps we can help you integrate the logic of the
    : function into the SELECT.
    AFAIK (I've helped David before) he's using MySQL. We don't have stored procedures in MySQL. Yet (they'll be with us in v5).

    : Also, unless you're going to use pattern matching, there's no reason
    : to use LIKE instead of equality.
    I thought the thing was failing because equality was the wrong thing to check for, which is why I suggested LIKE. I think he's trying to use LIKE as you use IN though, which is the problem (David, I'll respond to your post in a mo...)

    Jonathan


    ###
    for(74,117,115,116){$::a.=chr};(($_.='qwertyui')&&
    (tr/yuiqwert/her anot/))for($::b);for($::c){$_.=$^X;
    /(p.{2}l)/;$_=$1}$::b=~/(..)$/;print("$::a$::b $::c hack$1.");

  • : Same problem with IN = Works fine when I have it fully written out
    : but when I use the variable it doesn't run.
    : [code]
    : $sth = $dbh->prepare("SELECT Product FROM Dist_Products WHERE Dist = '$thisid' AND Product IN ('EZT','CHP','CSR','PTO','SGS','TRL')");
    :
    : #$sth = $dbh->prepare("SELECT Product FROM Dist_Products WHERE Dist = '$thisid' AND Product IN ($products_offerable{$site})");
    : [/code]
    : [code]
    : print "
    ";
    :
    :
    : [/code]
    OK, sounds like you do want to be using IN rather than LIKE - I wasn't quite sure what you wanted to do. Any chance you could do a:-

    print "SELECT Product FROM Dist_Products WHERE Dist = '$thisid' AND Product IN ($products_offerable{$site})";

    And post here what you get from that?

    : : I'm also trying to get it to work with multiple LIKE OR statements
    : : and no go.
    : :
    : : This works:
    : : [code]
    : : $sth = $dbh->prepare("SELECT Product FROM Dist_Products WHERE Dist = '$thisid' AND (Product LIKE 'EZT' OR Product LIKE 'CHP' OR Product LIKE 'CSR' OR Product LIKE 'PTO' OR Product LIKE 'SGS' OR Product LIKE 'TRL')");
    : : [/code]
    : : This doesn't work:
    : : [code]
    : : #$sth = $dbh->prepare("SELECT Product FROM Dist_Products WHERE Dist = '$thisid' AND '$products_offerable_search{$site}'");
    : : [/code]
    : : Print out of the variable:
    : : [code]
    : : print "
    ";
    : :
    : :
    : : [/code]
    Wonder if it's the contents of the $thidid variable? Very curious.

    Jonathan

    ###
    for(74,117,115,116){$::a.=chr};(($_.='qwertyui')&&
    (tr/yuiqwert/her anot/))for($::b);for($::c){$_.=$^X;
    /(p.{2}l)/;$_=$1}$::b=~/(..)$/;print("$::a$::b $::c hack$1.");

  • : : The problem is that you're returning a string via a stored
    : : procedure. A string result is not an SQL expression. What is your
    : : function doing? Perhaps we can help you integrate the logic of the
    : : function into the SELECT.
    : AFAIK (I've helped David before) he's using MySQL. We don't have stored procedures in MySQL. Yet (they'll be with us in v5).
    :
    : : Also, unless you're going to use pattern matching, there's no reason
    : : to use LIKE instead of equality.
    : I thought the thing was failing because equality was the wrong thing to check for, which is why I suggested LIKE. I think he's trying to use LIKE as you use IN though, which is the problem (David, I'll respond to your post in a mo...)
    :
    : Jonathan

    $products_offerable_search{$site} looks like a stored procedure call, especially how he was using it.

    What an ugly syntax for SQL.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

  • : $products_offerable_search{$site} looks like a stored procedure
    : call, especially how he was using it.
    :
    : What an ugly syntax for SQL.
    Nope. He's using Perl, and $product_offerable_search{$site} refers to the hash element in the hash %product_offerable_search identified by the key whose name is stored in the variable $site. The SQL query is written inside double quotes, which allow interpolation to take place. That's one reason why I like Perl so much - it's so easy to drop variables into outputs.

    Jonathan


    ###
    for(74,117,115,116){$::a.=chr};(($_.='qwertyui')&&
    (tr/yuiqwert/her anot/))for($::b);for($::c){$_.=$^X;
    /(p.{2}l)/;$_=$1}$::b=~/(..)$/;print("$::a$::b $::c hack$1.");

  • : : $products_offerable_search{$site} looks like a stored procedure
    : : call, especially how he was using it.
    : :
    : : What an ugly syntax for SQL.
    : Nope. He's using Perl, and $product_offerable_search{$site} refers to the hash element in the hash %product_offerable_search identified by the key whose name is stored in the variable $site. The SQL query is written inside double quotes, which allow interpolation to take place. That's one reason why I like Perl so much - it's so easy to drop variables into outputs.

    Ah, well that explains the ugly syntax ;-)

    Same "hash" value retreival in Python would be:

    product_offerable_search[site]

    Python string replacement can be done in a couple of ways:

    "some sqlstatement where foo = '%s'" % product_offerable_search[site]

    Or, if your values are all in a dictionary (hash), you can use this shortcut that embeds the keys into the string:

    "some sqlstatement where foo = '%(site)s'" % product_offerable_search


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

  • : Ah, well that explains the ugly syntax ;-)
    :
    : Same "hash" value retreival in Python would be:
    :
    : product_offerable_search[site]
    Don't you use [...] for arrays as well? We use [] for array elements in Perl, but a hash is treated kinda differently.

    : Python string replacement can be done in a couple of ways:
    :
    : "some sqlstatement where foo = '%s'" % product_offerable_search[site]
    Which is fine, apart from if you're printing a big block of output. Then you have to look all the way to the end of it to see what the variable is. Or, worse, if you're outputting quite a few and you count wrong, you could end up outputting the wrong thing or the right thing in the wrong place.

    : Or, if your values are all in a dictionary (hash), you can use this
    : shortcut that embeds the keys into the string:
    :
    : "some sqlstatement where foo = '%(site)s'" % product_offerable_search
    Wouldn't %[site]s have been more consistent syntactically?

    Jonathan

    ###
    for(74,117,115,116){$::a.=chr};(($_.='qwertyui')&&
    (tr/yuiqwert/her anot/))for($::b);for($::c){$_.=$^X;
    /(p.{2}l)/;$_=$1}$::b=~/(..)$/;print("$::a$::b $::c hack$1.");

  • : Don't you use [...] for arrays as well? We use [] for array elements in Perl, but a hash is treated kinda differently.

    Yes. I suppose that in the same way the () are the "call operator", you could call [] the "getitem operator". One of the "magic" methods in the list and dictionary classes is __getitem__(self, key). For sequences (strings, lists, tuples) the key must be an integer. Mappings (dictionaries) can have anything as the key. Because [] is therefore the "getitem operator", you can make any class a sequence or mapping simply by including a __getitem__ method (i.e. you don't have to subclass a list or dictionary, although you certainly could).

    : : Python string replacement can be done in a couple of ways:
    : :
    : : "some sqlstatement where foo = '%s'" % product_offerable_search[site]
    : Which is fine, apart from if you're printing a big block of output. Then you have to look all the way to the end of it to see what the variable is. Or, worse, if you're outputting quite a few and you count wrong, you could end up outputting the wrong thing or the right thing in the wrong place.

    Certainly. It's basically a different syntax for what amounts to the printf() function in C.

    : : Or, if your values are all in a dictionary (hash), you can use this
    : : shortcut that embeds the keys into the string:
    : : "some sqlstatement where foo = '%(site)s'" % product_offerable_search
    : Wouldn't %[site]s have been more consistent syntactically?

    I suppose so. I never thought about that. It could just be a leftover from long ago. I'm not sure what the rationale for using parenthesis is - or if there even is one. By now it's probably just a backwards-compatibility thing anyways -- it may not be perfectly consistent with dictionaries, but it doesn't really hurt anything either.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories