RE: Oracle CONNECT BY
This will get a manager of a team which contains a team which contains individual nr 5. SELECT I.Name FROM TEAMREPORT T3 TEAMREPORT T2 TEAMREPORT T, INDIVIDUAL I, WHERE T.individualId = 5 AND t.teamid = t2.indivdualID AND t3.IndividualId = t2.teamID AND T3.IndividualID = I.IndividualID AND I.Manager = 1. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: maandag 24 september 2001 17:40 To: [EMAIL PROTECTED] Subject: Oracle CONNECT BY Howdy all... New to MySQL and I've got a question. I have an application that does a good deal of hierarchical reporting. In order to do this one of the tables in my database contains nothing more than an individuals id and the team to which that individual reports TABLE - TEAMREPORT e.g. Individual ID(PK,FK) Team ID _ |1 |123 | _ |2 |123 | _ |3 |456 | _ |4 |789 | _ |5 |789 | _ I can get much more information by linking this table to another table: TABLE - INDIVIDUAL IndividualID Date Name Manager _ |1|2001-09-01|Smith |1 | _ |2|2001-09-01|Black |0 | _ |3|2001-09-01|Anderson |1 | _ |4|2001-09-01|Paul |1 | _ |5|2001-09-01|Wells |0 | _ (The Manager field tells me if that person is the manager of the team; 1=Manager, 0=NotManager) So if I wanted to find out what team individual #4 reported to I would simply SELECT TEAMID FROM table WHERE INDIVIDUAL ID = 4. If I wanted to find out who was the manager of team 789: SELECT I.Name FROM TEAMREPORT T, INDIVIDUAL I, WHERE T.IndividualID = I.IndividualID AND Manager = 1. Of course the problem at hand is a little more complex. It is possible to have more than 1 level of hierarchy. In other words, team 789 reports to team 123. So, in plain English, Individual 5 reports to Team 789 which, in turn, reports to Team 123. What I would like to find out is, if I start with individual #5 and #5 reports to team 789, which also reports to team 123, who is the Manger of team 123 (I need the manager 2 levels up rather than 1)? Of course a subquery would be ideal in this situation, but obviously this cannot happen. I know that many subqueries can be turned into a join (or multiple join) statement but I cannot figure out that statement for the life of me. I'd hate to turn one query into two, but at this point my SQL inexperience is forcing me to do so. Any SQL wizards have any ideas? If I was using Oracle, the CONNECT BY clause would be an option. Is there anything similar in MySQL? Hope this makes sense and I would love to hear any thoughts. T.J. - 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: multiple select
SELECT * from tipps WHERE (name like '%&name%' OR '$name' = '') AND (stadt = '$stadt' OR '$stadt' = '') AND (kueche = '$kueche' OR '$kueche' = '') ORDER by $order ASC LIMIT $start, $limit -Original Message- From: Chris [mailto:[EMAIL PROTECTED]] Sent: dinsdag 18 september 2001 13:05 To: MySql Subject: Re: multiple select If this is your query, you might have a problem at the first 'AND' : SELECT * from tipps AND name LIKE '%$name%' AND stadt = '$stadt' AND kueche= '$kueche' ORDER by $order ASC LIMIT $start, $limit should be: SELECT * from tipps WHERE name LIKE '%$name%' AND stadt = '$stadt' AND kueche= '$kueche' ORDER by $order ASC LIMIT $start, $limit Sorry if this does not answer your question - Original Message - From: "Michael Paffrath" <[EMAIL PROTECTED]> To: "MySql" <[EMAIL PROTECTED]> Sent: Tuesday, September 18, 2001 12:37 Subject: Re: multiple select am 18.09.2001 12:30 Uhr schrieb Chris unter [EMAIL PROTECTED]: > Post your query SELECT * from tipps AND name LIKE '%$name%' AND stadt = '$stadt' AND kueche = '$kueche' ORDER by $order ASC LIMIT $start, $limit Michael Paffrath fatmedia - agentur für interaktive medien Schillerstraße 6 50968 Köln Tel. 0221 - 660 36 63 Fax. 0221 - 276 03 98 http://www.fatmedia.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 *** This message may contain information which is confidential and subject to legal privilege. If you are not the intended recipient, you may not peruse, use, disseminate, distribute or copy this message. If you have received this message in error, please notify the sender immediately by email, facsimile or telephone and return and/or destroy the original message. *** - 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: ERROR 1054 at line 52: Unknown column 'nan' in 'field list'
i can reproduce it on 3.23.32 create table test (id int(13),low float(10,2) ) insert into test values (1,1) insert into test values (1,'nan') id low 1 1.00 1 nan doh.. - 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: Sub select.
you can rewrite this query to simple joins so you dont use sub selects. select media.media_id , media.price, media.filename from media,category_locks,categories where media.media_id = category_locks.media_id and category_locks.category_id = categories.category_id and categories.dynamic_media = 'true' -Original Message- From: Anders Alstrin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 07, 2001 2:58 PM To: [EMAIL PROTECTED] Subject: Sub select. Does anyone know how to do this sub select in MySQL. I know how to do it in Oracle! table media +--+---+--+ | media_id | price |filename | +--+---+--+ |3 | 0 | cal.jpg | | 82 | 0 | Soa.jpg | | 13 | 0 | ca.jpg | | 71 | 0 | gt.pdf | | 67 | 0 | low.pdf | | 68 | 0 | 10.pdf | | 83 | 0 | B5.pdf | +--+---+--+ table category_locks +--+-+--+ | category_lock_id | category_id | media_id | +--+-+--+ | 92 | 0 | 68 | | 93 | 0 | 67 | | 94 | 0 | 13 | | 91 | 0 | 71 | | 90 | 0 |3 | | 118 | 43 | 82 | | 121 | 43 | 83 | +--+-+--+ table categories +-+-++---+--+ | category_id | category_up | upload | dynamic_media | name | +-+-++---+--+ | 43 | 0 | false | true | web | | 0 | 0 | false | false | trine| | 0 | 0 | false | false | shop | +-+-++---+--+ A Oracle way to do it: SELECT * FROM media WHERE media_id = (SELECT media_id FROM category_locks WHERE category_id = (SELECT category_id FROM categories WHERE dynamic_media = 'true')) A MySQL way to do it? - 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: Help! MySQL variables laugh at me
if faq had a sequenced primary key you could do the following select a.sezione, IF(a.sezione = b.sezione, "no","yes") as hasChanged from faq as a, faq as b where faq.id = faq.id -1 -Original Message- From: Luca Accomazzi [mailto:[EMAIL PROTECTED]] Sent: Monday, June 04, 2001 11:02 AM To: [EMAIL PROTECTED] Subject: Help! MySQL variables laugh at me I've been frying my brain over this problem for the past 24 hours and I'll be very grateful for any hints. I'm trying to recognize when the value for a field is changing value from one record to the next. For an example, look at this: mysql> select sezione from faq limit 20; +---+ | sezione | +---+ | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Macintosh | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | +---+ 20 rows in set (0.01 sec) As you see, the field's value is almost always "Internet", but it becomes "Macintosh" in the middle. I'd like to recognize this change (and, later, act on it). I thought I had found the solution by employing a bit of variable magic. And it seemed to work just fine: mysql> select sezione, IF(@a=sezione, "no", "yes") as hasChanged, (@a:=sezione) as newValueForMemory from faq limit 20; +---++---+ | sezione | hasChanged | newValueForMemory | +---++---+ | Internet | yes| Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Macintosh | yes| Macintosh | | Internet | yes| Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | +---++---+ 20 rows in set (0.01 sec) I keep a copy of the previous value in a variable, then check that variable against the current value for the field. All is well. BUT, as soon as I tackle my real problem (on a nasty database, complex queries, and the real problem, all hell breaks loose: mysql> select ordine, IF(@oldOrdine=ordine, "no", "yes") as hasChanged, (@oldOrdine:=ordine) as newValueForMemory from aghi1, Not_needle_aux where (GENMATERIA = 'MONOSOF' OR GENMATERIA = 'DERMALON*') and (Needle1='pre-cut' or Needle1='stdlth' or Needle1='reel') and Needle1=Needle_code order by ordine, SIZEMR, LENGTHMETR; +++---+ | ordine | hasChanged | newValueForMemory | +++---+ | 1 | yes| 1 | | 1 | no | 1 | | 1 | no | 1 | | 1 | no | 1 | | 1 | yes| 1 | | 1 | no | 1 | | 1 | no | 1 | | 1 | no | 1 | | 3 | yes| 3 | | 3 | no | 3 | | 3 | yes| 3 | | 3 | no | 3 | | 3 | no | 3 | +++---+ 13 rows in set (0.10 sec) WHY OH WHY is MySQL behaving like this? I tried a bunch on variations (using two variables, applying a function on the field and ever weirder ones), to no avail. It gets even more unpredictable, as if the variables refused to be set and reset. mysql> select @a, ordine, @b:=ordine, @b, IF (@a=@b, 'Very Thin', 'Thin') as divider, @a:=@b from aghi1, Not_needle_aux where (GENMATERIA = 'MONOSOF' OR GENMATERIA = 'DERMALON*') and (Needle1='pre-cut' or Needle1='stdlth' or Needle1='reel') and Needle1=Needle_code order by ordine, SIZEMR, LENGTHMETR; +--+++--+---++ | @a | ordine | @b:=ordine | @b | divider | @a:=@b | +--+++--+---++ | 1| 1 | 1 |1 | Very Thin | 1 | | 1| 1 | 1 |1 | Very Thin | 1 | | 1| 1 | 1 |1 | Very Thin | 1 | | 1| 1 | 1 |1 | Very Thin | 1 | | 1| 1 | 1 |1 | Very Thin | 1 | | 1| 1 | 1 |1 | Very Thin | 1 | | 1| 1 |
RE: COUNT and WHERE
if you use an alias in your select clause use the same alias in the having clause so having num > 1 -Original Message- From: Ben Smith [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 31, 2001 11:00 AM To: [EMAIL PROTECTED] Subject: COUNT and WHERE Hello, I have this query select a.match_id, count(a.players_id) as num, a.description from a group by a.players_id, a.match_id order by num desc I only want to see the records where the count of players_id is greater than 1, and in various other DB's i'd use the Having clause, so i tried where count(a.players_id)>1 but mySQL said Invalid use of Group By function, which i guess is understandable. I'm sure there is an easy solution, anyone with any suggestions ? thanks in advance. Ben. - 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
