[MYSQL] INTERSECT, MINUS
WHat is wring with the following three sentences? SELECT p.name, p.gender, e.pizza FROM Person p, Eats e WHERE p.name = e.name AND p.gender = 'female' AND (e.pizza = 'mushroom') INTERSECT SELECT p.name, p.gender, e.pizza FROM Person p, Eats e WHERE p.name = e.name AND p.gender = 'female' AND (e.pizza = 'pepperoni'); SELECT * FROM Person MINUS SELECT * FROM Person WHERE name='Amy'; mysql SELECT name FROM Person MINUS SELECT name FROM Person WHERE Person.age 18; I've tried my first time sets and am not sure where is the problem, I've tried to google but when translating to my db it just doesn't work, You can download the create db sql here: http://s3.amazonaws.com/dbclass-resources/docs/pizza.sql ♥♥♥ When the sun rises I receive and when it sets I forgive! ♥♥♥ ˜♥ - http://moj.skavt.net/gleskovs/ - ♥ Always, Grega Leskovšek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [MYSQL] INTERSECT, MINUS
Hi, EXISTS function provides a simple way to find intersection between tables (INTERSECT operator from relational model). If we have table1 and table2, both having id and value columns, the intersection could be calculated like this: SELECT * FROM table1 WHERE EXISTS(SELECT * FROM table2 WHERE table1.id= table2.id AND table1.value=table2.value) For more details on intersect and minus, check this blog - http://www.bitbybit.dk/carsten/blog/?p=71 Thanks Suresh Kuna On Sun, Oct 16, 2011 at 5:12 PM, Grega Leskovšek legr...@gmail.com wrote: WHat is wring with the following three sentences? SELECT p.name, p.gender, e.pizza FROM Person p, Eats e WHERE p.name = e.name AND p.gender = 'female' AND (e.pizza = 'mushroom') INTERSECT SELECT p.name, p.gender, e.pizza FROM Person p, Eats e WHERE p.name = e.name AND p.gender = 'female' AND (e.pizza = 'pepperoni'); SELECT * FROM Person MINUS SELECT * FROM Person WHERE name='Amy'; mysql SELECT name FROM Person MINUS SELECT name FROM Person WHERE Person.age 18; I've tried my first time sets and am not sure where is the problem, I've tried to google but when translating to my db it just doesn't work, You can download the create db sql here: http://s3.amazonaws.com/dbclass-resources/docs/pizza.sql ♥♥♥ When the sun rises I receive and when it sets I forgive! ♥♥♥ ˜♥ - http://moj.skavt.net/gleskovs/ - ♥ Always, Grega Leskovšek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- Thanks Suresh Kuna MySQL DBA
Intersect question
I have the table 'test' which includes two columns: 'study' and 'symbol': study symbol a2008 A a2008 B a2008 C a2008 D b2005 A b2005 B b2005 E The task is to perform an intersection on 'name' column according to all distinct values in 'study' column. During the experiments the intersection was done 'manually' using the query: SELECT a.symbol FROM test as a, test as b WHERE a.symbol=b.symbol and a.study=a2008 and b.study=b2005; So the result of the query above is (A, B). The question is how to implement this query more automatically, without directly referencing to the study names, because I want to implement it into a php script. Thank you in advance for any suggestions. Best, Andrej -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Intersect question
On 12/01/2008 08:30 AM, Andrej Kastrin wrote: I have the table 'test' which includes two columns: 'study' and 'symbol': study symbol a2008 A a2008 B a2008 C a2008 D b2005 A b2005 B b2005 E The task is to perform an intersection on 'name' column according to all distinct values in 'study' column. During the experiments the intersection was done 'manually' using the query: SELECT a.symbol FROM test as a, test as b WHERE a.symbol=b.symbol and a.study=a2008 and b.study=b2005; So the result of the query above is (A, B). The question is how to implement this query more automatically, without directly referencing to the study names, because I want to implement it into a php script. Thank you in advance for any suggestions. Best, Andrej Why not: SELECT a.symbol FROM test as a, test as b WHERE a.symbol=b.symbol and a.study != b.study group by symbol; -Micah -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Intersect question
The task is to perform an intersection on 'name' column according to all distinct values in 'study' column. Did you try ... SELECT DISTINCT a.symbol FROM test as a JOIN test as b ON a.symbol=b.symbol WHERE a.study b.study; PB - Andrej Kastrin wrote: I have the table 'test' which includes two columns: 'study' and 'symbol': study symbol a2008 A a2008 B a2008 C a2008 D b2005 A b2005 B b2005 E The task is to perform an intersection on 'name' column according to all distinct values in 'study' column. During the experiments the intersection was done 'manually' using the query: SELECT a.symbol FROM test as a, test as b WHERE a.symbol=b.symbol and a.study=a2008 and b.study=b2005; So the result of the query above is (A, B). The question is how to implement this query more automatically, without directly referencing to the study names, because I want to implement it into a php script. Thank you in advance for any suggestions. Best, Andrej No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.176 / Virus Database: 270.9.11/1820 - Release Date: 11/29/2008 6:52 PM
RE: Intersect question
(0.00 sec)
mysql USE test
Database changed
mysql DROP TABLE IF EXISTS SCHOOL;
Query OK, 0 rows affected (0.00 sec)
mysql CREATE TABLE SCHOOL (study VARCHAR(10),symbol CHAR(1),
- KEY StudySymbolIndex (study,symbol)); Query OK, 0 rows affected (0.05
sec)
mysql INSERT INTO SCHOOL VALUES
- ('a2008','A'),
- ('a2008','B'),
- ('a2008','C'),
- ('a2008','D'),
- ('a2007','A'),
- ('a2007','B'),
- ('a2007','D'),
- ('b2006','A'),
- ('b2006','B'),
- ('b2006','F'),
- ('b2006','G'),
- ('b2006','H'),
- ('b2005','A'),
- ('b2005','B'),
- ('b2005','E');
Query OK, 15 rows affected (0.00 sec)
Records: 15 Duplicates: 0 Warnings: 0
mysql ALTER TABLE SCHOOL ORDER BY study,symbol;
Query OK, 15 rows affected (0.03 sec)
Records: 15 Duplicates: 0 Warnings: 0
mysql SELECT GROUP_CONCAT(DISTINCT study) AllStudies FROM SCHOOL;
+-+
| AllStudies |
+-+
| a2007,a2008,b2005,b2006 |
+-+
1 row in set (0.02 sec)
mysql SELECT X.* FROM
- (SELECT A.symbol,GROUP_CONCAT(B.study) Studies FROM
- (SELECT DISTINCT symbol FROM SCHOOL ORDER BY symbol) A,SCHOOL B
- WHERE A.symbol=B.symbol GROUP BY A.symbol) X,
- (SELECT GROUP_CONCAT(DISTINCT study) AllStudies FROM SCHOOL) Y
- WHERE X.Studies=Y.AllStudies;
++-+
| symbol | Studies |
++-+
| A | a2007,a2008,b2005,b2006 |
| B | a2007,a2008,b2005,b2006 |
++-+
2 rows in set (0.00 sec)
No matter how many distinct study values, you get the intersection of symbols.
GIVE IT A TRY AND HAVE FUN WITH IT !!!
-Original Message-
From: Micah Stevens [mailto:[EMAIL PROTECTED]
Sent: Monday, December 01, 2008 2:07 PM
To: Andrej Kastrin
Cc: mysql
Subject: Re: Intersect question
On 12/01/2008 08:30 AM, Andrej Kastrin wrote:
I have the table 'test' which includes two columns: 'study' and 'symbol':
study symbol
a2008 A
a2008 B
a2008 C
a2008 D
b2005 A
b2005 B
b2005 E
The task is to perform an intersection on 'name' column according to
all distinct values in 'study' column. During the experiments the
intersection was done 'manually' using the query:
SELECT a.symbol FROM test as a, test as b WHERE a.symbol=b.symbol and
a.study=a2008 and b.study=b2005;
So the result of the query above is (A, B).
The question is how to implement this query more automatically,
without directly referencing to the study names, because I want to
implement it into a php script.
Thank you in advance for any suggestions.
Best, Andrej
Why not:
SELECT a.symbol FROM test as a, test as b WHERE a.symbol=b.symbol and
a.study != b.study group by symbol;
-Micah
--
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]
INTERSECT
Hello, are there any plans for MySQL to support the INTERSECT command like most other DBMS (Oracle, PostgreSQL, SQLite) do? I've found a work-around to use an inner join, but I'm not sure how easy it is to adapt it to my situation. I'm intersecting rows from a single table, doing a lot of iterations. And the INTERSECT keyword is a much nicer and easier to read way of doing it. I'm using MySQL 5.0. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INTERSECT in mysql
Assuming you have access to MySQL 4.0+ select csymbol from tblavgPrice where avg 1 and avg 10 UNION select csymbol from tblAssets where assets 100 and assets 500 Unless you say UNION ALL, you won't get duplicate values. For more details: http://dev.mysql.com/doc/mysql/en/UNION.html Just out of curiosity and because this is not the first time the INTERSECT question has come up. What SQL dialect are you coming from? IF you are using a MySQL server that is pre-4.0+, you will have to recode that query using any of several variations that use a temporary table. Let me know if you need that work-around. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Chaitra Yale [EMAIL PROTECTED] wrote on 11/03/2004 02:12:42 AM: hi i have a problem mysql doesnt seem to support the intersect can somebody help how i can write the followoing code in mysql select csymbol from tblavgPrice where avg 1 and avg 10 intersect select csymbol from tblAssets where assets 100 and assets 500 thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INTERSECT in mysql
...how can union be the same as intersect..iam trying to get the names of comapnies that are in both queries.for example the first query gives me companies A, B AND C and the second query gives A , B..i want the intersect of these 2 queriesso i get companies A and B...if i did a union i will get A, B and C On Wed, 3 Nov 2004 09:13:08 -0500, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Assuming you have access to MySQL 4.0+ select csymbol from tblavgPrice where avg 1 and avg 10 UNION select csymbol from tblAssets where assets 100 and assets 500 Unless you say UNION ALL, you won't get duplicate values. For more details: http://dev.mysql.com/doc/mysql/en/UNION.html Just out of curiosity and because this is not the first time the INTERSECT question has come up. What SQL dialect are you coming from? IF you are using a MySQL server that is pre-4.0+, you will have to recode that query using any of several variations that use a temporary table. Let me know if you need that work-around. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Chaitra Yale [EMAIL PROTECTED] wrote on 11/03/2004 02:12:42 AM: hi i have a problem mysql doesnt seem to support the intersect can somebody help how i can write the followoing code in mysql select csymbol from tblavgPrice where avg 1 and avg 10 intersect select csymbol from tblAssets where assets 100 and assets 500 thanks -- 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: INTERSECT in mysql
Wouldn't SELECT p.csymbol FROM tblavgPrice p JOIN tblAssets a ON p.csymbol = a.csymbol WHERE p.avg 1 AND p.avg 10 AND a.assets 100 AND a.assets 500 do what you want? Michael Chaitra Yale wrote: ...how can union be the same as intersect..iam trying to get the names of comapnies that are in both queries.for example the first query gives me companies A, B AND C and the second query gives A , B..i want the intersect of these 2 queriesso i get companies A and B...if i did a union i will get A, B and C On Wed, 3 Nov 2004 09:13:08 -0500, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Assuming you have access to MySQL 4.0+ select csymbol from tblavgPrice where avg 1 and avg 10 UNION select csymbol from tblAssets where assets 100 and assets 500 Unless you say UNION ALL, you won't get duplicate values. For more details: http://dev.mysql.com/doc/mysql/en/UNION.html Just out of curiosity and because this is not the first time the INTERSECT question has come up. What SQL dialect are you coming from? IF you are using a MySQL server that is pre-4.0+, you will have to recode that query using any of several variations that use a temporary table. Let me know if you need that work-around. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Chaitra Yale [EMAIL PROTECTED] wrote on 11/03/2004 02:12:42 AM: hi i have a problem mysql doesnt seem to support the intersect can somebody help how i can write the followoing code in mysql select csymbol from tblavgPrice where avg 1 and avg 10 intersect select csymbol from tblAssets where assets 100 and assets 500 thanks -- 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: INTERSECT in mysql
On Wed, 3 Nov 2004, Chaitra Yale wrote: ...how can union be the same as intersect..iam trying to get the names of comapnies that are in both queries.for example the first query gives me companies A, B AND C and the second query gives A , B..i want the intersect of these 2 queriesso i get companies A and B...if i did a union i will get A, B and C Could use the union in a subquery together with a HAVING COUNT(*) on the outside. SELECT * FROM ( SELECT DISTINCT col1 FROM t1 WHERE... UNION ALL SELECT DISTINCT col1 FROM t1 WHERE... ) AS tbl GROUP BY tbl.col1 HAVING COUNT(*) = 2 Or something... not sure how well that would performe, however. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INTERSECT in mysql
hi i have a problem mysql doesnt seem to support the intersect can somebody help how i can write the followoing code in mysql select csymbol from tblavgPrice where avg 1 and avg 10 intersect select csymbol from tblAssets where assets 100 and assets 500 thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with sql without using INTERSECT
I have the following table: +-+-+ | ID_AGE | ID_ENTRY | +-+-+ | 1 | 1 | | 1 | 4 | | 1 | 5 | | 2 | 1 | | 2 | 2 | | 2 | 3 | | 2 | 4 | | 2 | 6 | | 2 | 7 | | 2 | 8 | | 2 |10 | | 2 |11 | | 2 |13 | | 2 |14 | | 2 |15 | | 2 |19 | | 2 |20 | | 2 |21 | | 2 |22 | | 2 |24 | | 3 |14 | | 3 |16 | | 3 |17 | | 3 |18 | | 3 |19 | | 3 |22 | +-+--+ And since INTERSECT is not currently supported how do I select the ID_ENTRY that has both 1 and 2 for ID_AGE SELECT ID_ENTRY WHERE ID_AGE = 1 INTERSECT SELECT ID_ENTRY EHRE ID_AGE=2; The results should be 1 and 4. Thanks in advanced. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with sql without using INTERSECT
Jeff Meyer wrote: I have the following table: +-+-+ | ID_AGE | ID_ENTRY | +-+-+ | 1 | 1 | | 1 | 4 | | 1 | 5 | | 2 | 1 | | 2 | 2 | | 2 | 3 | | 2 | 4 | | 2 | 6 | | 2 | 7 | | 2 | 8 | | 2 |10 | | 2 |11 | | 2 |13 | | 2 |14 | | 2 |15 | | 2 |19 | | 2 |20 | | 2 |21 | | 2 |22 | | 2 |24 | | 3 |14 | | 3 |16 | | 3 |17 | | 3 |18 | | 3 |19 | | 3 |22 | +-+--+ And since INTERSECT is not currently supported how do I select the ID_ENTRY that has both 1 and 2 for ID_AGE SELECT ID_ENTRY WHERE ID_AGE = 1 INTERSECT SELECT ID_ENTRY EHRE ID_AGE=2; The results should be 1 and 4. Thanks in advanced. select a.id_entry from idtable a, idtable b where a.identry=b.identry and a.age=1 and b.age=2; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with sql without using INTERSECT
INTERSECT sound very much like UNION DISTINCT (http://dev.mysql.com/doc/mysql/en/UNION.html) because this query should give you what you asked for and is very similar to yours: (SELECT ID_ENTRY FROM table WHERE ID_AGE = 1) UNION DISTINCT (SELECT ID_ENTRY FROM table WHERE ID_AGE=2) However, I can also think of other ways of answering this same question SELECT ID_ENTRY, count(1) FROM table WHERE id_age in (1,2) GROUP BY id_entry HAVING count(1)=2 SELECT t1.ID_ENTRY FROM table t1 INNER JOIN table t2 ON t1.id_entry = t2.id_entry AND t1.id_age=1 AND t2.id_age=2 Use whichever seems best for your circumstances Shawn Green Database Administrator Unimin Corporation - Spruce Pine news [EMAIL PROTECTED] wrote on 08/12/2004 02:48:21 PM: I have the following table: +-+-+ | ID_AGE | ID_ENTRY | +-+-+ | 1 | 1 | | 1 | 4 | | 1 | 5 | | 2 | 1 | | 2 | 2 | | 2 | 3 | | 2 | 4 | | 2 | 6 | | 2 | 7 | | 2 | 8 | | 2 |10 | | 2 |11 | | 2 |13 | | 2 |14 | | 2 |15 | | 2 |19 | | 2 |20 | | 2 |21 | | 2 |22 | | 2 |24 | | 3 |14 | | 3 |16 | | 3 |17 | | 3 |18 | | 3 |19 | | 3 |22 | +-+--+ And since INTERSECT is not currently supported how do I select the ID_ENTRY that has both 1 and 2 for ID_AGE SELECT ID_ENTRY WHERE ID_AGE = 1 INTERSECT SELECT ID_ENTRY EHRE ID_AGE=2; The results should be 1 and 4. Thanks in advanced. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to intersect resulting sets form sql query
The INTERSECT keyword is not available in MySQL. You will have to compare the columns in your join syntax (or get creative?). -Original Message- From: L a n a To: [EMAIL PROTECTED] Sent: 7/18/04 8:20 PM Subject: 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to intersect resulting sets form sql query
INTERSECT is not a MySQL keyword. If I read your logic correctly, you seem to want: SELECT * FROM table WHERE (field_a like '%keyword1%' OR field_b like '%keyword1%') AND (field_a like '%keyword2%' OR field_b like '%keyword2%') This statements tests that both keyword1 and keyword2 both appear in the same record in either field_a or field_b. If that is not what you intended, just write out a description of condition you need to find and we will try to help you make a query to do that. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine L a n a [EMAIL PROTECTED] wrote on 07/18/2004 09:20:20 PM: 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]
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]
alternative to intersect in mySQL
I know that intersect does not work yet with mySQL. I am selecting data from two tables. Results of one select would be 1, 2, 3, 4 . Results of other select 1, 2. Want to print 1, 2. Are there any nice workarounds using PHP or SQL? thanks, -tom - 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: alternative to intersect in mySQL
Hi, I know that intersect does not work yet with mySQL. I am selecting data from two tables. Results of one select would be 1, 2, 3, 4 . Results of other select 1, 2. Want to print 1, 2. Are there any nice workarounds using PHP or SQL? You could use a temporary table to store the result of the first query and join the second query with it afterwards. Nice enough? :) Regards, Sasa »mysql, select, database« - 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: alternative to intersect in mySQL
If it is in a multi-user environment, this is undesirable. Any other thoughts? I was thinking of doing arrays in PHP and then using array_intersect PHP function, but would prefer a cleaner way. -Original Message- From: Aleksandar Bradaric [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 23, 2002 1:08 AM To: Thomas Moore Cc: MySQL List Subject: Re: alternative to intersect in mySQL Hi, I know that intersect does not work yet with mySQL. I am selecting data from two tables. Results of one select would be 1, 2, 3, 4 . Results of other select 1, 2. Want to print 1, 2. Are there any nice workarounds using PHP or SQL? You could use a temporary table to store the result of the first query and join the second query with it afterwards. Nice enough? :) Regards, Sasa »mysql, select, database« - 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: alternative to intersect in mySQL
* Thomas Moore I know that intersect does not work yet with mySQL. I am selecting data from two tables. Results of one select would be 1, 2, 3, 4 . Results of other select 1, 2. Want to print 1, 2. Are there any nice workarounds using PHP or SQL? [temporary table] If it is in a multi-user environment, this is undesirable. Any other thoughts? Maybe you could give us some more details about the problem? Result of one select...? Is this a complex joined select which you can not expand to include the second select? Is it different hosts/databases? Why can you not use a join? select a.id from a,b where a.id = b.id -- Roger - 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: alternative to intersect in mySQL
The issue has to do with relating a PRODUCT table and ATTRIBUTE table so that one product is related to multiple attributes. In the shopping cart, I want to show multiple select menus. After a user selects all of them, only the parts related to those attributes will be displayed. Example: PRODUCT (product_id, etc.) Part #100 - has attributes 1, 2 Part #200 - has attribute 1 ATTRIBUTE (product_id, attribute_id) Part_ID, Attribute - GREEN (1) Part_ID, Attribute - Aluminum (2) Want to select all products from PRODUCT that are both Green and Aluminum. In Oracle, I can do this using the INTERSECT command. select product_id from PRODUCT P, ATTRIBUTE A where P.product_id = A.product_id and Attribute = 1 INTERSECT select product_id from PRODUCT P, ATTRIBUTE A where P.product_id = A.product_id and Attribute = 2 A temporary table may work, but I do not like this as a solution as I would have to maintain an extra table with non-relevant data (need to constantly insert and delete data). I tried playing with outer joins, but I don't know how to get it to do this. thanks, -tom -Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 23, 2002 10:43 AM To: MySQL List Cc: [EMAIL PROTECTED] Subject: RE: alternative to intersect in mySQL * Thomas Moore I know that intersect does not work yet with mySQL. I am selecting data from two tables. Results of one select would be 1, 2, 3, 4 . Results of other select 1, 2. Want to print 1, 2. Are there any nice workarounds using PHP or SQL? [temporary table] If it is in a multi-user environment, this is undesirable. Any other thoughts? Maybe you could give us some more details about the problem? Result of one select...? Is this a complex joined select which you can not expand to include the second select? Is it different hosts/databases? Why can you not use a join? select a.id from a,b where a.id = b.id -- Roger - 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 - 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: alternative to intersect in mySQL
* Thomas Moore The issue has to do with relating a PRODUCT table and ATTRIBUTE table so that one product is related to multiple attributes. In the shopping cart, I want to show multiple select menus. After a user selects all of them, only the parts related to those attributes will be displayed. Example: PRODUCT (product_id, etc.) Part #100 - has attributes 1, 2 Part #200 - has attribute 1 ATTRIBUTE (product_id, attribute_id) Part_ID, Attribute - GREEN (1) Part_ID, Attribute - Aluminum (2) Want to select all products from PRODUCT that are both Green and Aluminum. In Oracle, I can do this using the INTERSECT command. select product_id from PRODUCT P, ATTRIBUTE A where P.product_id = A.product_id and Attribute = 1 INTERSECT select product_id from PRODUCT P, ATTRIBUTE A where P.product_id = A.product_id and Attribute = 2 In mysql you can use two joins to the ATTRIBUTE table: SELECT P.product_id FROM PRODUCT P, ATTRIBUTE A1, ATTRIBUTE A2 WHERE P.product_id = A1.product_id AND A1.Attribute = 1 AND P.product_id = A2.product_id AND A2.Attribute = 2 A temporary table may work, but I do not like this as a solution as I would have to maintain an extra table with non-relevant data (need to constantly insert and delete data). No, that's the point with a TEMPORARY table... it's a special kind of table, it is automatically removed when the connection is closed, and separate connections have separate temporary tables... URL: http://www.mysql.com/doc/C/R/CREATE_TABLE.html Usefull in many cases, but I do not think you need it in this case. I tried playing with outer joins, but I don't know how to get it to do this. The commas in the above select statement is a short form of 'INNER JOIN'. -- Roger - 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: UNION/INTERSECT
For the questions... cut select tekst || tekst2 total from table; should return a virtual column called total with as value testtest it returns a virtual column called total alright on it's value is 0 I also tried this with + instead of || in oracle this should give an error since it aren't numeric fields in mysql it does the same as || (or atleast so it appears) || is a logical OR, unless you run MySQL in ANSI mode. Nothing wrong with the output. Use the CONCAT() function instead. cut these both don't work. I searched the manual but don't see UNION at all, is it supported? I'm figuring it isn't You didn't search very hard, did you? Try using the find command in your browser. The very first time the word UNION appears, the manual says: 1.5.4 Other Features Available From MySQL 4.0.0 ... Many users will also be happy to learn that MySQL now supports the UNION statement, a long awaited standard SQL feature. SQL SELECT * FROM FOOTBALL 2 INTERSECT 3 SELECT * FROM SOFTBALL; doesn't work either, i'm guessin intersect isn't implemented either? Same procedure gives you (very first hit, too): 1.9.3 Things That Have to be Done Sometime ... MINUS, INTERSECT and FULL OUTER JOIN. (Currently UNION (in 4.0) and LEFT OUTER JOIN are supported) / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - 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
UNION/INTERSECT
Hey there, first of all please CC my email address as I'm no longer a member of this list. Recieving too much stuff already (1000+ mails a day) Anyways I found a general SQL manual which uses oracle to show it's demos. So I tried some of the examples in there some work but don't do the expected others don't work at all. For the questions... This one I don't find really important. If you had a table a with a field tekst (text) containing test and a field tekst2 containing test according to the mysql course this statement select tekst || tekst2 total from table; should return a virtual column called total with as value testtest it returns a virtual column called total alright on it's value is 0 I also tried this with + instead of || in oracle this should give an error since it aren't numeric fields in mysql it does the same as || (or atleast so it appears) copy from course INPUT: SQL SELECT * FROM FOOTBALL; OUTPUT: NAME ABLE BRAVO CHARLIE DECON EXITOR FUBAR GOOBER 7 rows selected. INPUT: SQL SELECT * FROM SOFTBALL; OUTPUT: NAME ABLE BAKER CHARLIE DEAN EXITOR FALCONER GOOBER 7 rows selected. How many different people play on one team or another? INPUT/OUTPUT: SQL SELECT NAME FROM SOFTBALL 2 UNION 3 SELECT NAME FROM FOOTBALL; NAME ABLE BAKER BRAVO CHARLIE DEAN DECON EXITOR FALCONER FUBAR GOOBER 10 rows selected. UNION returns 10 distinct names from the two lists. How many names are on both lists (including duplicates)? INPUT/OUTPUT: SQL SELECT NAME FROM SOFTBALL 2 UNION ALL 3 SELECT NAME FROM FOOTBALL; NAME ABLE BAKER CHARLIE DEAN EXITOR FALCONER GOOBER ABLE BRAVO CHARLIE DECON EXITOR FUBAR GOOBER 14 rows selected. ANALYSIS: The combined list--courtesy of the UNION ALL statement--has 14 names. UNION ALL works just like UNION except it does not eliminate duplicates. Now show me a list of players who are on both teams. You can't do that with UNION--you need to learn INTERSECT. /copy these both don't work. I searched the manual but don't see UNION at all, is it supported? I'm figuring it isn't SQL SELECT * FROM FOOTBALL 2 INTERSECT 3 SELECT * FROM SOFTBALL; doesn't work either, i'm guessin intersect isn't implemented either? kind regards and have a good weekend - 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
workaround for INTERSECT?
Dear all the mysql DBA: I'm currently using mysql Ver 11.9 Distrib 3.23.29a-gamma, for pc-linux-gnu (i686) May I know is there any workaround for using INTERSECT in this version. Thanks SQL and Mysql newbie. Wee - 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... INTERSECT...
A newbie question... Does Mysql support SELECT.. INTERSECT? if not, are there any other options I can use to implement the same function? Thanks. Jowena __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.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
