mysqldump except one table
Dear all, I am researching about different parameters provided by the *mysqldump* utility. It provides a method to dump databases with all tables. My problem is I want to dump a database having 30 tables except one table i.e i have to dump only 29 tables. Is this possible ? Please guide me how to do this. Thanks Best Regards Adarsh Sharma
Re: mysqldump except one table
Check the manual? Its here under ignore-table http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html Quoting Adarsh Sharma adarsh.sha...@orkash.com: Dear all, I am researching about different parameters provided by the *mysqldump* utility. It provides a method to dump databases with all tables. My problem is I want to dump a database having 30 tables except one table i.e i have to dump only 29 tables. Is this possible ? Please guide me how to do this. Thanks Best Regards Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump except one table
you can use --ignore-table option for this, mysqldump -u -p dbname --ignore-table=dbname.tablename xyz.sql you can use this option multiple times to ignore multiple tables. Rgds, Jay On Tue, Jan 18, 2011 at 6:18 PM, Adarsh Sharma adarsh.sha...@orkash.comwrote: Dear all, I am researching about different parameters provided by the *mysqldump* utility. It provides a method to dump databases with all tables. My problem is I want to dump a database having 30 tables except one table i.e i have to dump only 29 tables. Is this possible ? Please guide me how to do this. Thanks Best Regards Adarsh Sharma
Re: correct way to simulate 'except' query in mysql 4.1
Baron Schwartz wrote: I don't think it will be any better to count distinct values. I think the query is just slow because the index lookups are slow. Is the 'word' column really 150 bytes? huh. it's a varchar(50) on table1 and a varchar(50) on table2. i wonder why explain is reporting 150 as key_len? That's probably the culprit. How slow is this, by the way? this is also interesting. as you can see in the slow query log reported before, it took 94 seconds. i'd say i see between 15 and 90 seconds in the slow query log for this normally. however, i just ran the query now, at a time when the application is not heavily loaded, and it finished quickly - less than a second. another run a few minutes later took around 3 seconds. so there seems to be some interaction with load. 370k rows in one table, verifying the non-existence of index records in a 4M-row table with 150-byte index values... what does slow mean for your application? How big is the index for the 4M-row table (use SHOW TABLE STATUS)? the larger table has 95M index. the smaller has a 5M index. key_buffer is set to 2G, and when i look at top mysql never actually get's above 1.5G, so i'm under the impression that all the indexes are in memory. it's a search table, so it does get a lot of inserts, but slow log never reports any lock time. is there anything else i can investgate? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: correct way to simulate 'except' query in mysql 4.1
Russell Uman wrote: Baron Schwartz wrote: I don't think it will be any better to count distinct values. I think the query is just slow because the index lookups are slow. Is the 'word' column really 150 bytes? huh. it's a varchar(50) on table1 and a varchar(50) on table2. i wonder why explain is reporting 150 as key_len? utf8? That's probably the culprit. How slow is this, by the way? this is also interesting. as you can see in the slow query log reported before, it took 94 seconds. i'd say i see between 15 and 90 seconds in the slow query log for this normally. however, i just ran the query now, at a time when the application is not heavily loaded, and it finished quickly - less than a second. another run a few minutes later took around 3 seconds. so there seems to be some interaction with load. 370k rows in one table, verifying the non-existence of index records in a 4M-row table with 150-byte index values... what does slow mean for your application? How big is the index for the 4M-row table (use SHOW TABLE STATUS)? the larger table has 95M index. the smaller has a 5M index. key_buffer is set to 2G, and when i look at top mysql never actually get's above 1.5G, so i'm under the impression that all the indexes are in memory. it's a search table, so it does get a lot of inserts, but slow log never reports any lock time. is there anything else i can investgate? Do you need utf8? :-) Check your cache hits. I can't remember if you said, but is it an InnoDB table? I'm guessing MyISAM since you have a 2G key buffer. Check key_read_requests and key_reads for the query (mysql-query-profiler is a handy way to do this). Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: correct way to simulate 'except' query in mysql 4.1
huh. it's a varchar(50) on table1 and a varchar(50) on table2. i wonder why explain is reporting 150 as key_len? utf8? yes. that does make sense. is there anything else i can investgate? Do you need utf8? :-) yes. it's an internationalized application :) Check your cache hits. I can't remember if you said, but is it an InnoDB table? I'm guessing MyISAM since you have a 2G key buffer. yes. we do have some tables as innodb - those that get many many inserts and don't require any count(*) queries which as i understand it are slow in innodb - if there's some reason that this kind of query would be faster under innodb i'm happy to give it a try... Check key_read_requests and key_reads for the query (mysql-query-profiler is a handy way to do this). awesome. i will look into it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: correct way to simulate 'except' query in mysql 4.1
I don't think it will be any better to count distinct values. I think the query is just slow because the index lookups are slow. Is the 'word' column really 150 bytes? That's probably the culprit. How slow is this, by the way? 370k rows in one table, verifying the non-existence of index records in a 4M-row table with 150-byte index values... what does slow mean for your application? How big is the index for the 4M-row table (use SHOW TABLE STATUS)? Russell Uman wrote: There's no using distinct, but there is not exists, and in fact no rows are returned. Slow query log reports #Query_time: 94 Lock_time: 0 Rows_sent: 0 Rows_examined: 370220 EXPLAIN: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 150 NULL 338451 Using index 1 SIMPLE t2 ref word word 150 t2.field 4 Using where; Using index; Not exists These are two search tables (hence the large key_len i believe), one with ~400K rows, one row per search term the other with ~4M rows, relating search terms to content. Perhaps I could optimize by doing a count(distinct) on each table and only running the expensive query if the counts don't match? Would I see any benefit by making these InnoDB tables? Thanks for your help with this! Baron Schwartz wrote: Hi, That is the right way, but if you show us the exact output of EXPLAIN we can help more. In particular, does it say Using distinct/not exists in Extra? Russell Uman wrote: howdy. i trying to find items in one table that don't exist in another. i'm using a left join with a where clause to do it: SELECT t1.field, t2.field FROM table1 t1 LEFT JOIN table2 t2 ON t1.word = t2.word WHERE t2.word IS NULL; both tables are quite large and the query is quite slow. the field column is indexed in both tables, and explain shows the indexes being used. is there a better way to construct this kind of query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
correct way to simulate 'except' query in mysql 4.1
howdy. i trying to find items in one table that don't exist in another. i'm using a left join with a where clause to do it: SELECT t1.field, t2.field FROM table1 t1 LEFT JOIN table2 t2 ON t1.word = t2.word WHERE t2.word IS NULL; both tables are quite large and the query is quite slow. the field column is indexed in both tables, and explain shows the indexes being used. is there a better way to construct this kind of query? thank you! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: correct way to simulate 'except' query in mysql 4.1
Hi, Russell Uman wrote: howdy. i trying to find items in one table that don't exist in another. i'm using a left join with a where clause to do it: SELECT t1.field, t2.field FROM table1 t1 LEFT JOIN table2 t2 ON t1.word = t2.word WHERE t2.word IS NULL; both tables are quite large and the query is quite slow. the field column is indexed in both tables, and explain shows the indexes being used. is there a better way to construct this kind of query? That is the right way, but if you show us the exact output of EXPLAIN we can help more. In particular, does it say Using distinct/not exists in Extra? Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: correct way to simulate 'except' query in mysql 4.1
There's no using distinct, but there is not exists, and in fact no rows are returned. Slow query log reports #Query_time: 94 Lock_time: 0 Rows_sent: 0 Rows_examined: 370220 EXPLAIN: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 150 NULL 338451 Using index 1 SIMPLE t2 ref word word 150 t2.field 4 Using where; Using index; Not exists These are two search tables (hence the large key_len i believe), one with ~400K rows, one row per search term the other with ~4M rows, relating search terms to content. Perhaps I could optimize by doing a count(distinct) on each table and only running the expensive query if the counts don't match? Would I see any benefit by making these InnoDB tables? Thanks for your help with this! Baron Schwartz wrote: Hi, That is the right way, but if you show us the exact output of EXPLAIN we can help more. In particular, does it say Using distinct/not exists in Extra? Russell Uman wrote: howdy. i trying to find items in one table that don't exist in another. i'm using a left join with a where clause to do it: SELECT t1.field, t2.field FROM table1 t1 LEFT JOIN table2 t2 ON t1.word = t2.word WHERE t2.word IS NULL; both tables are quite large and the query is quite slow. the field column is indexed in both tables, and explain shows the indexes being used. is there a better way to construct this kind of query? -- russell uman firebus d-_-b -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exclude duplicates except most recent using GROUP BY and HAVING
Maybe I should rephrase this in the form of a question :) I have a table with workstations and timestamps. I'd like to select the record with the most recent timestamp for each workstation. I know there is a simple query to accomplish this but I'm having difficulty. Does anyone have any suggestions? Am I posting on the correct list to get this answered? Thanks. Joel Nimety wrote: The default host table can possibly have multiple records for a given workstation. Presumable the duplicate records are for historical purposes, but only one is actually current. I don't really care why there are duplicates but only need to a query to exclude the duplicate records (leaving the duplicate with the most recent date) and still including the non-duplicated records alone. I think the HAVING clause is the answer but can't seem to craft the appropriate query. SAMPLE OF VIEW AS IT CURRENTLY EXISTS: TAG | EI_EntityID (Primary Key) | Machine Name| Last Activity Date ... CYB1| 000F20D0272D-453E8A1B-030E-0CE7-0734| BBLPTP | Wed Jan 10 10:31:38 EST 2007 CYB1| 000F20D0272D-453E8A1B-0556-F357-0744| BBREDENBERG | Fri May 05 13:37:29 EDT 2006 CYB1| 00508BE168CD-44DA4A44-02A6-4EDB-009E| CAL_LAPTOP | Wed Aug 09 17:49:13 EDT 2006 CYB1| 000F20D0272D-453E8A1B-0365-CEED-02D6| CAL_LAPTOP | Fri Feb 09 09:46:09 EST 2007 CYB1| 00508BE171F3-4587E84F-0540-56B8-003D| CFWRKSTATION| Tue Feb 06 16:14:03 EST 2007 CYB1| 000F20D0272D-453E8A1B-00F7-38CE-0323| CMILLER1-WIN| Mon Apr 24 10:20:41 EDT 2006 ... Please note the CAL_LAPTOP rows. This is an example of the duplication I was talking about above. As you can see the top one is older than the lower one. I need to exclude the older (top) one from the result set because there is a more current record (lower) with the same TAG and MACHINE NAME. SAMPLE DESIRED RESULT SET: TAG | EI_EntityID (Primary Key) | Machine Name| Last Activity Date ... CYB1| 000F20D0272D-453E8A1B-0556-F357-0744| BBREDENBERG | Fri May 05 13:37:29 EDT 2006 CYB1| 000F20D0272D-453E8A1B-0365-CEED-02D6| CAL_LAPTOP | Fri Feb 09 09:46:09 EST 2007 CYB1| 00508BE171F3-4587E84F-0540-56B8-003D| CFWRKSTATION| Tue Feb 06 16:14:03 EST 2007 ... -- Joel Nimety Product Architect 203.541.3416 [EMAIL PROTECTED] http://www.perimeterusa.com -- The sender of this email subscribes to Perimeter Internetworking's email anti-virus service. This email has been scanned for malicious code and is believed to be virus free. For more information on email security please visit: http://www.perimeterusa.com/email-defense-content.html This communication is confidential, intended only for the named recipient(s) above and may contain trade secrets or other information that is exempt from disclosure under applicable law. Any use, dissemination, distribution or copying of this communication by anyone other than the named recipient(s) is strictly prohibited. If you have received this communication in error, please delete the email and immediately notify our Command Center at 203-541-3444. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Exclude duplicates except most recent using GROUP BY and HAVING
The default host table can possibly have multiple records for a given workstation. Presumable the duplicate records are for historical purposes, but only one is actually current. I don't really care why there are duplicates but only need to a query to exclude the duplicate records (leaving the duplicate with the most recent date) and still including the non-duplicated records alone. I think the HAVING clause is the answer but can't seem to craft the appropriate query. SAMPLE OF VIEW AS IT CURRENTLY EXISTS: TAG | EI_EntityID (Primary Key) | Machine Name| Last Activity Date ... CYB1| 000F20D0272D-453E8A1B-030E-0CE7-0734| BBLPTP | Wed Jan 10 10:31:38 EST 2007 CYB1| 000F20D0272D-453E8A1B-0556-F357-0744| BBREDENBERG | Fri May 05 13:37:29 EDT 2006 CYB1| 00508BE168CD-44DA4A44-02A6-4EDB-009E| CAL_LAPTOP | Wed Aug 09 17:49:13 EDT 2006 CYB1| 000F20D0272D-453E8A1B-0365-CEED-02D6| CAL_LAPTOP | Fri Feb 09 09:46:09 EST 2007 CYB1| 00508BE171F3-4587E84F-0540-56B8-003D| CFWRKSTATION| Tue Feb 06 16:14:03 EST 2007 CYB1| 000F20D0272D-453E8A1B-00F7-38CE-0323| CMILLER1-WIN| Mon Apr 24 10:20:41 EDT 2006 ... Please note the CAL_LAPTOP rows. This is an example of the duplication I was talking about above. As you can see the top one is older than the lower one. I need to exclude the older (top) one from the result set because there is a more current record (lower) with the same TAG and MACHINE NAME. SAMPLE DESIRED RESULT SET: TAG | EI_EntityID (Primary Key) | Machine Name| Last Activity Date ... CYB1| 000F20D0272D-453E8A1B-0556-F357-0744| BBREDENBERG | Fri May 05 13:37:29 EDT 2006 CYB1| 000F20D0272D-453E8A1B-0365-CEED-02D6| CAL_LAPTOP | Fri Feb 09 09:46:09 EST 2007 CYB1| 00508BE171F3-4587E84F-0540-56B8-003D| CFWRKSTATION| Tue Feb 06 16:14:03 EST 2007 ... -- Joel Nimety Product Architect 203.541.3416 [EMAIL PROTECTED] http://www.perimeterusa.com -- The sender of this email subscribes to Perimeter Internetworking's email anti-virus service. This email has been scanned for malicious code and is believed to be virus free. For more information on email security please visit: http://www.perimeterusa.com/email-defense-content.html This communication is confidential, intended only for the named recipient(s) above and may contain trade secrets or other information that is exempt from disclosure under applicable law. Any use, dissemination, distribution or copying of this communication by anyone other than the named recipient(s) is strictly prohibited. If you have received this communication in error, please delete the email and immediately notify our Command Center at 203-541-3444. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT all except ... ?
On 09/12/05, Frank Rust [EMAIL PROTECTED] wrote: Is there a possibility to select all columns from a table except one or two columns? For example I have a table with 30 columns and want all columns but one column *not*. Do I have to write a very long select statement with 29 column names that i want to get? If you're using MySQL 5.0 an up you could achieve similar functionality using views. http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-views.html Will. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT all except ... ?
Frank Rust [EMAIL PROTECTED] wrote on 12/09/2005 01:59:35 AM: Is there a possibility to select all columns from a table except one or two columns? For example I have a table with 30 columns and want all columns but one column *not*. Do I have to write a very long select statement with 29 column names that i want to get? -- Frank Rust, Technische Universität, Institut für Theoretische Informatik Tel.: +49 531 391 9525 Postfach 3329, D-38023 Braunschweig Fax.: +49 531 391 9529 Mühlenpfordtstr. 22-23, D-38106 Braunschweig Nope, it's not part of the SQL standard to write a query that way. In fact, I cannot think of a single database (commercial or free) with that capability in their query syntax (and I have used a lot of them). Sorry! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Fw: SELECT all except ... ?
Oops, I meant to send this to the original poster _and_ the list :-) Rhino - Original Message - From: Rhino [EMAIL PROTECTED] To: Frank Rust [EMAIL PROTECTED] Sent: Friday, December 09, 2005 9:02 AM Subject: Re: SELECT all except ... ? - Original Message - From: Frank Rust [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, December 09, 2005 1:59 AM Subject: SELECT all except ... ? Is there a possibility to select all columns from a table except one or two columns? For example I have a table with 30 columns and want all columns but one column *not*. Do I have to write a very long select statement with 29 column names that i want to get? I've heard requests for Select all except for years now but have never seen anyone implement it. Mind you, I've only ever used two SQL databases seriously, DB2 and MySQL, so I can't be sure that Oracle or one of the others doesn't have this ability. The only thing that I've seen which comes close to what you describe is that DB2 has a set of dialogs that can be used to generate SQL. These dialogs let you select your table name(s) from a list, then select your column name(s) from a list, etc. When you select column names, there is a button for selecting all columns in the table(s), which is the equivalent of Select * if you were coding your own SQL. Once you've clicked that button, you can select one, several or all of the columns that were chosen for the query and de-select them again. Therefore, if you clicked Select all, then de-selected one or two of the columns, it would have the same effect as you want. Aside from that, I'm not sure why the syntax you want couldn't be added to the SQL language so maybe you should ask for it via a feature request. I could easily imagine a change to SQL that would allow something like this: select * except e.salary, d.deptname from employee e inner join department d on e.workdept = d.deptno Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date: 09/12/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT all except ... ?
It's not possible in the query, but I wonder if there's a UDF you could write that takes in the name of a table, and then a list of columns NOT to show, and outputs a string of comma-separated values. That'd be neat, so then you could write: SELECT allBut(mytable,badfield1,badfield2,. . .) FROM mytable; Would that even work? Is the query done first, and then the UDF performed? (my apologies for throwing out an idea that may or may not work). -Sheeri On 12/9/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Frank Rust [EMAIL PROTECTED] wrote on 12/09/2005 01:59:35 AM: Is there a possibility to select all columns from a table except one or two columns? For example I have a table with 30 columns and want all columns but one column *not*. Do I have to write a very long select statement with 29 column names that i want to get? -- Frank Rust, Technische Universität, Institut für Theoretische Informatik Tel.: +49 531 391 9525 Postfach 3329, D-38023 Braunschweig Fax.: +49 531 391 9529 Mühlenpfordtstr. 22-23, D-38106 Braunschweig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT all except ... ?
Is there a possibility to select all columns from a table except one or two columns? For example I have a table with 30 columns and want all columns but one column *not*. Do I have to write a very long select statement with 29 column names that i want to get? -- Frank Rust, Technische Universität, Institut für Theoretische Informatik Tel.: +49 531 391 9525 Postfach 3329, D-38023 Braunschweig Fax.: +49 531 391 9529 Mühlenpfordtstr. 22-23, D-38106 Braunschweig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select * except certain columns
Hello, I was wondering if it's possible to select all fields from a table except certain columns, e.g.: select * except text_name from text I have found several discussions on and outside the MySQL mailing lists that suggest it's not possible in (My)SQL. Why is this? It is a feature that would be very useful in several cases, such as: insert into text (select * except text_id, NULL as text_id from text) select text.* except text_content, translatedText.text_content from text, text as translatedText where ... It would be very nice to have this in MySQL. With regards, Mark van 't Zet
Re: Select * except certain columns
- Original Message - From: Mark van 't Zet [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 20, 2004 6:59 AM Subject: Select * except certain columns Hello, I was wondering if it's possible to select all fields from a table except certain columns, e.g.: select * except text_name from text I have found several discussions on and outside the MySQL mailing lists that suggest it's not possible in (My)SQL. Why is this? It is a feature that would be very useful in several cases, such as: insert into text (select * except text_id, NULL as text_id from text) select text.* except text_content, translatedText.text_content from text, text as translatedText where ... It would be very nice to have this in MySQL. I agree that it would be nice to have this feature sometimes but I haven't seen it on any relational database I've ever seen. I'm guessing that it isn't in the SQL standards that most of the database companies use in developing their products. I would suggest two possible approaches, neither of which is likely to give you what you want any time soon: - lobby the relational database vendors to include this sort of syntax in the next version of the SQL standard - lobby one vendor, maybe MySQL, to support the syntax as an extension of the standard. If they implement it and it proves popular, maybe this will give the other vendors an incentive to include it as standard syntax in some future version of the standard Getting a single vendor to include the syntax as an extension might work relatively quickly - say, a year or two - but it might take a good bit longer before it is adopted by other vendors as a standard feature of SQL. Sorry, I'm sure that is not nearly as fast as you'd like to see this feature adopted but I think that is the only way you are likely to see it happen. The only approach that I can imagine that would be considerably quicker would be for you to find or write some kind of preprocessor that allows for this syntax; then, you could write select * except colQ, colZ and the preprocessor would turn it into select colA, colB, colC, ... colP, colR, colS, ... colY. I've never tried to write a preprocessor so I can only begin to imagine how much work it might be to write, test, and implement. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: union, intersct and except operation?
Lana,
You have been asking this question for quite a while now. I think that you
do not have a satisfactory answer yet because I do not believe there is an
EXCEPT operator in the MySQL vocabulary. If you could post a link to the
page from the MySQL manual that shows this operator, we can help you
understand how to use it. Otherwise you need to take a little extra time
to explain what you want out of your data as many of us may not be
familiar with how the EXCEPT operator works in other database systems. (I
know I do not recognize the operator.)
You also need to tell us what version of MySQL you are using as many of
the possible suggested solutions to your query problem could use
version-dependent features of MySQL (like subqueries). Please post the
structure of the table or tables involved in your query. The easiest way
to do that is to use the command SHOW CREATE TABLE with the \G option (not
the ; option). A sample command would be
SHOW CREATE TABLE table \G
(documentation here:
http://dev.mysql.com/doc/mysql/en/SHOW_CREATE_TABLE.html)
Please help us to help you. Thank you for your patience.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
L a n a [EMAIL PROTECTED] wrote on 11/09/2004 08:49:18 PM:
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]
RE: union, intersct and except operation?
Lana, You have been asking this question for quite a while now. I think that you do not have a satisfactory answer yet because I do not believe there is an EXCEPT operator in the MySQL vocabulary. If you could post a link to the page from the MySQL manual that shows this operator, we can help you understand how to use it. Otherwise you need to take a little extra time to explain what you want out of your data as many of us may not be familiar with how the EXCEPT operator works in other database systems. (I know I do not recognize the operator.) INTERSECT and EXCEPT are set operators, similar to UNION, although UNION is the only one currently supported in MySQL (all are ANSI SQL92, but union is most widely supported). Other databases, e.g. Postgres and Oracle among others, do support INTERSECT and EXCEPT. To the best of my knowledge, Michael Stassen's suggestion earlier to use a subquery (meaning you need =4.1) is the only way out in MySQL, although the left join solution is intriguing, and I'd love to hear more about it. So, SELECT T.data_id from table T WHERE T.keyword = 'chemistry' EXCEPT SELECT T2.data_id from table T2 WHERE T2.keyword = 'computers' Would become SELECT T.data_id FROM table T WHERE T.keyword = 'chemistry' AND NOT EXISTS ( SELECT T2.data_id FROM table T2 WHERE T2.keyword = 'computers' AND T2.data_id = T1.data_id ) For more on this workaround, see: http://www.winnetmag.com/Windows/Article/ArticleID/40321/40321.html http://www-db.stanford.edu/~ullman/fcdb/oracle/my-nonstandard.html#intersect http://www.oracle.com/technology/products/rdb/pdf/new_except.pdf And, if you have it, Joe Celko's SQL for Smarties, pp. 414-419. Andy Crain NewsLogic, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to use except operation (corrections for the return results)?
Better: select * from table as t1 left join table as t2 on t1.study=t2.study and not t2.keyword ='K2' where t1.keyword = 'K1' Santino At 23:23 +0100 8-11-2004, Santino wrote: Something like: select * from table as t1, table as t2 where t1.study=t2.study and t1.keyword = 'K1' and not t2.keyword ='K2' Santino At 11:57 -0800 8-11-2004, L a n a wrote: 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- 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]
Re: union, intersct and except operation?
L a n a wrote:
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
Of course. NOT is an operator, not a connector. That is, NOT keyword =
'computers' has the opposite boolean value of keyword = 'computers'. You
still need to connect it to the rest of your conditions with AND or OR.
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'
Adding AND keyword != 'computers' to WHERE keyword = 'chemistry' is
pointless. Any row with keyword = 'chemistry' cannot have 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:
So, you want to choose a Study (not a row) based on looking at *all* the
rows with a particular Study value.
SELECT study FROM table WHERE keyword = 'chemistry' AND keyword
'computers'
returns A, B, C results
with Id = 1, 3, 5. That is, the rows with keyword = 'chemistry'.
SELECT study FROM table WHERE keyword = 'computers' AND keyword
'chemistry'
returns A,B
results in Ids 2 and 4, the rows with keyword = 'computers'.
SELECT study FROM table WHERE keyword = 'chemistry' AND NOT keyword =
'computers'
returns 0 results
Of course. keyword cannot have 2 different values **in the same row**.
Hence, no row matches.
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.
Right, this is not valid syntax.
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.
I doubt that. This is valid syntax, though it doesn't do what you want.
It's close, though. See below.
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 ('+chemistry -computers IN BOOLEAN MODE)
No, that selects a *row* which contains 'chemistry' but not 'computers' in
the fulltext-indexed columns. It does not compare values in one row to
values in another.
What about one field search?
Thank you,
Lana
One solution is similar to your above LEFT JOIN:
SELECT *
FROM table t1 LEFT JOIN table t2
ON t1.Study=t2.Study AND t2.keyword='computers'
WHERE t1.keyword='chemistry' AND t2.id is null;
That may seem a little strange, as we are asking for the opposite of what we
want on the right side of the join, but then we only take the rows from the
left which don't have a row on the right.
Another way to look at all the rows with a particular Study value would be
to GROUP BY Study and use aggregate functions. Something like
SELECT Study
FROM table
GROUP BY Study
HAVING SUM(IF(keyword='chemistry',1,0))
AND NOT SUM(IF(keyword='computers',1,0));
should do the trick.
If you like subqueries and have mysql 4.1, the following should also work:
SELECT * FROM table
WHERE keyword = 'chemistry'
AND Study NOT IN (SELECT Study FROM table t2
WHERE t2.keyword = 'computers');
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: union, intersct and except operation?
SELECT DISTINCT study FROM table WHERE keyword='chemistry';
- Of course if it's 'chemistry' it IS ''computers' or anything else
'chemistry' 'computers'
so last part ot your SQL statement is obsolete
L a n a wrote:
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]
Re: how to use except operation?
L a n a wrote: Hello, I've had a problem: statement SELECT data_id from table WHERE keyword = a NOT keyword =b returns SQL error This makes no sense. You have no column named data_id. You have no columns named a or b. You have no no keywords that have a value of 'a' or 'b'. What exactly are you trying to do? 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) They could not possibly be returning these values, since you don't select Study in any of the above queries. 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 Show what you really have in your tables, your queries, and what is actually returned. Then explain what you wanted. Post ONLY to the list. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to use except operation?
Id Study keyword 1 AK1 2 AK2 3 BK1 4 BK2 5 CK1 6 Ck3 SELECT DISTINCT Study FROM yourtablename WHERE keyword='K1' AND NOT keyword='K2' That *should* give you a single entry for 'C' since its keywords do not match both 'K1' *and* 'K2' But your posting *was* a little confusing, so perhaps you can elaborate a little further on what it is you're trying to accomplish? -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to use except operation?
ian douglas wrote: Id Study keyword 1 AK1 2 AK2 3 BK1 4 BK2 5 CK1 6 Ck3 SELECT DISTINCT Study FROM yourtablename WHERE keyword='K1' AND NOT keyword='K2' That *should* give you a single entry for 'C' since its keywords do not match both 'K1' *and* 'K2' No. Rows 1 and 5 both will be selected. But your posting *was* a little confusing, so perhaps you can elaborate a little further on what it is you're trying to accomplish? -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to use except operation (corrections for the return results)?
Something like: select * from table as t1, table as t2 where t1.study=t2.study and t1.keyword = 'K1' and not t2.keyword ='K2' Santino At 11:57 -0800 8-11-2004, L a n a wrote: 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump all tables except 2 in a database
I would use the --tables option of mysqldump. It accepts more than one table name, so all you need to do is make a list of the tables you want dumped. If you are combining --tables with other options (like -B/--databases or -u or -p) make sure the --tables option is the LAST parameter in the list as everything that occurs after it will be considered a table name. details here - http://dev.mysql.com/doc/mysql/en/mysqldump.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on 08/31/2004 05:57:50 PM: - Original Message - From: Emi Lu [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 31, 2004 4:13 PM Subject: mysqldump all tables except 2 in a database Hello all, In mysql, do we have a way to mysqldump all tables except two in a database. I know we have the way only dump schema, only data, a specific table, both data structure and data. But could someone help me about dumping all tables in a structure except two. For example, in databse D1, I have 10 tables, I'd like to dump 8 of them at one time. You haven't said which version of MySQL you are using or what operating system you are running so this may not be much use to you. I am running MySQL 4.0.15 on Linux Mandrake 8.2. A bash script I posted earlier today could probably be adapted to do what you want. It acquires the names of all of the databases and takes backups of them; all you'd have to do is add an 'if' statement that excluded the two databases that you didn't want to dump. I have attached the script again to this note since it is rather hard to read when it is wrapped in the email editor. Let me know if you have any questions about the script. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump all tables except 2 in a database
Hello all, In mysql, do we have a way to mysqldump all tables except two in a database. I know we have the way only dump schema, only data, a specific table, both data structure and data. But could someone help me about dumping all tables in a structure except two. For example, in databse D1, I have 10 tables, I'd like to dump 8 of them at one time. Thanks a lot! Emi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump all tables except 2 in a database
At 16:13 -0400 8/31/04, Emi Lu wrote: Hello all, In mysql, do we have a way to mysqldump all tables except two in a database. I know we have the way only dump schema, only data, a specific table, both data structure and data. But could someone help me about dumping all tables in a structure except two. For example, in databse D1, I have 10 tables, I'd like to dump 8 of them at one time. On the mysqldump command, name the database followed by the 8 tables you wnat to dump. You cannot say dump all but ... -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump all tables except 2 in a database
- Original Message - From: Emi Lu [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 31, 2004 4:13 PM Subject: mysqldump all tables except 2 in a database Hello all, In mysql, do we have a way to mysqldump all tables except two in a database. I know we have the way only dump schema, only data, a specific table, both data structure and data. But could someone help me about dumping all tables in a structure except two. For example, in databse D1, I have 10 tables, I'd like to dump 8 of them at one time. You haven't said which version of MySQL you are using or what operating system you are running so this may not be much use to you. I am running MySQL 4.0.15 on Linux Mandrake 8.2. A bash script I posted earlier today could probably be adapted to do what you want. It acquires the names of all of the databases and takes backups of them; all you'd have to do is add an 'if' statement that excluded the two databases that you didn't want to dump. I have attached the script again to this note since it is rather hard to read when it is wrapped in the email editor. Let me know if you have any questions about the script. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump all tables except 2 in a database
I have a script on my site that I call smalltables. When run, it echoes out the names of all of the tables _except_ for the large ones that I don't back up. I then use this in the mysqldump command line: /usr/local/mysql/bin/mysqldump -q --user=UUU --host=localhost --password=PPP DB_NAME `smalltables.php` The script runs show tables and filters out those that I don't want to back up. By using an exclusion list, I don't have to add new tables to the list very often. Jeff; On Tue, 31 Aug 2004 15:23:35 -0500, Paul DuBois [EMAIL PROTECTED] said: At 16:13 -0400 8/31/04, Emi Lu wrote: Hello all, In mysql, do we have a way to mysqldump all tables except two in a database. I know we have the way only dump schema, only data, a specific table, both data structure and data. But could someone help me about dumping all tables in a structure except two. For example, in databse D1, I have 10 tables, I'd like to dump 8 of them at one time. On the mysqldump command, name the database followed by the 8 tables you wnat to dump. You cannot say dump all but ... -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Do I use Except?
Hi, I'm building an online fantasy sports game. I want to present a list of players available to purchase. HOwever I need to filter out the players the user already has. I have a table which includes all of the players' information - name, position, price, etc I have a table which includes all of my transactions - managerid, playerid, dateofpurchase, dateofsale I need to select all players in the players table except those that occur in the transactions table which match the 'managerid'. I'm stuck! Any help would be greatfully received! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Do I use Except?
Look up Left JOIN. This join will solve your problem. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Matt MacLeod [mailto:[EMAIL PROTECTED] --Sent: Friday, September 19, 2003 8:38 AM --To: [EMAIL PROTECTED] --Subject: Do I use Except? -- --Hi, -- --I'm building an online fantasy sports game. I want to present a list of --players available to purchase. HOwever I need to filter out the players --the user already has. -- --I have a table which includes all of the players' information - name, --position, price, etc --I have a table which includes all of my transactions - managerid, --playerid, dateofpurchase, dateofsale -- --I need to select all players in the players table except those that --occur in the transactions table which match the 'managerid'. -- --I'm stuck! Any help would be greatfully received! -- --Matt -- -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Do I use Except?
Matt, On most platforms, you would generally do a sub select of the form select playerid from players p where not exists ( select * from myplayers m where m.player_id = p.player_id ) However, as sub selects are only supported in mysql 4.1, you'll need to see section 1.7.4.1 Sub queries in the manual on how to change this into a join supported in mysql prior to 4.1 Andy -Original Message- From: Matt MacLeod [mailto:[EMAIL PROTECTED] Sent: 19 September 2003 16:38 To: [EMAIL PROTECTED] Subject: Do I use Except? Hi, I'm building an online fantasy sports game. I want to present a list of players available to purchase. HOwever I need to filter out the players the user already has. I have a table which includes all of the players' information - name, position, price, etc I have a table which includes all of my transactions - managerid, playerid, dateofpurchase, dateofsale I need to select all players in the players table except those that occur in the transactions table which match the 'managerid'. I'm stuck! Any help would be greatfully received! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT... EXCEPT FOR
If there's not a way to do this I would suggest it be added to the next version of MySQL, but has anyone ever heard of an EXCEPT FOR clause that can be used in MySQL's SELECT statements? For instance... SELECT * FROM products EXCEPT FOR colors WHERE sizes LIKE '%small%' Thanks... Jed Hunsaker [EMAIL PROTECTED]
Re: SELECT... EXCEPT FOR
How about SELECT (column1, column2, column3, etc.) FROM products... I believe something along the lines of EXCEPT FOR would be a huge violation of SQL standards. It might be useful for you, but it's likely that most users would simply declare the desired columns or exclude one programatically. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Jed Hunsaker [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, 14 June, 2003 22:03 Subject: SELECT... EXCEPT FOR If there's not a way to do this I would suggest it be added to the next version of MySQL, but has anyone ever heard of an EXCEPT FOR clause that can be used in MySQL's SELECT statements? For instance... SELECT * FROM products EXCEPT FOR colors WHERE sizes LIKE '%small%' Thanks... Jed Hunsaker [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
except?
I'm working on learning mySQL, and SQL in general. I have a database book from college (just last year, so it's not an old one) and was looking for a way to do a difference between two groups. In my book, it says to use the EXCEPT operation, but either I did it wrong or it doesn't exist. Here's roughly what I typed: (select Computer.CompID, Computer.Location, Computer.User from Computer where Computer.OS like Microsoft*) except all (select Software.CompID from Software where Software.Name = Excel); Basically I want to know what computers in my list don't have the program Excel. Did I do this right, or did I miss something? I did check the manual, but I didn't find anything that looked correct. Brian Ronk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: except?
(select Computer.CompID, Computer.Location, Computer.User from Computer where Computer.OS like Microsoft*) except all (select Software.CompID from Software where Software.Name = Excel); I think your trying to do what mySQL considers a sub-select and mySQL can't handle those yet(AFAIK). There are ways to get around doing sub-selects but I don't what they are. Please someone correct me if I'm wrong HTH, Ryan __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
EXCEPT, NOT IN
hi MySQL 3.23.52 could not make my way throuh the JOIN statement. i would like to accomplish something like what EXCEPT does in other databases, with a full SQL-statement after the EXCEPT. xxxIDref is a foreign key. first i have a table with users ID Name 1 John Doe 2 Jane Doe 3 Me Myself then i have one table with questions. ID Text 1 What is ... 2 Who is ... 3 Question 3 and i have a table with possible answers ID QuestionIDref Text 1 1 Yes 2 1 No 3 2 Me 4 2 You 5 3 Well 6 3 not well when a user anwers somthing to a question the result is saved in a table called answerToQuestion ID QuestionIDref AnswerIDref PersonIDref 1 1 1 1 2 1 2 2 3 2 3 2 now i would like to find out that person 1 has not answerd question 2 and 3 and person 2 has not answered to question 3, and that person 3 has not answerd to any question. i would like something like (to get person 3's remaining questions) SELECT q.ID FROM questions AS q1 EXCEPT ( SELECT q2.ID FROM questions AS q2, answerToQuestion AS atq WHERE atq.PersonIDref=3 ); however the LEFT JOIN statement in MySQL seems not allow anything like WHERE atq.PersonIDref=3. the problem is that if i write: SELECTq.ID FROM questions AS q LEFT JOIN answerToQuestion ON q.questionID=answerToQuestion.QuestionIDref WHERE resultQuestion.questionIDref IS NULL ORDER BY q.questionID; this does not select the remaining question for a specific person. so i would need somthing like: SELECTq.ID FROM questions AS q LEFT JOIN answerToQuestion ON q.questionID=answerToQuestion.QuestionIDref WHERE answerToQuestion.questionIDref IS NULL AND answerToQuestion.PersonIDref=3 ORDER BY q.questionID; but this does not produce any answer. also tried to use IN instead of AND to select the current person, but still no luck. any help is appreciated. stig _ Gratis e-mail resten av livet på www.yahoo.se/mail Busenkelt! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
EXCEPT, NOT IN
hi MySQL 3.23.52 could not make my way throuh the JOIN statement. i would like to accomplish something like what EXCEPT does in other databases, with a full SQL-statement after the EXCEPT. xxxIDref is a foreign key. first i have a table with users ID Name 1 John Doe 2 Jane Doe 3 Me Myself then i have one table with questions. ID Text 1 What is ... 2 Who is ... 3 Question 3 and i have a table with possible answers ID QuestionIDref Text 1 1 Yes 2 1 No 3 2 Me 4 2 You 5 3 Well 6 3 not well when a user anwers somthing to a question the result is saved in a table called answerToQuestion ID QuestionIDref AnswerIDref PersonIDref 1 1 1 1 2 1 2 2 3 2 3 2 now i would like to find out that person 1 has not answerd question 2 and 3 and person 2 has not answered to question 3, and that person 3 has not answerd to any question. i would like something like (to get person 3's remaining questions) SELECT q.ID FROM questions AS q1 EXCEPT ( SELECT q2.ID FROM questions AS q2, answerToQuestion AS atq WHERE atq.PersonIDref=3 ); however the LEFT JOIN statement in MySQL seems not allow anything like WHERE atq.PersonIDref=3. the problem is that if i write: SELECTq.ID FROM questions AS q LEFT JOIN answerToQuestion ON q.questionID=answerToQuestion.QuestionIDref WHERE resultQuestion.questionIDref IS NULL ORDER BY q.questionID; this does not select the remaining question for a specific person. so i would need somthing like: SELECTq.ID FROM questions AS q LEFT JOIN answerToQuestion ON q.questionID=answerToQuestion.QuestionIDref WHERE answerToQuestion.questionIDref IS NULL AND answerToQuestion.PersonIDref=3 ORDER BY q.questionID; but this does not produce any answer. also tried to use IN instead of AND to select the current person, but still no luck. any help is appreciated. stig _ Gratis e-mail resten av livet på www.yahoo.se/mail Busenkelt! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
EXCEPT, NOT IN, JOIN
hi MySQL 3.23.52 could not make my way throuh the JOIN statement. i would like to accomplish something like what EXCEPT does in other databases, with a full SQL-statement after the EXCEPT. xxxIDref is a foreign key. first i have a table with users ID Name 1 John Doe 2 Jane Doe 3 Me Myself then i have one table with questions. ID Text 1 What is ... 2 Who is ... 3 Question 3 and i have a table with possible answers ID QuestionIDref Text 1 1 Yes 2 1 No 3 2 Me 4 2 You 5 3 Well 6 3 not well when a user anwers somthing to a question the result is saved in a table called answerToQuestion ID QuestionIDref AnswerIDref PersonIDref 1 1 1 1 2 1 2 2 3 2 3 2 now i would like to find out that person 1 has not answerd question 2 and 3 and person 2 has not answered to question 3, and that person 3 has not answerd to any question. i would like something like (to get person 3's remaining questions) SELECT q.ID FROM questions AS q1 EXCEPT ( SELECT q2.ID FROM questions AS q2, answerToQuestion AS atq WHERE atq.PersonIDref=3 ); however the LEFT JOIN statement in MySQL seems not allow anything like WHERE atq.PersonIDref=3. the problem is that if i write: SELECTq.ID FROM questions AS q LEFT JOIN answerToQuestion ON q.questionID=answerToQuestion.QuestionIDref WHERE resultQuestion.questionIDref IS NULL ORDER BY q.questionID; this does not select the remaining question for a specific person. so i would need somthing like: SELECTq.ID FROM questions AS q LEFT JOIN answerToQuestion ON q.questionID=answerToQuestion.QuestionIDref WHERE answerToQuestion.questionIDref IS NULL AND answerToQuestion.PersonIDref=3 ORDER BY q.questionID; but this does not produce any answer. also tried to use IN instead of AND to select the current person, but still no luck. any help is appreciated. stig _ Gratis e-mail resten av livet på www.yahoo.se/mail Busenkelt! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select except
On Sun, 19 May 2002, Jule wrote: Hey guys and gals, is there a query that selects all comuns from a table, except the one's i have defined? Jule No, there isn't. It is safter if you name the columns you want. If you use a select * in your code instead of naming the particular columns you want, you could be in for some unpleasent surprises if the table is altered in the future. Naming the columns also makes your code easier to understand. Regards, Jason [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
select except
Hey guys and gals, is there a query that selects all comuns from a table, except the one's i have defined? Jule -- |\/\__/\/| | Jule Slootbeek | | [EMAIL PROTECTED]| | http://blindtheory.cjb.net | | __ | |/\/ \/\| - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: EXCEPT statement in MYSQL?
On Tue, May 15, 2001 at 04:45:22PM +0200, Viktor van den Berg wrote: Hi, I am new to this list, so maybe this question is asked before. I like to know how to use the except statement in MYSQL. EXCEPT is (almost?) the same as an exlusive or (XOR): select userid from user1 except select userid from user2 The result is a data set containing userid's that are only availlable in user1 or in user2. If the userid is availlable in user1 AND user2, then it won't be availlable in the result. The question is what syntax to use in MYSQL to achieve this result! One of the possible ways would be SELECT u1.userid FROM user1 u1 LEFT JOIN user2 u2 ON u2.userid=u1.userid WHERE u2.userid IS NULL; G'luck, Peter -- This inert sentence is my body, but my soul is alive, dancing in the sparks of your brain. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: cant connect except as root
Willie Strickland wrote: I have just installed MySQL for the first time on my RedHat linux 7.0 machine. I thought it all went fine and was working. However, now I realize that only root can start the client program mysql (or the server program for that matter, but I figure that is a good thing). Root can login using any of the other valid users and mysql -u someuser. But someuser cannot connect except by su to root then connect using mysql -u someuser. I spent several hours yesterday trying to research this on the internet and in my reference books but didnt get it resolved. Where did I go wrong? How do I correct it? Thanks, Willie -- [EMAIL PROTECTED] If you loaded MySQL rpm from the RH 7.0 CD, go to redhat.com or mysql.com for an rpm that works. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT * EXCEPT field1, field2
Jack Dempsey wrote: Hi all, I'm wondering if it's possible to do something like that select line in the subject...often i want to select * but not one or two fields...i found something about this in the to do list for mysql4.0, but i'm guessing that's not exactly what i'm thinking...is there an easy way to do this? thanks so much...hope this isn't to dumb of a question... Jack Dempsey No - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Selecting rows with same data in all fields except one
Hello, I have a table which consists of 5 columns. There are a large number of rows that have the same values for the first 4 columns and the last column is the only thing that differs. I would like to grab all the rows that have these identical columns so I can store it into one rows with all the different values in the last column of just one row. What would the syntax be to grab all the rows that have identical data in the first 4 columns? I know this is pretty trivial but everthing I try seems to be wrong. Thanks for your help. Greg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
