locked
Combining "AND" and "OR" statements in SQL RRS feed

  • Question

  • I'm used to Visual FoxPro (VFP) coding and am just learning SQL.  In VFP, I can say: SELECT name FROM table1 WHERE city = "Chicago" AND (street = "Elm" OR street = "Maple")    with the parentheses in those positions.  How would I combine this statement in SQL?  - Thanks.


    Jim Fuchs, Davenport, IA

    Thursday, November 13, 2014 6:52 PM

Answers

  • or can simplify it using IN operator as

    SELECT NAME
    FROM table1
    WHERE city = 'Chicago'
    AND street IN ('Elm','Maple')


    Please Mark This As Answer if it solved your issue
    Please Mark This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Ashish.Upadhyay Thursday, November 13, 2014 8:54 PM
    • Marked as answer by Jim Fuchs 28 Thursday, November 13, 2014 9:19 PM
    Thursday, November 13, 2014 7:13 PM
    Answerer
  • It is same in SQL. Only double quotes should be replaced with single quotes.

    SELECT NAME
    FROM table1
    WHERE city = 'Chicago'
    	AND (
    		street = 'Elm'
    		OR street = 'Maple'
    		)


    -Vaibhav Chaudhari

    • Proposed as answer by Ashish.Upadhyay Thursday, November 13, 2014 8:54 PM
    • Marked as answer by Jim Fuchs 28 Thursday, November 13, 2014 9:19 PM
    Thursday, November 13, 2014 7:03 PM

All replies

  • It is same in SQL. Only double quotes should be replaced with single quotes.

    SELECT NAME
    FROM table1
    WHERE city = 'Chicago'
    	AND (
    		street = 'Elm'
    		OR street = 'Maple'
    		)


    -Vaibhav Chaudhari

    • Proposed as answer by Ashish.Upadhyay Thursday, November 13, 2014 8:54 PM
    • Marked as answer by Jim Fuchs 28 Thursday, November 13, 2014 9:19 PM
    Thursday, November 13, 2014 7:03 PM
  • or can simplify it using IN operator as

    SELECT NAME
    FROM table1
    WHERE city = 'Chicago'
    AND street IN ('Elm','Maple')


    Please Mark This As Answer if it solved your issue
    Please Mark This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Ashish.Upadhyay Thursday, November 13, 2014 8:54 PM
    • Marked as answer by Jim Fuchs 28 Thursday, November 13, 2014 9:19 PM
    Thursday, November 13, 2014 7:13 PM
    Answerer
  • Here's what I'm really trying to do: . . . (fldEntityType = 'Composite') AND (fldYearsContracted IN ('X','Y','Z','A'))  . . . this works with no errors but only gives me 'X'
    
    
    
    
    

    Jim Fuchs, Davenport, IA

    Thursday, November 13, 2014 7:34 PM
  • Hi,

      That should work unless there is no other matching data. If there is data with those conditions, you can check that the database or columns are not case sensitive.

       A simple test for case sensitivity could be :

     fldEntityType = 'Composite' AND fldYearsContracted IN ('X','Y','y','A','a') 

    Notice the small letters.

    Hope that helps.


    og-bit.com

    Thursday, November 13, 2014 7:43 PM
  • What I suspect you really did was replace "'X','Y','Z','A'" with a variable.  SQL syntax does not allow you do that.

    You need to split the comma delimited string into a table, then join the table.

    Thursday, November 13, 2014 7:44 PM
    Answerer
  • Hi Jim 

    If you want us to check you specific issue then you can post a query to create the table, and a query to insert some sample data. with this info, we can reproduce your issue. Nu as much as I can see you got answers from the start. Did you cheed them?


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]

    Thursday, November 13, 2014 7:51 PM
  • og-bit, the data is all uppercase - not worried about that. Tom, putting X, Y, Z, and A in a table and joining it is an interesting tack, but I'm already joining five tables in this query and don't want to muck it up any further.  I'm looking for X, Y, Z, and A in one named field in tblContracts and only X and Y in another named field in tblComposites so tblPeopleEntity.fldEntityType = 'Contract' OR tblPeopleEntity.fldEntityType = 'Composite' AND tblContracts.fldYearsContracted = 'X', 'Y', 'Z', or 'A' OR tblComposite,fldActiveYear = 'X' or 'Y'.  In a nutshell this is like entity = 'City' OR entity = 'Town' AND name = 'Chicago' OR name = "ChiTown'    I'm really appreciative of all the replies so far.  Thanks!
    

    Jim Fuchs, Davenport, IA

    Thursday, November 13, 2014 8:04 PM
  • I think I'll just select all records then simply delete the ones I DON'T want - years other than X, Y, Z, and A. That seems to be the quickest, easiest thing to do. It's just a one-time need.

    Jim Fuchs, Davenport, IA

    Thursday, November 13, 2014 8:19 PM
  • Thanks! Appreciate it!

    Jim Fuchs, Davenport, IA

    Thursday, November 13, 2014 9:21 PM
  • 
    Thanks. Appreciate it.

    Jim Fuchs, Davenport, IA

    Thursday, November 13, 2014 9:23 PM
  • Thanks. Appreciate it!

    Jim Fuchs, Davenport, IA

    Thursday, November 13, 2014 9:23 PM
  • You are most welcome :-)

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]

    Saturday, November 15, 2014 8:03 AM
  • og-bit, the data is all uppercase - not worried about that. Tom, putting X, Y, Z, and A in a table and joining it is an interesting tack, but I'm already joining five tables in this query and don't want to muck it up any further.  I'm looking for X, Y, Z, and A in one named field in tblContracts and only X and Y in another named field in tblComposites so tblPeopleEntity.fldEntityType = 'Contract' OR tblPeopleEntity.fldEntityType = 'Composite' AND tblContracts.fldYearsContracted = 'X', 'Y', 'Z', or 'A' OR tblComposite,fldActiveYear = 'X' or 'Y'.  In a nutshell this is like entity = 'City' OR entity = 'Town' AND name = 'Chicago' OR name = "ChiTown'    I'm really appreciative of all the replies so far.  Thanks!
    

    Jim Fuchs, Davenport, IA

    I think this?

    (tblPeopleEntity.fldEntityType = 'Contract' OR tblPeopleEntity.fldEntityType = 'Composite')
    AND (tblContracts.fldYearsContracted IN ('X', 'Y', 'Z', 'A') 
    OR tblComposite,fldActiveYear IN ('X','Y')


    Please Mark This As Answer if it solved your issue
    Please Mark This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Saturday, November 15, 2014 8:35 AM
    Answerer