light Posted February 12, 2008 Share Posted February 12, 2008 I m learning sql.......i want help with this querybooks thar missing either COST or YEAR_ACQUIRED, but not both from the collection table Link to comment Share on other sites More sharing options...
justsomeguy Posted February 12, 2008 Share Posted February 12, 2008 Use XOR, if your database has it.SELECT * FROM table WHERE cost=null XOR year_acquired=nullsomething like that. Link to comment Share on other sites More sharing options...
light Posted February 12, 2008 Author Share Posted February 12, 2008 my database is not recognizing the XOR Link to comment Share on other sites More sharing options...
justsomeguy Posted February 12, 2008 Share Posted February 12, 2008 It might have a different operator that does it, you might want to check the documentation for it. The operator is the "exclusive or" operator. If you don't use XOR it would be like this:SELECT * FROM table WHERE (cost=null AND year_acquired!=null) OR (year_acquired=null AND cost!=null) Link to comment Share on other sites More sharing options...
light Posted February 13, 2008 Author Share Posted February 13, 2008 sorry for late reply...query is workingthank you Link to comment Share on other sites More sharing options...
light Posted February 13, 2008 Author Share Posted February 13, 2008 i m confusing with getting date using below query select colum1,colum2,column3 from table where date_last_out BETWEEN ( '01-NOV-99' AND '30-NOV-99 + 1')actually i m not getting the answerforis there any specific format for date?????plz help me on thatThanks in advance Link to comment Share on other sites More sharing options...
justsomeguy Posted February 13, 2008 Share Posted February 13, 2008 There's not one specific date format, it will recognize several. But the +1 will be causing problems. Check the documentation for your database to see what formats it can process a date in. Link to comment Share on other sites More sharing options...
light Posted February 13, 2008 Author Share Posted February 13, 2008 there is no+ 1 ..sorry that i mistyped. Link to comment Share on other sites More sharing options...
Reg Edit Posted February 13, 2008 Share Posted February 13, 2008 where date_last_out BETWEEN ( '01-NOV-99' AND '30-NOV-99 + 1')The brackets would not work in SQL Server. You would need: where date_last_out BETWEEN '01-NOV-99' AND '30-NOV-99' What database are you using? Link to comment Share on other sites More sharing options...
justsomeguy Posted February 13, 2008 Share Posted February 13, 2008 Oh, that's a good call. BETWEEN requires 2 parameters, and when everything is in parens it sees that as a single parameter. Link to comment Share on other sites More sharing options...
light Posted February 21, 2008 Author Share Posted February 21, 2008 plz help me on this query someoneusing college table, list the patrons with a name that sounds like 'smith' using SOUNDEX and INSTRThanks in advance Link to comment Share on other sites More sharing options...
justsomeguy Posted February 21, 2008 Share Posted February 21, 2008 http://dev.mysql.com/doc/refman/5.0/en/str...unction_soundexINSTR is also on that page. Link to comment Share on other sites More sharing options...
light Posted February 21, 2008 Author Share Posted February 21, 2008 http://dev.mysql.com/doc/refman/5.0/en/str...unction_soundexINSTR is also on that page.THANK YOU FOR THE LINKBUT I M GETTING CONFUSED USING BOTH OF THEM IN QUERY Link to comment Share on other sites More sharing options...
justsomeguy Posted February 21, 2008 Share Posted February 21, 2008 What do you have so far? Link to comment Share on other sites More sharing options...
light Posted February 21, 2008 Author Share Posted February 21, 2008 What do you have so far?I M TRYING LIKE THIS.....SELECT P.NAME FROM CS260.PATRON PWHERE SOUNDEX(P.NAME) = SOUNDEX('SMITH')ORSELECT INSTR(SOUNDEX('SMITH'),2) AS SMITH, P.NAME FROM CS260.PATRON P Link to comment Share on other sites More sharing options...
justsomeguy Posted February 21, 2008 Share Posted February 21, 2008 The first one is close. The soundex function returns a string of arbitrary length, but I think you're only interested in the first part. So you can use instr to figure out if the soundex string from the column contains the soundex string for "smith".SELECT P.NAME FROM CS260.PATRON PWHERE INSTR(SOUNDEX(P.NAME), SOUNDEX('SMITH')) = 1That says to select rows where the soundex string for smith shows up starting on character 1 in the soundex string for the name column. So there could be other characters after that, but you just want to be sure that it starts the same. Link to comment Share on other sites More sharing options...
light Posted February 21, 2008 Author Share Posted February 21, 2008 For some reason it is not working for me.....may be bcz of i m using Oracle?? Link to comment Share on other sites More sharing options...
justsomeguy Posted February 21, 2008 Share Posted February 21, 2008 Maybe so.. that information would have been good to have at the start. The documentation links are for MySQL. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.