Re: Subqueries in the FROM Clause
On 18 April 2011 20:19, Joerg Bruehe wrote: > Hallo everybody! > > > Ants Pants wrote: > > Hello All, > > > > Tables: > > # relevant fields > > invitations: donation_pledge, paid (boolean), currency_id > > currencies: code > > > > > > I am trying to subtract the paid amounts from the amounts pledged using a > > subquery in the FROM clause but am having problems and am going blind. > Plus, > > My SQL is weak at present. > > > > I was hoping a SQL ninja could have a look for me and tell me where I'm > > going wrong. > > > > I hope the following formats nicely for you to see what I've done > > > > This shows the amounts pledged grouped by (currency) code > > > >SELECT SUM(i.donation_pledge), c.code > > FROM invitations i LEFT JOIN currencies c ON i.currency_id = c.id > > WHERE i.meeting_id = 934311021 > > GROUP BY c.code; > > ++--+ > > | sum(i.donation_pledge) | code | > > ++--+ > > | 11170 | BRL | > > | 2997 | EUR | > > ++--+ > > > > This shows the amounts paid grouped by (currency) code > > > >SELECT SUM(i.donation_pledge), c.code > > FROM invitations i LEFT JOIN currencies c ON i.currency_id = c.id > > WHERE i.meeting_id = 934311021 AND paid = true > > GROUP BY c.code; > > > > ++--+ > > | sum(i.donation_pledge) | code | > > ++--+ > > | 70 | BRL | > > |999 | EUR | > > ++--+ > > > > And this is supposed to show the amounts outstanding but it has doubled > the > > values and subtracted 70 from each each value (the BRL currency code > amount) > > > > SELECT sum(donation_pledge) - paid_donation_pledge > > FROM (SELECT i2.meeting_id, sum(donation_pledge) AS > paid_donation_pledge > > > > FROM invitations i2 LEFT JOIN currencies c2 ON > > i2.currency_id = c2.id > >WHERE i2.meeting_id = 934311021 AND i2.paid = true > > GROUP BY c2.code ) AS i2 LEFT JOIN invitations i ON > i2.meeting_id > > = i.meeting_id > > LEFT JOIN currencies > c > > ON i.currency_id = c.id > > GROUP BY c.code; > > > > +-+ > > | sum(donation_pledge) - paid_donation_pledge | > > +-+ > > | 22270 | > > |5924 | > > +-+ > > AFAICS, you are missing the equality condition on the currency between > the subquery and the other tables. This would explain why the 70 is > subtracted not only from the BRL value but also from the EUR. > Off-hand, I have no explanation for the doubling of the sums, but I have > never used subqueries in the FROM clause. > > Others might know more about this, but telling the version you are using > might be helpful for them. > > > That said, IMO you are doing it much more complicated than necessary: > As your "invitations" table that lists the pledges also has a field > "paid", it seems you could calculate the amounts outstanding in the same > way as those paid, just changing the condition on "paid": > > SELECT SUM(i.donation_pledge), c.code > FROM invitations i LEFT JOIN currencies c ON i.currency_id = c.id > WHERE i.meeting_id = 934311021 AND paid != true > GROUP BY c.code; > > Of course, details will depend on what you enter in "paid", and you must > take care of NULL values. > > > HTH, > Joerg > > -- > Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com > ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 Berlin > Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven > Amtsgericht Muenchen: HRA 95603 > Joerg, How embarrassing!! That's what you get for not taking breaks and having blinker vision. I'm such a fool!! Right now, I am the colour of my shirt. A very bright red!! Have a nice evening
Re: Subqueries in the FROM Clause
Hallo everybody! Ants Pants wrote: > Hello All, > > Tables: > # relevant fields > invitations: donation_pledge, paid (boolean), currency_id > currencies: code > > > I am trying to subtract the paid amounts from the amounts pledged using a > subquery in the FROM clause but am having problems and am going blind. Plus, > My SQL is weak at present. > > I was hoping a SQL ninja could have a look for me and tell me where I'm > going wrong. > > I hope the following formats nicely for you to see what I've done > > This shows the amounts pledged grouped by (currency) code > >SELECT SUM(i.donation_pledge), c.code > FROM invitations i LEFT JOIN currencies c ON i.currency_id = c.id > WHERE i.meeting_id = 934311021 > GROUP BY c.code; > ++--+ > | sum(i.donation_pledge) | code | > ++--+ > | 11170 | BRL | > | 2997 | EUR | > ++--+ > > This shows the amounts paid grouped by (currency) code > >SELECT SUM(i.donation_pledge), c.code > FROM invitations i LEFT JOIN currencies c ON i.currency_id = c.id > WHERE i.meeting_id = 934311021 AND paid = true > GROUP BY c.code; > > ++--+ > | sum(i.donation_pledge) | code | > ++--+ > | 70 | BRL | > |999 | EUR | > ++--+ > > And this is supposed to show the amounts outstanding but it has doubled the > values and subtracted 70 from each each value (the BRL currency code amount) > > SELECT sum(donation_pledge) - paid_donation_pledge > FROM (SELECT i2.meeting_id, sum(donation_pledge) AS paid_donation_pledge > > FROM invitations i2 LEFT JOIN currencies c2 ON > i2.currency_id = c2.id >WHERE i2.meeting_id = 934311021 AND i2.paid = true > GROUP BY c2.code ) AS i2 LEFT JOIN invitations i ON i2.meeting_id > = i.meeting_id > LEFT JOIN currencies c > ON i.currency_id = c.id > GROUP BY c.code; > > +-+ > | sum(donation_pledge) - paid_donation_pledge | > +-+ > | 22270 | > |5924 | > +-+ AFAICS, you are missing the equality condition on the currency between the subquery and the other tables. This would explain why the 70 is subtracted not only from the BRL value but also from the EUR. Off-hand, I have no explanation for the doubling of the sums, but I have never used subqueries in the FROM clause. Others might know more about this, but telling the version you are using might be helpful for them. That said, IMO you are doing it much more complicated than necessary: As your "invitations" table that lists the pledges also has a field "paid", it seems you could calculate the amounts outstanding in the same way as those paid, just changing the condition on "paid": SELECT SUM(i.donation_pledge), c.code FROM invitations i LEFT JOIN currencies c ON i.currency_id = c.id WHERE i.meeting_id = 934311021 AND paid != true GROUP BY c.code; Of course, details will depend on what you enter in "paid", and you must take care of NULL values. HTH, Joerg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Subqueries in MySQL < 4.1
On Wed, 2006-08-23 at 22:23 +0200, spacemarc wrote: > Hi, > I have a query like this: > > SELECT table1.*,( > SELECT COUNT( field2 ) > FROM table2 > WHERE id=10 > ) AS total > FROM table1 > GROUP BY id > LIMIT 1 > > but the subqueries do not work with mysql < 4.1. How can I convert it > (or make to work) in MySQL 3.x, 4.0 possibly in one only query? Your query doesn't show any relationship between the two tables (via a join condition or correlation) so you would have to do two queries (which is exactly what your original query does anyway: SELECT COUNT(field2):[EMAIL PROTECTED] FROM table2 WHERE id = 10; SELECT table1.*, @counter as total FROM table1 LIMIT 1; Note that I took out the GROUP BY clause, which is pointless given the query's structure of returning the first id column. Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subqueries in MySQL < 4.1
See http://dev.mysql.com/doc/refman/5.0/en/rewriting-subqueries.html for some tips Dan On 8/23/06, spacemarc <[EMAIL PROTECTED]> wrote: Hi, I have a query like this: SELECT table1.*,( SELECT COUNT( field2 ) FROM table2 WHERE id=10 ) AS total FROM table1 GROUP BY id LIMIT 1 but the subqueries do not work with mysql < 4.1. How can I convert it (or make to work) in MySQL 3.x, 4.0 possibly in one only query? thanks -- http://www.spacemarc.it -- 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: subqueries *not* using indexes for IN clause
Greg Whalin wrote: We have noticed this as well and it is really pretty shoddy. It seems that when using IN( SELECT ), they treat it as ANY() which does a full table scan. Only way we have found to get fast performance out of subqueries is to use the derived table format and join with the derived table. But if I have to do that, might as well just use the join without the funky syntax. Still, it does simplify some sql which is difficult to do with a regular join (i.e. joining w/ max() col, etc.). In any rate, I agree. What is the point of claiming to offer sub-selects when thay are practically unusable in IN() statements which is how most people use subselects IMO. Yup... couldn't agree more! MySQL subqueries in 4.1 are at best useless and at worst Evil.. plain Evil ! ;) But nice try guys! This seems like it REALLY deserves a bug fix! Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: subqueries *not* using indexes for IN clause
We have noticed this as well and it is really pretty shoddy. It seems that when using IN( SELECT ), they treat it as ANY() which does a full table scan. Only way we have found to get fast performance out of subqueries is to use the derived table format and join with the derived table. But if I have to do that, might as well just use the join without the funky syntax. Still, it does simplify some sql which is difficult to do with a regular join (i.e. joining w/ max() col, etc.). In any rate, I agree. What is the point of claiming to offer sub-selects when thay are practically unusable in IN() statements which is how most people use subselects IMO. greg Kevin A. Burton wrote: http://www.peerfear.org/rss/permalink/2005/04/02/BrokenMySQLSubqueries Whats up with this? As far as I can tell MySQL subqueries in 4.1.x releases are totally broken with IN clauses The major reason is that they don't use *ANY* indexes and resort to full table scans. Lets take two queries: mysql> EXPLAIN SELECT * FROM FEED, ARTICLE WHERE ARTICLE.ID = 1628011 AND FEED.ID = ARTICLE.ID *** 1. row *** id: 1 select_type: SIMPLE table: FEED type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: *** 2. row *** id: 1 select_type: SIMPLE table: ARTICLE type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: 2 rows in set (0.00 sec) Which is *great*. The join is using both of the PRIMARY indexes on the columns and only references one row. Can't get any better than that! Now lets rewrite the SELECT to use a subquery: mysql> EXPLAIN SELECT * FROM FEED WHERE ID IN (SELECT ID FROM ARTICLE WHERE ID = 1628011) *** 1. row *** id: 1 select_type: PRIMARY table: FEED type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2316698 Extra: Using where *** 2. row *** id: 2 select_type: DEPENDENT SUBQUERY table: ARTICLE type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: Using index 2 rows in set (0.00 sec) And here's where the fun begins. The FEED table won't use *ANY* index! It really can't get ANY worse than that. So either this is a bug in both 4.1.10 and 4.1.7 or the optimizer is just plain broken. Note that using FORCE INDEX doesn't work at all. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SubQueries
You must be having a problem with your email client as this is about the ninth time I have seen this same request today. Please check your client for problems. I know because I have responded once already. Shawn Green Database Administrator Unimin Corporation - Spruce Pine <[EMAIL PROTECTED]> wrote on 01/20/2005 10:20:12 AM: > > > Hi, > > Could you please help me in writing an equvivalent query in mysql 4.0.21 > for the following oracle subquery?. > > update macvm set embedded='Y' where vm_server in > (select a.vm_server from macvm a, component b, element c where > a.vm_server = b.name (+) and b.id = c.id (+) and c.sxvariant = 'I3'); > > > Thanks, > > Narasimha > > > > > > > > > > Confidentiality Notice > > > The information contained in this electronic message and any > attachments to this message are intended > for the exclusive use of the addressee(s) and may contain > confidential or privileged information. If > you are not the intended recipient, please notify the sender at > Wipro or [EMAIL PROTECTED] immediately > and destroy all copies of this message and any attachments. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
Re: SubQueries
<[EMAIL PROTECTED]> wrote on 01/20/2005 08:59:15 AM: > Hi, > Thank you. Could you please help me in writing an equvivalent > query in mysql for the following oracle subquery?. > > update macvm set embedded='Y' where vm_server in > (select a.vm_server from macvm a, component b, element c where > a.vm_server = b.name (+) >and b.id = c.id (+) and c.sxvariant = 'I3'); > > Thanks, > Narasimha > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, December 08, 2004 9:28 PM > To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) > Cc: mysql@lists.mysql.com > Subject: Re: SubQueries > > Since 4.0.22 does NOT have subqueries, you will have to use a JOIN > http://dev.mysql.com/doc/mysql/en/JOIN.html > http://dev.mysql.com/doc/mysql/en/Rewriting_subqueries.html > > ... as in this example > > SELECT PAGE_SERVICE.TIMEOUT > , PAGE_SERVICE.PAGE_SERVICE_COMMENT > , PAGE_SERVICE.NUMERICMSGMAXSIZE > , PAGE_SERVICE.ALPHAMSGMAXSIZE > , PAGE_SERVICE.PASSWORD > , PAGE_SERVICE.PHONE_NO > , PAGE_SERVICE.NAME > , PAGE_SERVICE.PAGE_SERVICE_ID > FROM PAGE_SERVICE > LEFT JOIN PAGER > ON PAGE_SERVICE.PAGE_SERVICE_ID = PAGER.PAGE_SERVICE_ID > WHERE PAGER.PAGE_SERVICE_ID IS NULL; > > ... best wishes! > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > Please Google for tutorials on how to JOIN tables (using the explicit JOIN clauses, not the implicit inner joins created by comma-separated lists of tables) and RTM (see links above). Most subqueries will easily translate to explicit JOINs. Learning this form, now, will save you lots of heartache later. Original ORACLE query (re-formatted): update macvm set embedded='Y' where vm_server in( select a.vm_server from macvm a, component b, element c where a.vm_server = b.name (+) and b.id = c.id (+) and c.sxvariant = 'I3' ); To be perfectly HONEST, I have had limited exposure to the ORACLE join syntax. If I remember correctly, the (+) is on the side of the equation with the optional results (but I could be wrong). So I think that we translate this clause from the subquery: from macvm a, component b, element c where a.vm_server = b.name (+) and b.id = c.id (+) and c.sxvariant = 'I3' to read: FROM macvm a LEFT JOIN component b ON a.vm_server = b.name LEFT JOIN element c ON b.id = c.id AND c.sxvariant = 'I3' HOWEVER!! Because we _need_ a field from the table element to have a particular value. We should (for performance reasons) use INNER JOINs to ensure that the column sxvariant always contains the value 'I3'. That would change that portion of the subquery to read: FROM macvm a INNER JOIN component b ON a.vm_server = b.name INNER JOIN element c ON b.id = c.id and c.sxvariant = 'I3' Since macvm IS THE TABLE THAT CONTAINS THE FIELD YOU WANT TO UPDATE, and the JOINs accurately define the set of records you want to change (it usually takes a combination of JOINs and WHERE conditions to define the set of records to update but this time it didn't) we can use this clause "as is" as the "target" of the update statement. PROPOSED TRANSLATION: UPDATE macvm a INNER JOIN component b ON a.vm_server = b.name INNER JOIN element c ON b.id = c.id AND c.sxvariant = 'I3' SET embedded='Y'; ALTERNATIVE TRANSLATION: UPDATE macvm a INNER JOIN component b ON a.vm_server = b.name INNER JOIN element c ON b.id = c.id SET embedded='Y'; WHERE c.sxvariant = 'I3'; ALTERNATIVE TRANSLATION 2: UPDATE macvm a LEFT JOIN component b ON a.vm_server = b.name LEFT JOIN element c ON b.id = c.id SET embedded='Y'; WHERE c.sxvariant = 'I3'; If you ever want to double-check that you are going to update the correct set of rows, check your "update target" by rearranging your UPDATE statement into a SELECT statement. I usually list the target columns (the columns that get SET to some value) first, then I list the columns that participate in the JOINS and maybe even those that participate in the WHERE clause, too. If everything seems correct, then you know you have a good UPDATE target. Here is how I would manually verify the first translation: SELECT a.embedded , a.vm_server , b.id , c.sxvariant FROM macvm a INNER JOIN component b ON a.vm_server = b.name INNER JOIN element c ON b.id = c.id AND c.sxvariant = 'I3'; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: SubQueries
Since 4.0.22 does NOT have subqueries, you will have to use a JOIN http://dev.mysql.com/doc/mysql/en/JOIN.html http://dev.mysql.com/doc/mysql/en/Rewriting_subqueries.html ... as in this example SELECT PAGE_SERVICE.TIMEOUT , PAGE_SERVICE.PAGE_SERVICE_COMMENT , PAGE_SERVICE.NUMERICMSGMAXSIZE , PAGE_SERVICE.ALPHAMSGMAXSIZE , PAGE_SERVICE.PASSWORD , PAGE_SERVICE.PHONE_NO , PAGE_SERVICE.NAME , PAGE_SERVICE.PAGE_SERVICE_ID FROM PAGE_SERVICE LEFT JOIN PAGER ON PAGE_SERVICE.PAGE_SERVICE_ID = PAGER.PAGE_SERVICE_ID WHERE PAGER.PAGE_SERVICE_ID IS NULL; ... best wishes! Shawn Green Database Administrator Unimin Corporation - Spruce Pine <[EMAIL PROTECTED]> wrote on 12/08/2004 09:36:09 AM: > > Hi, > > I have a sub query in oracle, I want to convert it into Mysql > 4.0.21 compatible > > > > >SELECT PAGE_SERVICE.TIMEOUT, PAGE_SERVICE.PAGE_SERVICE_COMMENT, > > > PAGE_SERVICE.NUMERICMSGMAXSIZE, PAGE_SERVICE.ALPHAMSGMAXSIZE, > PAGE_SERVICE.PASSWORD, PAGE_SERVICE.PHONE_NO, PAGE_SERVICE.NAME, > PAGE_SERVICE.PAGE_SERVICE_ID > >FROM PAGE_SERVICE > >WHERE NOT EXISTS (SELECT 1 FROM PAGER WHERE > PAGE_SERVICE.PAGE_SERVICE_ID = PAGER.PAGE_SERVICE_ID) > >WITH CHECK OPTION; > > > > >The above query is not displaying all the records whose > PAGER.PAGE_SERVICE_ID is equal to PAGE_SERVICE.PAGE_SERVICE_ID. > > > > >As subqueries are not supporting in mysql, could any one of you > please give a solution . > > > > > Waiting for the reply. Thanks in Advance > > > > > Thanks, > > Narasimha > > > > > > > > > Confidentiality Notice > > > The information contained in this electronic message and any > attachments to this message are intended > for the exclusive use of the addressee(s) and may contain > confidential or privileged information. If > you are not the intended recipient, please notify the sender at > Wipro or [EMAIL PROTECTED] immediately > and destroy all copies of this message and any attachments.
Re: Subqueries and JOIN
I believe this will work as you want: select h1.* FROM hist h1 LEFT JOIN hist h2 on h1.tel = h2.tel and h2.date_h < '20041027' where h1.date_h = '20041027' and h2.tel is null; This query should return all of the rows from the hist table where the tel value appears for the date you supplied but no earlier. I would also try these other variations to see if you get better or worse performance. select h1.* FROM hist h1 LEFT JOIN hist h2 on h2.tel = h1.tel and h2.date_h < h1.date_h where h1.date_h = '20041027' and h2.tel is null; select h1.* FROM hist h1 LEFT JOIN hist h2 on h2.tel = h1.tel and h2.date_h < h1.date_h and h2.date_h < '20041027' where h1.date_h = '20041027' and h2.tel is null; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Hector Villafuerte <[EMAIL PROTECTED]> wrote on 10/27/2004 10:21:08 PM: > Can I execute this query in a single JOIN statement? > > select * from hist > where date_h = '20041027' > and tel not in > (select distinct tel from hist where date_h < '20041027') > > I know I could do it using a temporary table, but I wonder if > there's a way to do it directly. > Thanks! > Hector > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >
Re: Subqueries in version 4.1
Wouter Coppieters <[EMAIL PROTECTED]> wrote: > > We installed version 4.1.0-alpha-max-nt and try to run a query with a > subquery and get the message > > [DB_BAS_LOCAL] ERROR 1235: This version of MySQL doesn't yet support 'LIMIT > & IN/ALL/ANY/SOME subquery' > > > Are subqueries supporeted in 4.1 as is mentioned in the doc or not? > Yup, but as mentioned in the error LIMIT currently is not supported in the IN/ALL/ANY/SOME subquery. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SubQueries and IN
Sebastian Tobias Mendel genannt Mendelsohn <[EMAIL PROTECTED]> wrote: >>>SELECT product_id, name, description, sales.sale_id >>>FROM products LEFT JOIN sales ON products.product_id = sales.product_id >>>WHERE sales.customer_id = 10 AND sales.sale_id IS NULL >> >> >> This query should return no rows, because if you retrieve rows where sales.sale_id >> is NULL, customer_id for these rows also will be NULL, not 10. > > > you are wrong, or do you know the table-structure? No, I don't know table structure. > sales.sale_id can be NULL while customer_id can be 10 ! Probably you misundernstood me. Look at the following example, there are 2 test table: t1 and t2. mysql> select * from t1; +--+ | id | +--+ |1 | |2 | |3 | |4 | |5 | +--+ 5 rows in set (0.00 sec) mysql> select * from t2; +--+--+ | id | name | +--+--+ |1 | vita | |3 | egor | |5 | tony | +--+--+ 3 rows in set (0.00 sec) Now I want to do this simple SELECT statement that is like author want to do: SELECT * FROM t1 WHERE t1.id NOT IN (SELECT t2.id FROM t2 WHERE name='egor'). For versions before 4.1 I can rewrite NOT IN() using LEFT JOIN. Here is the output of LEFT JOIN: mysql> select * from t1 left join t2 on t1.id=t2.id; +--+--+--+ | id | id | name | +--+--+--+ |1 |1 | vita | |2 | NULL | NULL | |3 |3 | egor | |4 | NULL | NULL | |5 |5 | tony | +--+--+--+ 5 rows in set (0.01 sec) As you can see, if I add condition t2.id IS NULL to the WHERE clause, I can't retrieve rows where name='egor'. That is why author didn't get any rows. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SubQueries and IN
SELECT product_id, name, description, sales.sale_id FROM products LEFT JOIN sales ON products.product_id = sales.product_id WHERE sales.customer_id = 10 AND sales.sale_id IS NULL This query should return no rows, because if you retrieve rows where sales.sale_id is NULL, customer_id for these rows also will be NULL, not 10. you are wrong, or do you know the table-structure? sales.sale_id can be NULL while customer_id can be 10 ! -- Sebastian Mendel www.sebastianmendel.de www.tekkno4u.de www.nofetish.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SubQueries and IN
"Andy Hall" <[EMAIL PROTECTED]> wrote: > > I have just started using MySQL from MSSQL 7. I need to port the following > into MySQL from an existing (working) query on MSSQL Server: > > SELECT product_id, name, description > FROM products > WHERE product_id NOT IN (SELECT product_id FROM sales WHERE customer_id = > 10) > > i.e. get all the products that a particular customer has not already bought > > This errors, and I have since read that the MySQL "IN" does not allow > sub-queries, but also seen examples of it done. Is it only supported in a > later version? We are running v. 3.23.3. > > I have also tried: > > SELECT product_id, name, description, sales.sale_id > FROM products LEFT JOIN sales ON products.product_id = sales.product_id > WHERE sales.customer_id = 10 AND sales.sale_id IS NULL This query should return no rows, because if you retrieve rows where sales.sale_id is NULL, customer_id for these rows also will be NULL, not 10. > This does not return any records as it seems to ignoring the LEFT JOIN part > when I stick on the "WHERE sales.customer_id = 10". > (pretty sure this query would work in MS-SQL) > > There must be a way to do this, but I dont seem to be able to put my finger > on it and I would appreciate any help! You can rewrite the initial query as: SELECT products.* FROM products, sales LEFT JOIN sales ss ON products.product_id=ss.product_id AND sales.product_id=ss.product_id WHERE sales.customer_id=10 AND ss.product_id IS NULL -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SubQueries and IN
> > > You need to get the conditions for the LEFT JOIN out of the WHERE clause: > > SELECT product_id, name, description, sales.sale_id > FROM products > LEFT JOIN sales ON > products.product_id = sales.product_id WHERE > sales.sale_id IS NULL AND > sales.customer_id = 10 > I lied in my last email - this did do the trick! I was not aware that you could stick multiple clauses for the "ON" section. It all makes sense now! Thanks again Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SubQueries and IN
Thanks for the query suggestions, but unfortunately none of them seem to do the trick. Not possible to upgrade to 4.x at the moment, so I am going to have to do it in 2 queries; one to get the list of ID's, then create a list in PHP and drop it into the second query. Thanks for the help! Andy Hall. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SubQueries and IN
* Andy Hall > I have just started using MySQL from MSSQL 7. I need to port the following > into MySQL from an existing (working) query on MSSQL Server: > > SELECT product_id, name, description > FROM products > WHERE product_id NOT IN (SELECT product_id FROM sales WHERE customer_id = > 10) > > i.e. get all the products that a particular customer has not > already bought > > This errors, and I have since read that the MySQL "IN" does not allow > sub-queries, but also seen examples of it done. Is it only supported in a > later version? We are running v. 3.23.3. That is a very old version... you should upgrade if you can. Version 4.0 is the current recommended version: http://www.mysql.com/downloads/index.html > sub-queries will be allowed from mysql version 4.1 (not yet stable): http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html > See also this page, describing which versions of MySQL will support which new feature: http://www.mysql.com/doc/en/Roadmap.html > > I have also tried: > > SELECT product_id, name, description, sales.sale_id > FROM products LEFT JOIN sales ON products.product_id = sales.product_id > WHERE sales.customer_id = 10 AND sales.sale_id IS NULL > > This does not return any records as it seems to ignoring the LEFT > JOIN part > when I stick on the "WHERE sales.customer_id = 10". > (pretty sure this query would work in MS-SQL) > > There must be a way to do this, but I dont seem to be able to put > my finger on it and I would appreciate any help! You need to get the conditions for the LEFT JOIN out of the WHERE clause: SELECT product_id, name, description, sales.sale_id FROM products LEFT JOIN sales ON products.product_id = sales.product_id AND sales.customer_id = 10 WHERE sales.sale_id IS NULL Hope this helps, -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SubQueries and IN
> sub-queries, but also seen examples of it done. Is it only supported in a > later version? We are running v. 3.23.3. As far as I know, subqueries are only supported in MySQL v4(.1?)+ Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SubQueries and IN
Andy: Sub queries are supported as of version 4.1 (see link #1). As for your query, double-check the syntax in the select piece. Specifically take out the 'sales.sale_id' and anything else from the 'sales' table. Then try again. Regards, Adam Link #1 - http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html -Original Message- From: Andy Hall [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 10:02 AM To: [EMAIL PROTECTED] Subject: SubQueries and IN Hi, I have just started using MySQL from MSSQL 7. I need to port the following into MySQL from an existing (working) query on MSSQL Server: SELECT product_id, name, description FROM products WHERE product_id NOT IN (SELECT product_id FROM sales WHERE customer_id = 10) i.e. get all the products that a particular customer has not already bought This errors, and I have since read that the MySQL "IN" does not allow sub-queries, but also seen examples of it done. Is it only supported in a later version? We are running v. 3.23.3. I have also tried: SELECT product_id, name, description, sales.sale_id FROM products LEFT JOIN sales ON products.product_id = sales.product_id WHERE sales.customer_id = 10 AND sales.sale_id IS NULL This does not return any records as it seems to ignoring the LEFT JOIN part when I stick on the "WHERE sales.customer_id = 10". (pretty sure this query would work in MS-SQL) There must be a way to do this, but I dont seem to be able to put my finger on it and I would appreciate any help! Thanks Andy Hall. -- 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: SubQueries and IN
Andy Hall wrote: Hi, I have just started using MySQL from MSSQL 7. I need to port the following into MySQL from an existing (working) query on MSSQL Server: SELECT product_id, name, description FROM products WHERE product_id NOT IN (SELECT product_id FROM sales WHERE customer_id = 10) i.e. get all the products that a particular customer has not already bought This errors, and I have since read that the MySQL "IN" does not allow sub-queries, but also seen examples of it done. Is it only supported in a later version? We are running v. 3.23.3. subqueries requieres 4.x try SELECT product_id, name, description FROM products LEFT JOIN sales ON products.product_id = sales.product_id WHERE NOT sales.customer_id = 10 I have also tried: SELECT product_id, name, description, sales.sale_id FROM products LEFT JOIN sales ON products.product_id = sales.product_id WHERE sales.customer_id = 10 AND sales.sale_id IS NULL This does not return any records as it seems to ignoring the LEFT JOIN part when I stick on the "WHERE sales.customer_id = 10". (pretty sure this query would work in MS-SQL) this seems a bit different then this before but should work, does SELECT * FROM sales WHERE sales.customer_id = 10 AND sales.sale_id IS NULL return any results? There must be a way to do this, but I dont seem to be able to put my finger on it and I would appreciate any help! -- Sebastian Mendel www.sebastianmendel.de www.tekkno4u.de www.nofetish.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SubQueries and Temp Tables
You brought it up. You made the comparison. Give us a break. Dave Morse wrote: >Looks like you want to bring up something that doesn't have anything to >do with the original question which is your perogative. But please >don't compare MySQL with Oracle and mainstream SQL RDBMSs. Give us a >break. > >>-Original Message- >>From: Andrew Houghton [mailto:[EMAIL PROTECTED]] >>Sent: Thursday, June 27, 2002 9:10 AM >>To: Dave Morse >>Cc: 'Paul DuBois'; 'Arul'; [EMAIL PROTECTED]; 'MySQL' >>Subject: Re: SubQueries and Temp Tables >> >> >>It makes sense to many, or most, I'd say. Oracle produces >>clients in a >>variety of languages. So does every major database vendor. There is >>zero import in the difference between the languages a server >>and client >>are written in.. it's all a network protocol, for God's sake, so who >>gives a damn? >> >>- a. >> >>Dave Morse wrote: >> >>>NO - mine is a honest question. An "open source" server in C and a >>>client in Java makes sense, I guess to some. >>> >>> >>>>-Original Message- >>>>From: Paul DuBois [mailto:[EMAIL PROTECTED]] >>>>Sent: Wednesday, June 26, 2002 5:37 PM >>>>To: Dave Morse; 'Arul'; [EMAIL PROTECTED] >>>>Cc: 'MySQL' >>>>Subject: RE: SubQueries and Temp Tables >>>> >>>> >>>>At 7:16 -0700 6/26/02, Dave Morse wrote: >>>> >>>>>MySQL is barely an SQL database - it doesn't support much >>>>> >>>>basic SQL 89 >>>> >>>>>functionality. Can any one throw some light on why professional SQL >>>>>database developers want to use it for anything but simple file >>>>>management? AND It is written in C as well so why do Java >>>>> >>developers >> >>>>>use it? >>>>> >>>>Given that the server is a separate program than Java developers >>>>would write *clients* for, why would it matter in the least what >>>>language the server is written in. >>>> >>>>You're trolling, right? >>>> >>>> >>>>>Regards, >>>>> >>>>>Dave >>>>> >>>>> >>>>>>-Original Message- >>>>>>From: Arul [mailto:[EMAIL PROTECTED]] >>>>>>Sent: Tuesday, June 25, 2002 9:05 PM >>>>>>To: [EMAIL PROTECTED] >>>>>>Cc: MySQL >>>>>>Subject: SubQueries and Temp Tables >>>>>> >>>>>> >>>>>>Hi All >>>>>> >>>>>> I am currently porting our application from Oracle to MySQL. >>>>>>We have some subqueries in oracle which cannot be ported into >>>>>>MySQL.We even >>>>>>tried some joins which didnt work out.. >>>>>> >>>>>> So Could anyone throw some light on Temporary tables.Is this >>>>>>the right way >>>>>> to do it.. >>>>>> >>>>>> Since our main query is dependent on the inner query we >>>>>>thought we could >>>>>>run >>>>>> the inner queryfirst and create a temp table.Then we could >>>>>>have a join >>>>>> between the main table and the inner table. >>>>>> >>>>>> Is this advisable.. >>>>>> >>>>>> Any other better options? >>>>>> >>>>>>Regards, >>>>>> -Arul >>>>>> >>>>>> >>>>>> >>>>>> >>>> >>>> >>- >> >>>>>>Please check >>>>>>"http://www.mysql.com/Manual_chapter/manual> _toc.html" before >>>>>> >>>>>>posting. To request this thread, e-mail >>>>>>[EMAIL PROTECTED] >>>>>> >>>>>>To unsubscribe, send a message to the address shown in the >>>>>>List-Unsubscribe header of this message. If you cannot see it, >>>>>>e-mail [EMAIL PROTECTED] instead. >>>>>> >>>>> >>>>> >>>>>--
RE: SubQueries and Temp Tables
Looks like you want to bring up something that doesn't have anything to do with the original question which is your perogative. But please don't compare MySQL with Oracle and mainstream SQL RDBMSs. Give us a break. > -Original Message- > From: Andrew Houghton [mailto:[EMAIL PROTECTED]] > Sent: Thursday, June 27, 2002 9:10 AM > To: Dave Morse > Cc: 'Paul DuBois'; 'Arul'; [EMAIL PROTECTED]; 'MySQL' > Subject: Re: SubQueries and Temp Tables > > > It makes sense to many, or most, I'd say. Oracle produces > clients in a > variety of languages. So does every major database vendor. There is > zero import in the difference between the languages a server > and client > are written in.. it's all a network protocol, for God's sake, so who > gives a damn? > > - a. > > Dave Morse wrote: > > NO - mine is a honest question. An "open source" server in C and a > > client in Java makes sense, I guess to some. > > > > > >>-Original Message- > >>From: Paul DuBois [mailto:[EMAIL PROTECTED]] > >>Sent: Wednesday, June 26, 2002 5:37 PM > >>To: Dave Morse; 'Arul'; [EMAIL PROTECTED] > >>Cc: 'MySQL' > >>Subject: RE: SubQueries and Temp Tables > >> > >> > >>At 7:16 -0700 6/26/02, Dave Morse wrote: > >> > >>>MySQL is barely an SQL database - it doesn't support much > >> > >>basic SQL 89 > >> > >>>functionality. Can any one throw some light on why professional SQL > >>>database developers want to use it for anything but simple file > >>>management? AND It is written in C as well so why do Java > developers > >>>use it? > >> > >>Given that the server is a separate program than Java developers > >>would write *clients* for, why would it matter in the least what > >>language the server is written in. > >> > >>You're trolling, right? > >> > >> > >>>Regards, > >>> > >>>Dave > >>> > >>> > >>>> -Original Message- > >>>> From: Arul [mailto:[EMAIL PROTECTED]] > >>>> Sent: Tuesday, June 25, 2002 9:05 PM > >>>> To: [EMAIL PROTECTED] > >>>> Cc: MySQL > >>>> Subject: SubQueries and Temp Tables > >>>> > >>>> > >>>> Hi All > >>>> > >>>> I am currently porting our application from Oracle to MySQL. > >>>> We have some subqueries in oracle which cannot be ported into > >>>> MySQL.We even > >>>> tried some joins which didnt work out.. > >>>> > >>>> So Could anyone throw some light on Temporary tables.Is this > >>>> the right way > >>>> to do it.. > >>>> > >>>> Since our main query is dependent on the inner query we > >>>> thought we could > >>>> run > >>>> the inner queryfirst and create a temp table.Then we could > >>>> have a join > >>>> between the main table and the inner table. > >>>> > >>>> Is this advisable.. > >>>> > >>>> Any other better options? > >>>> > >>>> Regards, > >>>> -Arul > >>>> > >>>> > >>>> > >>>> > >>> > >> > - > >> > >>>> Please check > >>>> "http://www.mysql.com/Manual_chapter/manual> _toc.html" before > >>>> > >>>> posting. To request this thread, e-mail > >>>> [EMAIL PROTECTED] > >>>> > >>>> To unsubscribe, send a message to the address shown in the > >>>> List-Unsubscribe header of this message. If you cannot see it, > >>>> e-mail [EMAIL PROTECTED] instead. > >>>> > >>> > >>> > >>> > >>>--- > -- > >>>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 > >> > >> > > > > > > > > > > > - > > Please check > "http://www.mysql.com/Manual_chapter/manual> _toc.html" before > > > posting. To request this thread, e-mail > [EMAIL PROTECTED] > > > > To unsubscribe, send a message to the address shown in the > > List-Unsubscribe header of this message. If you cannot see it, > > e-mail [EMAIL PROTECTED] instead. > > > > > > > > > > - > Please check > "http://www.mysql.com/Manual_chapter/manual> _toc.html" before > > posting. To request this thread, e-mail > [EMAIL PROTECTED] > > To unsubscribe, send a message to the address shown in the > List-Unsubscribe header of this message. If you cannot see it, > e-mail [EMAIL PROTECTED] instead. > > - 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: SubQueries and Temp Tables
Dave, The language that the server is written in is irrelevant. Oracle, SQL Server, Sybase and most other databases are written in C and Java developers do business in them just fine. That's why JDBC is for. Many legacy systems are written in C, C++, or other language. That is another area where EJB shines, in frontending legacy systems with Java/JSP clients. The reason this makes sense to most is because this is how the real world works. I love writing in Java and don't want to go back to C/C++; but C/C++ makes sense for operating systems like Linux or db systems like MySQL. That isn't likely to change in the next 10-20 years, if ever. Even when 20Ghz+ machines come about and a "Java Linux" would be reasonable, the C version is already there and would still greatly outperform a Java version. The same is true for any of the database systems out there, including MySQL So bridges between these worlds is where the action is. Other people can best answer your objections to MySQL itself. For myself, I believe that it does support most of '89 but do wish it had Stored Procedures. Subselects are on the way. Foreign key support is coming, though I don't care for that much. The transactional tables covered the biggest objections I had. I am able to do "real" database applications with it as are many professional database developers. Cheers, Frank Dave Morse wrote: > NO - mine is a honest question. An "open source" server in C and a > client in Java makes sense, I guess to some. > > > -Original Message- > > From: Paul DuBois [mailto:[EMAIL PROTECTED]] > > Sent: Wednesday, June 26, 2002 5:37 PM > > To: Dave Morse; 'Arul'; [EMAIL PROTECTED] > > Cc: 'MySQL' > > Subject: RE: SubQueries and Temp Tables > > > > > > At 7:16 -0700 6/26/02, Dave Morse wrote: > > >MySQL is barely an SQL database - it doesn't support much > > basic SQL 89 > > >functionality. Can any one throw some light on why professional SQL > > >database developers want to use it for anything but simple file > > >management? AND It is written in C as well so why do Java developers > > >use it? > > > > Given that the server is a separate program than Java developers > > would write *clients* for, why would it matter in the least what > > language the server is written in. > > > > You're trolling, right? > > > > > > > >Regards, > > > > > >Dave > > > > > >> -Original Message- > > >> From: Arul [mailto:[EMAIL PROTECTED]] > > >> Sent: Tuesday, June 25, 2002 9:05 PM > > >> To: [EMAIL PROTECTED] > > >> Cc: MySQL > > >> Subject: SubQueries and Temp Tables > > >> > > >> > > >> Hi All > > >> > > >> I am currently porting our application from Oracle to MySQL. > > >> We have some subqueries in oracle which cannot be ported into > > >> MySQL.We even > > >> tried some joins which didnt work out.. > > >> > > >> So Could anyone throw some light on Temporary tables.Is this > > >> the right way > > >> to do it.. > > >> > > >> Since our main query is dependent on the inner query we > > >> thought we could > > >> run > > >> the inner queryfirst and create a temp table.Then we could > > >> have a join > > >> between the main table and the inner table. > > >> > > >> Is this advisable.. > > >> > > >> Any other better options? > > >> > > >> Regards, > > >> -Arul > > >> > > >> > > >> > > >> > > - > > >> Please check > > >> "http://www.mysql.com/Manual_chapter/manual> _toc.html" before > > >> > > >> posting. To request this thread, e-mail > > >> [EMAIL PROTECTED] > > >> > > >> To unsubscribe, send a message to the address shown in the > > >> List-Unsubscribe header of this message. If you cannot see it, > > >> e-mail [EMAIL PROTECTED] instead. > > >> > > > > > > > > > > > >- > > >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 > > > > > > - > Please check "http://www.mysql.com/Manual_chapter/manual_toc.html"; before > posting. To request this thread, e-mail [EMAIL PROTECTED] > > To unsubscribe, send a message to the address shown in the > List-Unsubscribe header of this message. If you cannot see it, > e-mail [EMAIL PROTECTED] instead. - 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: SubQueries and Temp Tables
It makes sense to many, or most, I'd say. Oracle produces clients in a variety of languages. So does every major database vendor. There is zero import in the difference between the languages a server and client are written in.. it's all a network protocol, for God's sake, so who gives a damn? - a. Dave Morse wrote: > NO - mine is a honest question. An "open source" server in C and a > client in Java makes sense, I guess to some. > > >>-Original Message- >>From: Paul DuBois [mailto:[EMAIL PROTECTED]] >>Sent: Wednesday, June 26, 2002 5:37 PM >>To: Dave Morse; 'Arul'; [EMAIL PROTECTED] >>Cc: 'MySQL' >>Subject: RE: SubQueries and Temp Tables >> >> >>At 7:16 -0700 6/26/02, Dave Morse wrote: >> >>>MySQL is barely an SQL database - it doesn't support much >> >>basic SQL 89 >> >>>functionality. Can any one throw some light on why professional SQL >>>database developers want to use it for anything but simple file >>>management? AND It is written in C as well so why do Java developers >>>use it? >> >>Given that the server is a separate program than Java developers >>would write *clients* for, why would it matter in the least what >>language the server is written in. >> >>You're trolling, right? >> >> >>>Regards, >>> >>>Dave >>> >>> >>>> -Original Message- >>>> From: Arul [mailto:[EMAIL PROTECTED]] >>>> Sent: Tuesday, June 25, 2002 9:05 PM >>>> To: [EMAIL PROTECTED] >>>> Cc: MySQL >>>> Subject: SubQueries and Temp Tables >>>> >>>> >>>> Hi All >>>> >>>> I am currently porting our application from Oracle to MySQL. >>>> We have some subqueries in oracle which cannot be ported into >>>> MySQL.We even >>>> tried some joins which didnt work out.. >>>> >>>> So Could anyone throw some light on Temporary tables.Is this >>>> the right way >>>> to do it.. >>>> >>>> Since our main query is dependent on the inner query we >>>> thought we could >>>> run >>>> the inner queryfirst and create a temp table.Then we could >>>> have a join >>>> between the main table and the inner table. >>>> >>>> Is this advisable.. >>>> >>>> Any other better options? >>>> >>>> Regards, >>>> -Arul >>>> >>>> >>>> >>>> >>> >>- >> >>>> Please check >>>> "http://www.mysql.com/Manual_chapter/manual> _toc.html" before >>>> >>>> posting. To request this thread, e-mail >>>> [EMAIL PROTECTED] >>>> >>>> To unsubscribe, send a message to the address shown in the >>>> List-Unsubscribe header of this message. If you cannot see it, >>>> e-mail [EMAIL PROTECTED] instead. >>>> >>> >>> >>> >>>- >>>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 >> >> > > > > > - > Please check "http://www.mysql.com/Manual_chapter/manual_toc.html"; before > posting. To request this thread, e-mail [EMAIL PROTECTED] > > To unsubscribe, send a message to the address shown in the > List-Unsubscribe header of this message. If you cannot see it, > e-mail [EMAIL PROTECTED] instead. > > > - 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: SubQueries and Temp Tables
At 7:12 -0700 6/27/02, Dave Morse wrote: >NO - mine is a honest question. An "open source" server in C and a >client in Java makes sense, I guess to some. I guess I'm not sure why it wouldn't make sense. If you're running a Web browser that's written in Java, do you require a Web server to be written in Java before you'll connect to it? > >> -Original Message- >> From: Paul DuBois [mailto:[EMAIL PROTECTED]] >> Sent: Wednesday, June 26, 2002 5:37 PM >> To: Dave Morse; 'Arul'; [EMAIL PROTECTED] >> Cc: 'MySQL' >> Subject: RE: SubQueries and Temp Tables >> >> >> At 7:16 -0700 6/26/02, Dave Morse wrote: >> >MySQL is barely an SQL database - it doesn't support much >> basic SQL 89 >> >functionality. Can any one throw some light on why professional SQL >> >database developers want to use it for anything but simple file >> >management? AND It is written in C as well so why do Java developers >> >use it? >> >> Given that the server is a separate program than Java developers >> would write *clients* for, why would it matter in the least what >> language the server is written in. >> >> You're trolling, right? >> >> > >> >Regards, >> > >> >Dave >> > >> >> -Original Message- >> >> From: Arul [mailto:[EMAIL PROTECTED]] >> >> Sent: Tuesday, June 25, 2002 9:05 PM >> >> To: [EMAIL PROTECTED] >> >> Cc: MySQL >> >> Subject: SubQueries and Temp Tables >> >> >> >> >> >> Hi All >> >> >> >> I am currently porting our application from Oracle to MySQL. >> >> We have some subqueries in oracle which cannot be ported into >> >> MySQL.We even >> >> tried some joins which didnt work out.. >> >> >> >> So Could anyone throw some light on Temporary tables.Is this >> >> the right way >> >> to do it.. >> >> >> >> Since our main query is dependent on the inner query we >> >> thought we could >> >> run >> >> the inner queryfirst and create a temp table.Then we could >> >> have a join >> >> between the main table and the inner table. >> >> >> >> Is this advisable.. >> >> >> >> Any other better options? >> >> >> >> Regards, >> >> -Arul >> >> >> >> >> >> >> >> >> - >> >> Please check >> >> "http://www.mysql.com/Manual_chapter/manual> _toc.html" before >> >> >> >> posting. To request this thread, e-mail >> >> [EMAIL PROTECTED] >> >> >> >> To unsubscribe, send a message to the address shown in the >> >> List-Unsubscribe header of this message. If you cannot see it, >> >> e-mail [EMAIL PROTECTED] instead. >> >> >> > >> > >> > >> >- >> >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: SubQueries and Temp Tables
NO - mine is a honest question. An "open source" server in C and a client in Java makes sense, I guess to some. > -Original Message- > From: Paul DuBois [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, June 26, 2002 5:37 PM > To: Dave Morse; 'Arul'; [EMAIL PROTECTED] > Cc: 'MySQL' > Subject: RE: SubQueries and Temp Tables > > > At 7:16 -0700 6/26/02, Dave Morse wrote: > >MySQL is barely an SQL database - it doesn't support much > basic SQL 89 > >functionality. Can any one throw some light on why professional SQL > >database developers want to use it for anything but simple file > >management? AND It is written in C as well so why do Java developers > >use it? > > Given that the server is a separate program than Java developers > would write *clients* for, why would it matter in the least what > language the server is written in. > > You're trolling, right? > > > > >Regards, > > > >Dave > > > >> -Original Message- > >> From: Arul [mailto:[EMAIL PROTECTED]] > >> Sent: Tuesday, June 25, 2002 9:05 PM > >> To: [EMAIL PROTECTED] > >> Cc: MySQL > >> Subject: SubQueries and Temp Tables > >> > >> > >> Hi All > >> > >> I am currently porting our application from Oracle to MySQL. > >> We have some subqueries in oracle which cannot be ported into > >> MySQL.We even > >> tried some joins which didnt work out.. > >> > >> So Could anyone throw some light on Temporary tables.Is this > >> the right way > >> to do it.. > >> > >> Since our main query is dependent on the inner query we > >> thought we could > >> run > >> the inner queryfirst and create a temp table.Then we could > >> have a join > >> between the main table and the inner table. > >> > >> Is this advisable.. > >> > >> Any other better options? > >> > >> Regards, > >> -Arul > >> > >> > >> > >> > - > >> Please check > >> "http://www.mysql.com/Manual_chapter/manual> _toc.html" before > >> > >> posting. To request this thread, e-mail > >> [EMAIL PROTECTED] > >> > >> To unsubscribe, send a message to the address shown in the > >> List-Unsubscribe header of this message. If you cannot see it, > >> e-mail [EMAIL PROTECTED] instead. > >> > > > > > > > >- > >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: SubQueries and Temp Tables
At 7:16 -0700 6/26/02, Dave Morse wrote: >MySQL is barely an SQL database - it doesn't support much basic SQL 89 >functionality. Can any one throw some light on why professional SQL >database developers want to use it for anything but simple file >management? AND It is written in C as well so why do Java developers >use it? Given that the server is a separate program than Java developers would write *clients* for, why would it matter in the least what language the server is written in. You're trolling, right? > >Regards, > >Dave > >> -Original Message- >> From: Arul [mailto:[EMAIL PROTECTED]] >> Sent: Tuesday, June 25, 2002 9:05 PM >> To: [EMAIL PROTECTED] >> Cc: MySQL >> Subject: SubQueries and Temp Tables >> >> >> Hi All >> >> I am currently porting our application from Oracle to MySQL. >> We have some subqueries in oracle which cannot be ported into >> MySQL.We even >> tried some joins which didnt work out.. >> >> So Could anyone throw some light on Temporary tables.Is this >> the right way >> to do it.. >> >> Since our main query is dependent on the inner query we >> thought we could >> run >> the inner queryfirst and create a temp table.Then we could >> have a join >> between the main table and the inner table. >> >> Is this advisable.. >> >> Any other better options? >> >> Regards, >> -Arul >> >> >> >> - >> Please check >> "http://www.mysql.com/Manual_chapter/manual> _toc.html" before >> >> posting. To request this thread, e-mail >> [EMAIL PROTECTED] >> >> To unsubscribe, send a message to the address shown in the >> List-Unsubscribe header of this message. If you cannot see it, >> e-mail [EMAIL PROTECTED] instead. >> > > > >- >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: SubQueries and Temp Tables
It's hard to give a common suggestion which is suitable for all cases. We need to know the exact queries being executed. Arul wrote: > Agreed Ralf... > Not all SubQueries can be replaced by Joins.. > Thats why we have planned for Temp Tables.. > Any ideas on it.. > > > - Original Message - > From: "Ralf Narozny" <[EMAIL PROTECTED]> > To: "Arul" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]>; "MySQL" <[EMAIL PROTECTED]> > Sent: Wednesday, June 26, 2002 3:00 PM > Subject: Re: SubQueries and Temp Tables > > > >>Hiho hiho! >> >>Which joins could that be? I think most if not all subqueries should be >>replacable with joins. >> >>Greetings >> Ralf >> >>Arul wrote: >> >> >>>Hi All >>> >>>I am currently porting our application from Oracle to MySQL. >>>We have some subqueries in oracle which cannot be ported into MySQL.We >> > even > >>>tried some joins which didnt work out.. >>> >>>So Could anyone throw some light on Temporary tables.Is this the right >> > way > >>>to do it.. >>> >>>Since our main query is dependent on the inner query we thought we could >>>run >>>the inner queryfirst and create a temp table.Then we could have a join >>>between the main table and the inner table. >>> >>>Is this advisable.. >>> >>>Any other better options? >>> >>> >>> >> >>-- >>Ralf Narozny >>Splendid Internet GmbH >>Skandinaviendamm 212, 24109 Kiel, Germany >>fon: +49 431 660 97 0, fax: +49 431 660 97 20 >>mailto:[EMAIL PROTECTED], http://www.splendid.de >> >> >> > > > > - > 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 > > > -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Barkov <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Izhevsk, Russia <___/ www.mysql.com +7-902-856-80-21 - 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: SubQueries and Temp Tables
MySQL is barely an SQL database - it doesn't support much basic SQL 89 functionality. Can any one throw some light on why professional SQL database developers want to use it for anything but simple file management? AND It is written in C as well so why do Java developers use it? Regards, Dave > -Original Message- > From: Arul [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, June 25, 2002 9:05 PM > To: [EMAIL PROTECTED] > Cc: MySQL > Subject: SubQueries and Temp Tables > > > Hi All > > I am currently porting our application from Oracle to MySQL. > We have some subqueries in oracle which cannot be ported into > MySQL.We even > tried some joins which didnt work out.. > > So Could anyone throw some light on Temporary tables.Is this > the right way > to do it.. > > Since our main query is dependent on the inner query we > thought we could > run > the inner queryfirst and create a temp table.Then we could > have a join > between the main table and the inner table. > > Is this advisable.. > > Any other better options? > > Regards, > -Arul > > > > - > Please check > "http://www.mysql.com/Manual_chapter/manual> _toc.html" before > > posting. To request this thread, e-mail > [EMAIL PROTECTED] > > To unsubscribe, send a message to the address shown in the > List-Unsubscribe header of this message. If you cannot see it, > e-mail [EMAIL PROTECTED] instead. > - 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: SubQueries and Temp Tables
Agreed Ralf... Not all SubQueries can be replaced by Joins.. Thats why we have planned for Temp Tables.. Any ideas on it.. - Original Message - From: "Ralf Narozny" <[EMAIL PROTECTED]> To: "Arul" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; "MySQL" <[EMAIL PROTECTED]> Sent: Wednesday, June 26, 2002 3:00 PM Subject: Re: SubQueries and Temp Tables > Hiho hiho! > > Which joins could that be? I think most if not all subqueries should be > replacable with joins. > > Greetings > Ralf > > Arul wrote: > > >Hi All > > > > I am currently porting our application from Oracle to MySQL. > >We have some subqueries in oracle which cannot be ported into MySQL.We even > >tried some joins which didnt work out.. > > > > So Could anyone throw some light on Temporary tables.Is this the right way > > to do it.. > > > > Since our main query is dependent on the inner query we thought we could > >run > > the inner queryfirst and create a temp table.Then we could have a join > > between the main table and the inner table. > > > > Is this advisable.. > > > > Any other better options? > > > > > > > > -- > Ralf Narozny > Splendid Internet GmbH > Skandinaviendamm 212, 24109 Kiel, Germany > fon: +49 431 660 97 0, fax: +49 431 660 97 20 > mailto:[EMAIL PROTECTED], http://www.splendid.de > > > - 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: SubQueries and Temp Tables
Hiho hiho! Which joins could that be? I think most if not all subqueries should be replacable with joins. Greetings Ralf Arul wrote: >Hi All > > I am currently porting our application from Oracle to MySQL. >We have some subqueries in oracle which cannot be ported into MySQL.We even >tried some joins which didnt work out.. > > So Could anyone throw some light on Temporary tables.Is this the right way > to do it.. > > Since our main query is dependent on the inner query we thought we could >run > the inner queryfirst and create a temp table.Then we could have a join > between the main table and the inner table. > > Is this advisable.. > > Any other better options? > > > -- Ralf Narozny Splendid Internet GmbH Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - 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: SubQueries
Arul, Wednesday, May 29, 2002, 1:05:02 PM, you wrote: A> i am running MySql 3.23.49 Max on Win 2K A> Does this version of MySQL supports subqueries. Nope. How to re-write queries read in our manual: http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html A> Also does this support transaction.. MySQL has transaction-unsafe table types(ISAM, MyISAM, HEAP) and transaction-safe tables (InnoDB, BDB). So, if you want to use transactions you should install MySQL server with InnoDB or BDB support. http://www.mysql.com/doc/T/a/Table_types.html If you install MySQL from the source distribution you should configure MySQL with --with-innodb option or --with-berkeley-db option. If you use binary distribution, you should install MySQL-Max: http://www.mysql.com/doc/m/y/mysqld-max.html A> Where can i get the details reg this.. In the MySQL manual: http://www.mysql.com/documentation/index.html A> Regards, A> Arul -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.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
Re: Subqueries
It can't, try using JOIN. Tyler Longren Captain Jack Communications [EMAIL PROTECTED] www.captainjack.com - Original Message - From: "Leo Przybylski" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, April 03, 2002 1:35 PM Subject: Subqueries > Hello all, > > Does anyone know if MySQL can do subqueries? > > I am trying to provide a SELECT subquery to an IN clause and I am getting > errors. Is this possible? > > -Leo > > - > 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: Subqueries
On Wednesday 03 April 2002 12:35 pm, Leo Przybylski wrote: > Hello all, > > Does anyone know if MySQL can do subqueries? Not the current version, no. > > I am trying to provide a SELECT subquery to an IN clause and I am getting > errors. Is this possible? Read the manual and follow the example on how to rewrite your query. - 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: subqueries?
No that's a 4.1 TODO. Bryan Capitano wrote: > > Do any of the newer versions of MySQL support sub-queries yet? > > - > 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: subqueries / nested queries
Hi. For many sub-queries, there exists a one-query work-around. Most of the rest, you can work-around with some commands. And then, there are a few around which one has to work in application. There is a section in the manual about how to handle the lack of sub-queries, which explains the most common work-around(s). If you still don't know how to rewrite your query, post the sub-query you want to / would use, and probably someone will give you the right pointer where to start. Bye, Benjamin. On Fri, Feb 02, 2001 at 11:33:11AM +1030, [EMAIL PROTECTED] wrote: > I understand that MySQL does not support subqueries or nested > queries. Is there a simple workaround or do I have to do a number of > queries and manipulate them outside of MySQL? I am using PHP for > server side code. Where can I look for answers? > > Terence - 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
