help with sql statement
in [General]
|
Prev: Recupero di pen drive e memory card non più accessibili
Next: Looking for a little MySQL help as well
From: Adam on 12 Jul 2010 11:24 I was google searching, and the only SQL mailing list I found is currently giving a 503 error, so I hope you don't mind me asking my SQL question here, since there are a lot of SQL gurus here. I am having a syntax problem: Instead of doing a query like this:: select SMS_R_SYSTEM.Name from SMS_R_System where (SMS_R_System.SystemOUName = "example.com/COMPUTERS/MAIN CAMPUS/ABC") or (SMS_R_System.SystemOUName = "example.com/COMPUTERS/MAIN CAMPUS/XYZ") I'd like to shorten it in the where clause to: select SMS_R_SYSTEM.Name from SMS_R_System where (SMS_R_System.SystemOUName = "example.com/COMPUTERS/MAIN CAMPUS/ABC", "example.com/COMPUTERS/MAIN CAMPUS/XYZ") But I'm getting a syntax error. Any idea why my SQL syntax isn't valid?
From: Ashley Sheridan on 12 Jul 2010 11:26 On Mon, 2010-07-12 at 10:24 -0500, Adam wrote: > I was google searching, and the only SQL mailing list I found is > currently giving a 503 error, so I hope you don't mind me asking my SQL > question here, since there are a lot of SQL gurus here. I am having a > syntax problem: > > Instead of doing a query like this:: > > select SMS_R_SYSTEM.Name from SMS_R_System where > (SMS_R_System.SystemOUName = "example.com/COMPUTERS/MAIN CAMPUS/ABC") or > (SMS_R_System.SystemOUName = "example.com/COMPUTERS/MAIN CAMPUS/XYZ") > > I'd like to shorten it in the where clause to: > > select SMS_R_SYSTEM.Name from SMS_R_System where > (SMS_R_System.SystemOUName = "example.com/COMPUTERS/MAIN CAMPUS/ABC", > "example.com/COMPUTERS/MAIN CAMPUS/XYZ") > > But I'm getting a syntax error. Any idea why my SQL syntax isn't valid? > > > The short answer is your syntax isn't valid, which means that what you've written isn't valid SQL :p What I think you're looking for instead is something like this: SELECT SMS_R_SYSTEM.Name FROM SMS_R_System WHERE SMS_R_System.SystemOUName IN ("example.com/COMPUTERS/MAIN CAMPUS/ABC", "example.com/COMPUTERS/MAIN CAMPUS/XYZ") which lets MySQL compare the field against an array of different values within the brackets. Thanks, Ash http://www.ashleysheridan.co.uk
From: "Tommy Pham" on 12 Jul 2010 13:34 > -----Original Message----- > From: Ashley Sheridan [mailto:ash(a)ashleysheridan.co.uk] > Sent: Monday, July 12, 2010 8:26 AM > To: Adam > Cc: PHP General > Subject: Re: [PHP] help with sql statement > > On Mon, 2010-07-12 at 10:24 -0500, Adam wrote: > > > I was google searching, and the only SQL mailing list I found is > > currently giving a 503 error, so I hope you don't mind me asking my > > SQL question here, since there are a lot of SQL gurus here. I am > > having a syntax problem: > > > > Instead of doing a query like this:: > > > > select SMS_R_SYSTEM.Name from SMS_R_System where > > (SMS_R_System.SystemOUName = "example.com/COMPUTERS/MAIN > CAMPUS/ABC") > > or (SMS_R_System.SystemOUName = "example.com/COMPUTERS/MAIN > > CAMPUS/XYZ") > > > > I'd like to shorten it in the where clause to: > > > > select SMS_R_SYSTEM.Name from SMS_R_System where > > (SMS_R_System.SystemOUName = "example.com/COMPUTERS/MAIN > CAMPUS/ABC", > > "example.com/COMPUTERS/MAIN CAMPUS/XYZ") > > > > But I'm getting a syntax error. Any idea why my SQL syntax isn't valid? > > > > > > > > > The short answer is your syntax isn't valid, which means that what you've > written isn't valid SQL :p > > What I think you're looking for instead is something like this: > > SELECT SMS_R_SYSTEM.Name FROM SMS_R_System WHERE > SMS_R_System.SystemOUName IN ("example.com/COMPUTERS/MAIN > CAMPUS/ABC", "example.com/COMPUTERS/MAIN CAMPUS/XYZ") > Even shorter ;) SELECT srs.Name FROM SMS_R_System srs WHERE srs.SystemOUName IN ("example.com/COMPUTERS/MAIN CAMPUS/ABC", "example.com/COMPUTERS/MAIN CAMPUS/XYZ") > which lets MySQL compare the field against an array of different values > within the brackets. Given his syntax, I'd guess that his RDBMS is MS SQL and he's trying to get some info from the DB that's populated by MS' SMS. Regards, Tommy > > Thanks, > Ash > http://www.ashleysheridan.co.uk >
From: Richard Quadling on 13 Jul 2010 05:13
On 12 July 2010 18:34, Tommy Pham <tommyhp2(a)gmail.com> wrote: > SELECT srs.Name FROM SMS_R_System srs WHERE srs.SystemOUName IN > ("example.com/COMPUTERS/MAIN CAMPUS/ABC", "example.com/COMPUTERS/MAIN > CAMPUS/XYZ") > As this is a single table query, there is no need for the table alias. SELECT Name FROM SMS_R_System WHERE LEFT(SystemOUName, 34) = 'example.com/COMPUTERS/MAIN CAMPUS/' AND RIGHT(SystemOUName, 3) IN ('ABC', 'XYZ') But this will probably take a measurable amount of time longer to execute - 2 comparisons and 2 string processes. Maybe not for a single run, but after several hundred. |