mySQL 4.1.3-beta subqueries
Hello, Since I had a problem with except operator and subqueries, I investigated a topic on mysql version capability. I tried a few examples which were offered on this mailing list, all of them gave me a syntax error, so I've read a manual and tried some examples from it. However, things that must work still doesn't work I got frustrated... please help... I have the following two tables in mySQL 4.1.3-beta : CREATE TABLE `user_info` ( `comments` varchar(250) default '', `user_id` int(11) NOT NULL auto_increment, `login_name` varchar(20) default NULL, `user_passwd` varchar(20) default NULL, PRIMARY KEY (`user_id`), KEY `user_id` (`user_id`) ) TYPE=MyISAM; CREATE TABLE `user_info` ( `comments` varchar(250) default '', `user_id` int(11) NOT NULL auto_increment, `login_name` varchar(20) default NULL, `user_passwd` varchar(20) default NULL, PRIMARY KEY (`user_id`), KEY `user_id` (`user_id`) ) TYPE=MyISAM; I've read the manual and there are two examples: 1. SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2); 2. SELECT * FROM t1 WHERE (column1,column2) IN (SELECT column1,column2 FROM t2); I adjusted them to my tables and tested as the following: 1.$sql = SELECT * from user_info WHERE login_name = ANY ( SELECT login_name from new_user_info); 2.$sql = SELECT * from user_info WHERE (login_name, user_passwd) IN ( SELECT login_name, user_passwd FROM new_user_info); It gives the following error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'user_passwd) IN ( SELECT login_name,user_passwd FROM new_user_i Why? What's wrong? Can anyone help? Thank you, Lana -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mySQL 4.1.3-beta subqueries(correction on table names)
Hello, Since I had a problem with except operator and subqueries, I investigated a topic on mysql version capability. I tried a few examples which were offered on this mailing list, all of them gave me a syntax error, so I've read a manual and tried some examples from it. However, things that must work still doesn't work I got frustrated... please help... I have the following two tables in mySQL 4.1.3-beta : CREATE TABLE `user_info` ( `comments` varchar(250) default '', `user_id` int(11) NOT NULL auto_increment, `login_name` varchar(20) default NULL, `user_passwd` varchar(20) default NULL, PRIMARY KEY (`user_id`), KEY `user_id` (`user_id`) ) TYPE=MyISAM; CREATE TABLE `new_user_info` ( `comments` varchar(250) default '', `user_id` int(11) NOT NULL auto_increment, `login_name` varchar(20) default NULL, `user_passwd` varchar(20) default NULL, PRIMARY KEY (`user_id`), KEY `user_id` (`user_id`) ) TYPE=MyISAM; Basically two tables contain same structured info for old and new users. I've read the manual and there are two examples: 1. SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2); 2. SELECT * FROM t1 WHERE (column1,column2) IN (SELECT column1,column2 FROM t2); I adjusted them to my tables and tested as the following: 1.$sql = SELECT * from user_info WHERE login_name = ANY ( SELECT login_name from new_user_info); 2.$sql = SELECT * from user_info WHERE (login_name, user_passwd) IN ( SELECT login_name, user_passwd FROM new_user_info); It gives the following error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'user_passwd) IN ( SELECT login_name,user_passwd FROM new_user_i Why? What's wrong? Can anyone help? Thank you, Lana -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
union, intersct and except operation?
Hello,
I've had a problem trying to get working except operator in mysql:
statement SELECT study from table WHERE keyword = 'chemistry' NOT keyword
= 'computers' returns SQL error
I've got the following not working solutions to my problem:
SELECT [distinct] study FROM table WHERE keyword = 'chemistry' AND keyword
(or !=) 'computers'
SELECT study FROM table WHERE keyword = 'chemistry' AND NOT (or OR NOT)
keyword = 'computers'
To explain better what result I need to get, there is table
Id Study keyword
1 Achemistry
2 Acomputers
3 Bchemistry
4 Bcomputers
5 Cchemistry
I need to return study C, because it has only 'chemistry' keyword(not
'computers'). However, all the suggested solutions return me wrong results:
SELECT study FROM table WHERE keyword = 'chemistry' AND keyword
'computers'
returns A, B, C results
SELECT study FROM table WHERE keyword = 'computers' AND keyword
'chemistry'
returns A,B
SELECT study FROM table WHERE keyword = 'chemistry' AND NOT keyword =
'computers'
returns 0 results
I tried SELECT T.data_id from table T WHERE T.keyword = 'chemistry' EXCEPT
SELECT T2.data_id from table T2 WHERE T2.keyword =computers . But it gives
SQL error.
I, also, tried SELECT * from table as t1 left join table as t2 on
t1.study=t2.study and not t2.keyword ='computers' where t1.keyword =
'chemistry' - gives SQL error as well.
Is there a way to get exception (not) operator working in mysql at all,
does it mean that only Union (or) and Intersect (and) available?
I know that in fulltext search it's possible to do: sql = SELECT ...
MATCH...AGAINST ('+chemestry -omputers IN BOOLEAN MODE)
What about one field search?
Thank you,
Lana
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to use except operation?
Hello, I've had a problem: statement SELECT data_id from table WHERE keyword = a NOT keyword =b returns SQL error I've got the following not working solutions to my problem: SELECT data_id FROM table WHERE keyword = a AND keyword (or !=) b SELECT data_id FROM table WHERE keyword = a AND NOT (or OR NOT) keyword = b To explain better what result I need to get, there is table Id Study keyword 1 AK1 2 AK2 3 BK1 4 BK2 5 CK1 6 Ck3 I need to return study C, because it has only K1 keyword. However, all the sugested solutions return me A, B (and doesn't return C) How to solve the problem? I tried SELECT T.data_id from table T WHERE T.keyword = a EXCEPT SELECT T2.data_id from table T2 WHERE T2.keyword =b . But it gives SQL error. Any thoughts? Thank you, Lana -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to use except operation (corrections for the return results)?
Hello, I've had a problem: statement SELECT data_id from table WHERE keyword = a NOT keyword = b returns SQL error I've got the following not working solutions to my problem: SELECT data_id FROM table WHERE keyword = a AND keyword (or !=) b SELECT data_id FROM table WHERE keyword = a AND NOT (or OR NOT) keyword = b To explain better what result I need to get, there is table Id Study keyword 1 AK1 2 AK2 3 BK1 4 BK2 5 CK1 6 Ck3 I need to return study C, because it has only K1 keyword. However, all the suggested solutions return me A, B, C in the following order: if I ask for K1/K2 then it returns studies A, B; if I ask for K2/K1 (order matters) then it returns A, B, C. How to solve the problem? I tried SELECT T.data_id from table T WHERE T.keyword = a EXCEPT SELECT T2.data_id from table T2 WHERE T2.keyword =b . But it gives SQL error. Any thoughts? Thank you, Lana -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql NOT operator
Hello, Could you please tell me how I can write an sql statement in php when I'd llike to select boolean search in one field like except or NOT result. What I mean here is that I can execute the following: 1. SELECT data_id from table WHERE keyword = a AND keyword =b 2. SELECT data_id from table WHERE keyword = a OR keyword =b However, NOT operator gives an error: 3. SELECT data_id from table WHERE keyword = a NOT keyword =b ( returns sql error) Could you please help? Thank you, Lana -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to intersect resulting sets form sql query
Hello, I'm trying to find a resulting set for searching two keywords, like: Select * from table where filed_a like '%keyword1%' OR field_b like '%keyword1%' INTERSECT (Select * from table where filed_a like '%keyword2%' OR field_b like '%keyword2%'). This statement gives an error in syntax. What is right way to write this kind of logic in query? Thank you, _ MSN Premium: Up to 11 personalized e-mail addresses and 2 months FREE* http://join.msn.com/?pgmarket=en-capage=byoa/premxAPID=1994DI=1034SU=http://hotmail.com/encaHL=Market_MSNIS_Taglines -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
deleting duplicates from table
Hello, I'm trying to solve a task in Mysql 4: I have a table rec_id | record_ref | keyword ** 1| 2| apple 2| 2|orange 3| 2|apple 4| 3|mango ** How can I delete records where pair (record_ref and keyword) are identical? In this case either record #1 or #3, but not both. Thank you, Lana _ Add photos to your messages with MSN Premium. Get 2 months FREE* http://join.msn.com/?pgmarket=en-capage=byoa/premxAPID=1994DI=1034SU=http://hotmail.com/encaHL=Market_MSNIS_Taglines -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
deleting duplicates from table
Hello, I'm trying to solve a task in Mysql 4: I have a table rec_id | record_ref | keyword ** 1| 2| apple 2| 2|orange 3| 2|apple 4| 3|mango ** How can I delete records where pair (record_ref and keyword) are identical? In this case either record #1 or #3, but not both. Thank you, Lana _ Free yourself from those irritating pop-up ads with MSn Premium. Get 2months FREE* http://join.msn.com/?pgmarket=en-capage=byoa/premxAPID=1994DI=1034SU=http://hotmail.com/encaHL=Market_MSNIS_Taglines -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
return substrings out of a string
Hello, Im working on a search function using PHP4 and MYSQL4. Im looking for a function that allow to take a string value (consisted of several substrings separated by coma) out of db field and return each substring one by one. Could you please tell me if there is a function that can do something similar to SELECT function_name (field_name, ,) (return)- substr1, substr2, substr3 Thank you for your help, Lana _ Add photos to your messages with MSN Premium. Get 2 months FREE* http://join.msn.com/?pgmarket=en-capage=byoa/premxAPID=1994DI=1034SU=http://hotmail.com/encaHL=Market_MSNIS_Taglines -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
hello
testing
