Join question
I have two tables, one is a list of users and the other is a list of events for each user. It is a one to many relationship. The event table is pretty simple just an event type and a the date and time of the event in a datetime field. I need a query that shows all events of a certain type for each user, very simple so far. In fact the query I use now is simply, SELECT u.FName, u.LName, e.EventType, DATE(e.DateTime) FROM user u JOIN event e USING(UserID) ORDER BY u.LName, u.FName, e.EventType, e.DateTime The twist comes in that there can be several records for a given user and event type all on the same day, in a case like that, I only want the query to show one record. So I need one record per user per event type per day. The query will strip the time part off of the date time field and only display the date. We don't really care if that event happened 1 or 10 times in one day just that it happened at least once on that day for a user. Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join question
Chris W wrote: I have two tables, one is a list of users and the other is a list of events for each user. It is a one to many relationship. The event table is pretty simple just an event type and a the date and time of the event in a datetime field. I need a query that shows all events of a certain type for each user, very simple so far. In fact the query I use now is simply, SELECT u.FName, u.LName, e.EventType, DATE(e.DateTime) FROM user u JOIN event e USING(UserID) ORDER BY u.LName, u.FName, e.EventType, e.DateTime The twist comes in that there can be several records for a given user and event type all on the same day, in a case like that, I only want the query to show one record. So I need one record per user per event type per day. The query will strip the time part off of the date time field and only display the date. We don't really care if that event happened 1 or 10 times in one day just that it happened at least once on that day for a user. Chris W try DISTINCT. -- Gerald L. Clark Sr. V.P. Development Supplier Systems Corporation Unix since 1982 Linux since 1992 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join question
I have no idea what I was thinking. For some reason I was thinking Distinct wouldn't work, must have been temporarily brain dead. Thanks for the wake up call. Gerald L. Clark wrote: Chris W wrote: I have two tables, one is a list of users and the other is a list of events for each user. It is a one to many relationship. The event table is pretty simple just an event type and a the date and time of the event in a datetime field. I need a query that shows all events of a certain type for each user, very simple so far. In fact the query I use now is simply, SELECT u.FName, u.LName, e.EventType, DATE(e.DateTime) FROM user u JOIN event e USING(UserID) ORDER BY u.LName, u.FName, e.EventType, e.DateTime The twist comes in that there can be several records for a given user and event type all on the same day, in a case like that, I only want the query to show one record. So I need one record per user per event type per day. The query will strip the time part off of the date time field and only display the date. We don't really care if that event happened 1 or 10 times in one day just that it happened at least once on that day for a user. Chris W try DISTINCT. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Join question
Dear all, I have two tables,let's call then a and b: Table a: CUI1|CUI2 C001|C002 C002|C003 C003|C055 C004|C002 ... Table b: CUI|STY C001|T001 C002|T002 C003|T003 C004|T004 C005|T006 C055|T061 .. And the join table should be: T001|T002 T002|T003 T003|T061 T004|T002 ... So,I should convert table a according to table b. Thank you in advance for all your help Best regards, Andrej -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join question
Dear all, I have two tables,let's call then a and b: Table a: CUI1|CUI2 C001|C002 C002|C003 C003|C055 C004|C002 ... Table b: CUI|STY C001|T001 C002|T002 C003|T003 C004|T004 C005|T006 C055|T061 .. And the join table should be: T001|T002 T002|T003 T003|T061 T004|T002 ... I assume that the third table should be the result. If you need to store the result in a table you can use a INSERT ... SELECT query instead of only a SELECT (look INSERT...SELECT up in the online manual). So,I should convert table a according to table b. Thank you in advance for all your help My approach would be the opposite: SELECT t1.`STY`, t3.`STY` FROM `table_b` AS t1 JOIN `table_a` AS t2 ON t1.`CUI` = t2.`CUI1` JOIN `table_b` AS t3 ON t2.`CUI2`=t3.`CUI`; table_a determines which records from table_b must be connected. -- Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
outer join question
This query isn't what I want... select a.rhrqid,a.rhrqsid,a.rhrssid,b.sid,b.rlsid from rqhistory a left join relay b on a.rhrqsid = 101 or a.rhrssid = 101 and (a.rhrqid = b.rqid and (a.rhrqsid = b.rlsid or a.rhrqsid = b.sid or a.rhrssid = b.rlsid or a.rhrssid = b.sid)) because it returns all rows from 'a' regardless of the criteria 101 Any advice on how would I get only rows from 'a' that match 101 and any in 'b' that match if they exist? -km -- View this message in context: http://www.nabble.com/outer-join-question-tf3178361.html#a8819711 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: outer join question
Hello, Try this... select a.rhrqid,a.rhrqsid,a.rhrssid,b.sid,b.rlsid from rqhistory a left join relay b on (a.rhrqid = b.rqid and (a.rhrqsid = b.rlsid or a.rhrqsid = b.sid or a.rhrssid = b.rlsid or a.rhrssid = b.sid)) where a.rhrqsid = 101 or a.rhrssid = 101 Thanks, ViSolve DB Team - Original Message - From: KMiller [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, February 06, 2007 8:37 AM Subject: outer join question This query isn't what I want... select a.rhrqid,a.rhrqsid,a.rhrssid,b.sid,b.rlsid from rqhistory a left join relay b on a.rhrqsid = 101 or a.rhrssid = 101 and (a.rhrqid = b.rqid and (a.rhrqsid = b.rlsid or a.rhrqsid = b.sid or a.rhrssid = b.rlsid or a.rhrssid = b.sid)) because it returns all rows from 'a' regardless of the criteria 101 Any advice on how would I get only rows from 'a' that match 101 and any in 'b' that match if they exist? -km -- View this message in context: http://www.nabble.com/outer-join-question-tf3178361.html#a8819711 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.411 / Virus Database: 268.17.25/669 - Release Date: 2/4/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: outer join question
Thanks much! ViSolve DB Team-2 wrote: Hello, Try this... select a.rhrqid,a.rhrqsid,a.rhrssid,b.sid,b.rlsid from rqhistory a left join relay b on (a.rhrqid = b.rqid and (a.rhrqsid = b.rlsid or a.rhrqsid = b.sid or a.rhrssid = b.rlsid or a.rhrssid = b.sid)) where a.rhrqsid = 101 or a.rhrssid = 101 Thanks, ViSolve DB Team - Original Message - From: KMiller [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, February 06, 2007 8:37 AM Subject: outer join question This query isn't what I want... select a.rhrqid,a.rhrqsid,a.rhrssid,b.sid,b.rlsid from rqhistory a left join relay b on a.rhrqsid = 101 or a.rhrssid = 101 and (a.rhrqid = b.rqid and (a.rhrqsid = b.rlsid or a.rhrqsid = b.sid or a.rhrssid = b.rlsid or a.rhrssid = b.sid)) because it returns all rows from 'a' regardless of the criteria 101 Any advice on how would I get only rows from 'a' that match 101 and any in 'b' that match if they exist? -km -- View this message in context: http://www.nabble.com/outer-join-question-tf3178361.html#a8819711 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.411 / Virus Database: 268.17.25/669 - Release Date: 2/4/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- View this message in context: http://www.nabble.com/outer-join-question-tf3178361.html#a8821432 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 3 Table Join question
Solved it with Union :) SELECT images.id,images.name, playlist.title FROM images,playlist WHERE playlist.image_id = images.id UNION SELECT images.id,images.name, media.title FROM images,media WHERE media.image_id = images.id ORDER BY id ASC On Jun 23, 2006, at 6:44 PM, Graham Anderson wrote: I am trying to build a query to 1) Get all the results from one table, 'images' 2) For each entry in the 'images' table, find the correct title from the 'playlist' OR 'media' table where images.id = which_table.images_id images table id, filename playlist table title images_id media table title, images_id So the result would something like id filenametitle 1 file1 playlist-title1 // id matches entry in the Playlist table 2 file2 playlist-title2 3 file3 media-title1//id matches entry in the Media table 4 file4 media-title2 any help is appreciated as my queries have been pretty simple up to this point many thanks g -- 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]
3 Table Join question
I am trying to build a query to 1) Get all the results from one table, 'images' 2) For each entry in the 'images' table, find the correct title from the 'playlist' OR 'media' table where images.id = which_table.images_id images table id, filename playlist table title images_id media table title, images_id So the result would something like id filenametitle 1 file1 playlist-title1 // id matches entry in the Playlist table 2 file2 playlist-title2 3 file3 media-title1//id matches entry in the Media table 4 file4 media-title2 any help is appreciated as my queries have been pretty simple up to this point many thanks g -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
join question
Hi All, I have a question for clearer brains than mine. I would like to join two tables,. There may be many possible joins in table B to table A, but I only want to join one row from B to table A - the row with the closest, but lesser date. TABLE A Row Id date 1 46 3 Jan 7 20 10 Jan TABLE B Row Id date 4 46 1 Jan 5 46 2 Jan 6 46 4 Jan 8 20 8 Jan 10 20 7 Jan 11 20 9 jan Result Row 1` in A is joined to row 5 in B Row 7` in A is joined to row 11 in B SELECT a.row, b.row FROM A a LEFT JOIN B b ON (a.id = b.id AND a.date b.date AND the row with the max dates from the possible join in b?) Any suggestions would be appreciated. Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: join question
http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html I'd translate it to your example, but it's bed-time here in England! HTH, James Harvard At 11:42 pm + 5/1/06, Terry Spencer wrote: I have a question for clearer brains than mine. I would like to join two tables,. There may be many possible joins in table B to table A, but I only want to join one row from B to table A - the row with the closest, but lesser date. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic sql join question...
Hi Philip, what yoy called gand total is in @total for evevy row. you can just play : set @total:=0; select name,price,quantity, price*quantity as subtotal,@total:[EMAIL PROTECTED] from fruits; select @total as grand total; that's all ! Mathias Selon Philip George [EMAIL PROTECTED]: On May 29, 2005, at 2:34 PM, [EMAIL PROTECTED] wrote: you can use mysql variables : set @total:=0; select name,price,quantity, price*quantity as subtotal,@total:[EMAIL PROTECTED] from fruits; ++---+--+-- +---+ | name | price | quantity | subtotal | @total:[EMAIL PROTECTED] | ++---+--+-- +---+ | orange | 1 |2 |2 | 2 | | banana | 1 |4 |4 | 6 | ++---+--+-- +---+ The total column will be incremented by subtotal in each row. actually, i need a grand total of the entire ticket: 1 orange x 0.97 = 0.97 + 3 pears x 1.09 = 3.27 _ 4.24 grand total i think you're right that variables can be used to do this, but i can't figure out how to get a grand total for the entire sale. but, i wouldn't scratch your head about it too much. i sort of gave up on the idea in favor of doing the calculation in the client code at runtime after selecting all the pertinent data. thanks though. :) - philip -- 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: basic sql join question...
On May 30, 2005, at 1:28 AM, [EMAIL PROTECTED] wrote: you can just play : set @total:=0; select name,price,quantity, price*quantity as subtotal,@total:[EMAIL PROTECTED] from fruits; select @total as grand total; works great. thanks very, very much. - philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic sql join question...
re-send : Hi, you can use mysql variables : set @total:=0; select name,price,quantity, price*quantity as subtotal,@total:[EMAIL PROTECTED] from fruits; ++---+--+--+---+ | name | price | quantity | subtotal | @total:[EMAIL PROTECTED] | ++---+--+--+---+ | orange | 1 |2 |2 | 2 | | banana | 1 |4 |4 | 6 | ++---+--+--+---+ The total column will be incremented by subtotal in each row. Mathias Selon Philip George [EMAIL PROTECTED]: actually, i've decided this is sort of a moot point, since i can do this calculation in the client app. no sql required. thanks. - philip -- 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: basic sql join question...
On 5/29/05, Philip George [EMAIL PROTECTED] wrote: +--++---+--+ | quantity | name | price | subtotal | +--++---+--+ |1 | orange | 0.97 | 0.97 | |3 | pear | 1.09 | 3.27 | +--++---+--+ how can i also show a grand total for the ticket (without changing the table structure)? i've tried with no success to use SUM() to do this. would i even use SUM()? This has got nothing to do with joins, you can have the same problem in a single table: http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic sql join question...
On May 29, 2005, at 1:41 AM, Jochem van Dieten wrote: http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html already read that. the join in my example is more complicated than anything depicted on that page. please explain. - philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic sql join question...
On 5/29/05, Philip George wrote: On May 29, 2005, at 1:41 AM, Jochem van Dieten wrote: http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html already read that. the join in my example is more complicated than anything depicted on that page. The join is irrelevant. Your join returns a resultset and you can just pretent that resultset is a single table: SELECT field1, field2, field3 FROM (very complicated join) AS simpletable GROUP BY ... WITH ROLLUP Just copy-pate your join into this and fix the fieldnames. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic sql join question...
http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html already read that. the join in my example is more complicated than anything depicted on that page. please explain. actually i should say that there are no examples of SUM() or AVG() -- or any of the other GROUP BY functions -- that are used with a join on that page. that's why i mentioned the join. sorry. i should have been more clear. - philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic sql join question...
The join is irrelevant. Your join returns a resultset and you can just pretent that resultset is a single table: SELECT field1, field2, field3 FROM (very complicated join) AS simpletable GROUP BY ... WITH ROLLUP Just copy-pate your join into this and fix the fieldnames. aaahhh okay, i'm close: mysql selectticket_details.quantity, product.name, product.price, (product.price * ticket_details.quantity) as subtotal from product, ticket_details where ticket_details.ticket = '9f2d7b86-213d-1028-88b7-09e76b61a517' AND ticket_details.product = product.id group by subtotal with rollup ; +--++---+--+ | quantity | name | price | subtotal | +--++---+--+ |1 | orange | 0.97 | 0.97 | |3 | pear | 1.09 | 3.27 | |3 | pear | 1.09 | NULL | +--++---+--+ the NULL is in the wrong column. where is my mistake? thanks, jochem. - philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic sql join question...
On 5/29/05, Philip George wrote: On 5/29/05, Philip George wrote: On 5/29/05, Jochem van Dieten wrote: http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html already read that. the join in my example is more complicated than anything depicted on that page. please explain. actually i should say that there are no examples of SUM() or AVG() -- or any of the other GROUP BY functions -- that are used with a join on that page. You can't always solve your problems by following an example. Sometimes you have to recognize the patterns, apply your own knowledge and extend the examples. The MySQL documentation, with its focus on examples instead of formal definitions, isn't the easiest for that, so I would strongly suggest learning SQL from a source that pays more attention to formal definitions. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic sql join question...
actually, i've decided this is sort of a moot point, since i can do this calculation in the client app. no sql required. thanks. - philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic sql join question...
re-submitted : re-send : Hi, you can use mysql variables : set @total:=0; select name,price,quantity, price*quantity as subtotal,@total:[EMAIL PROTECTED] from fruits; ++---+--+--+---+ | name | price | quantity | subtotal | @total:[EMAIL PROTECTED] | ++---+--+--+---+ | orange | 1 |2 |2 | 2 | | banana | 1 |4 |4 | 6 | ++---+--+--+---+ The total column will be incremented by subtotal in each row. Mathias Selon Philip George [EMAIL PROTECTED]: actually, i've decided this is sort of a moot point, since i can do this calculation in the client app. no sql required. thanks. - philip -- 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: basic sql join question...
On May 29, 2005, at 2:34 PM, [EMAIL PROTECTED] wrote: you can use mysql variables : set @total:=0; select name,price,quantity, price*quantity as subtotal,@total:[EMAIL PROTECTED] from fruits; ++---+--+-- +---+ | name | price | quantity | subtotal | @total:[EMAIL PROTECTED] | ++---+--+-- +---+ | orange | 1 |2 |2 | 2 | | banana | 1 |4 |4 | 6 | ++---+--+-- +---+ The total column will be incremented by subtotal in each row. actually, i need a grand total of the entire ticket: 1 orange x 0.97 = 0.97 + 3 pears x 1.09 = 3.27 _ 4.24 grand total i think you're right that variables can be used to do this, but i can't figure out how to get a grand total for the entire sale. but, i wouldn't scratch your head about it too much. i sort of gave up on the idea in favor of doing the calculation in the client code at runtime after selecting all the pertinent data. thanks though. :) - philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic sql join question...
Hi, you can use mysql variables : set @total:=0; select name,price,quantity, price*quantity as subtotal,@total:[EMAIL PROTECTED] from fruits; ++---+--+--+---+ | name | price | quantity | subtotal | @total:[EMAIL PROTECTED] | ++---+--+--+---+ | orange | 1 |2 |2 | 2 | | banana | 1 |4 |4 | 6 | ++---+--+--+---+ The total column will be incremented by subtotal in each row. Mathias Selon Philip George [EMAIL PROTECTED]: actually, i've decided this is sort of a moot point, since i can do this calculation in the client app. no sql required. thanks. - philip -- 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]
basic sql join question...
is it okay to post a basic sql join question to this list? if not, please point me to a list for these types of questions. thanks. - philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic sql join question...
guess i'll just ask: here are the 2 tables of interest: mysql select * from ticket_details; +-- +--+--+ | ticket | product | quantity | +-- +--+--+ | 9f2d7b86-213d-1028-88b7-09e76b61a517 | 85d0d5bc-213c-1028-88b7-09e76b61a517 |1 | | 9f2d7b86-213d-1028-88b7-09e76b61a517 | ad67557e-213c-1028-88b7-09e76b61a517 |3 | | ec04c91e-2142-1028-88b7-09e76b61a517 | 60e766f8-213c-1028-88b7-09e76b61a517 |7 | | ec04c91e-2142-1028-88b7-09e76b61a517 | a4341a8c-213c-1028-88b7-09e76b61a517 |2 | +-- +--+--+ mysql select * from product; +--++---+ | id | name | price | +--++---+ | 60e766f8-213c-1028-88b7-09e76b61a517 | banana | 1.98 | | 85d0d5bc-213c-1028-88b7-09e76b61a517 | orange | 0.97 | | a4341a8c-213c-1028-88b7-09e76b61a517 | apple | 0.89 | | ad67557e-213c-1028-88b7-09e76b61a517 | pear | 1.09 | +--++---+ here's a functional join that retrieves some specifics from a given ticket #: select ticket_details.quantity, product.name, product.price, (product.price * ticket_details.quantity) as subtotal fromproduct, ticket_details where ticket_details.ticket = '9f2d7b86-213d-1028-88b7-09e76b61a517' AND ticket_details.product = product.id ; +--++---+--+ | quantity | name | price | subtotal | +--++---+--+ |1 | orange | 0.97 | 0.97 | |3 | pear | 1.09 | 3.27 | +--++---+--+ how can i also show a grand total for the ticket (without changing the table structure)? i've tried with no success to use SUM() to do this. would i even use SUM()? if i could refer to the resultant table above in a subsequent select, that would be ideal. is there a way to do that (something like select SUM(@@result.subtotal);)? thanks. - philip On May 28, 2005, at 11:36 PM, Philip George wrote: is it okay to post a basic sql join question to this list? if not, please point me to a list for these types of questions. thanks. - philip -- 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]
table join question
Although I didn't find it in the docs anywhere, I know from experience that you cannot join more than 31 tables in 4.0.21 I was wondering if anybody knew if this limit has changed in version 5 Thx Rob
RE: table join question
I suspect 5.x.x is the same as 4.1.x which is 61 tables - dependent on processor. See an earlier posting of mine on the same subject. What they really need to do is replace the #define with a struct and some 'code' to interpret its contents then you could have any limit you wanted - provided you're prepared to wait whilst it works out the result. Kevin Cowley Product Development Alchemetrics Ltd SMARTER DATA , FASTER Tel: 0118 902 9000 (swithcboard) Tel: 0118 902 9099 (direct) Web: www.alchemetrics.co.uk Email: [EMAIL PROTECTED] -Original Message- From: Rob Brooks [mailto:[EMAIL PROTECTED] Sent: 29 March 2005 15:57 To: 'MySQL list' Subject: table join question Although I didn't find it in the docs anywhere, I know from experience that you cannot join more than 31 tables in 4.0.21 I was wondering if anybody knew if this limit has changed in version 5 Thx Rob ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Yet another LEFT JOIN question
Try something like this SELECT A1.ID, SUM(IF(ISNULL(C.AdID),0,1)) AS Clicks, SUM(IF(ISNULL(V.AdID),0,1)) AS Views FROM Ads A1 LEFT JOIN Clicks C ON A1.ID = C.AdID LEFT JOIN Views V ON A1.ID = V.AdID GROUP BY A1.ID -Original Message- From: Ron Gilbert [mailto:[EMAIL PROTECTED] Sent: Saturday, December 04, 2004 2:09 PM To: [EMAIL PROTECTED] [EMAIL PROTECTED] Subject: Yet another LEFT JOIN question I have three tables, 'Ads' is a list of ads, 'Clicks' is a simple list of every time a Ad was clicked on with the Ads ID, and 'Views' is a simple list of views that ad got, with the Ads ID. I am trying to SELECT a list of all the ads, with a count for clicks and a count for views, but my LEFT JOIN is not producing what I thought. If the click count is 0, then the view count is OK, but if not, then the Click count and view count are equal, but a much too large number. If I just SELECT for views or clicks, then it works OK, it's when they are combined that it falls apart. SELECT A1.ID, Count(C.AdID) AS Clicks, Count(V.AdID) AS Views FROM Ads A1 LEFT JOIN Clicks C ON A1.ID = C.AdID LEFT JOIN Views V ON A1.ID = V.AdID group by A1.ID CREATE TABLE `Clicks` ( `AdID` int(10) NOT NULL default '0' [snip] ) CREATE TABLE `Views` ( `AdID` int(10) NOT NULL default '0' [snip] ) CREATE TABLE `Ads` ( `ID` int(10) NOT NULL default '0' [snip] ) I have tried a lot of combinations for LEFT JOIN with no luck. I've read all the posts on this list and they don't seem to be doing what I am, or else I'm not seeing it. Thanks, Ron -- 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]
Yet another LEFT JOIN question
Ron, What's happening is that, when there are clicks and views for an ad, you are getting the number of clicks TIMES the number of views. A quick and dirty solution is to put a column, say id, in clicks which is different for each click, and similarly for views. Then, you can change your counts to count(distinct clicks.id) and count(distinct views.id). Note that, internally, MySQL will still find all of the (click, view) pairs, then sort them and remove duplicates--this may or may not be a problem, depending on usage. If you are using 4.1 or later, you could do a subquery to count the clicks, then left join that with the views. HTH Bill From: Ron Gilbert [EMAIL PROTECTED] Subject: Yet another LEFT JOIN question Date: Sat, 4 Dec 2004 12:08:43 -0800 I have three tables, 'Ads' is a list of ads, 'Clicks' is a simple list of every time a Ad was clicked on with the Ads ID, and 'Views' is a simple list of views that ad got, with the Ads ID. I am trying to SELECT a list of all the ads, with a count for clicks and a count for views, but my LEFT JOIN is not producing what I thought. If the click count is 0, then the view count is OK, but if not, then the Click count and view count are equal, but a much too large number. If I just SELECT for views or clicks, then it works OK, it's when they are combined that it falls apart. SELECT A1.ID, Count(C.AdID) AS Clicks, Count(V.AdID) AS Views FROM Ads A1 LEFT JOIN Clicks C ON A1.ID = C.AdID LEFT JOIN Views V ON A1.ID = V.AdID group by A1.ID CREATE TABLE `Clicks` ( `AdID` int(10) NOT NULL default '0' [snip] ) CREATE TABLE `Views` ( `AdID` int(10) NOT NULL default '0' [snip] ) CREATE TABLE `Ads` ( `ID` int(10) NOT NULL default '0' [snip] ) I have tried a lot of combinations for LEFT JOIN with no luck. I've read all the posts on this list and they don't seem to be doing what I am, or else I'm not seeing it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Yet another LEFT JOIN question
If you are using 4.1 or later, you could do a subquery to count the clicks, then left join that with the views. I am using 4.1. I tried to do a sub-query, but never got it run. Can you give me a quick example? Is the sub-query a better (faster) way to do this? Ron On Dec 6, 2004, at 6:19 AM, Bill Easton wrote: Ron, What's happening is that, when there are clicks and views for an ad, you are getting the number of clicks TIMES the number of views. A quick and dirty solution is to put a column, say id, in clicks which is different for each click, and similarly for views. Then, you can change your counts to count(distinct clicks.id) and count(distinct views.id). Note that, internally, MySQL will still find all of the (click, view) pairs, then sort them and remove duplicates--this may or may not be a problem, depending on usage. If you are using 4.1 or later, you could do a subquery to count the clicks, then left join that with the views. HTH Bill From: Ron Gilbert [EMAIL PROTECTED] Subject: Yet another LEFT JOIN question Date: Sat, 4 Dec 2004 12:08:43 -0800 I have three tables, 'Ads' is a list of ads, 'Clicks' is a simple list of every time a Ad was clicked on with the Ads ID, and 'Views' is a simple list of views that ad got, with the Ads ID. I am trying to SELECT a list of all the ads, with a count for clicks and a count for views, but my LEFT JOIN is not producing what I thought. If the click count is 0, then the view count is OK, but if not, then the Click count and view count are equal, but a much too large number. If I just SELECT for views or clicks, then it works OK, it's when they are combined that it falls apart. SELECT A1.ID, Count(C.AdID) AS Clicks, Count(V.AdID) AS Views FROM Ads A1 LEFT JOIN Clicks C ON A1.ID = C.AdID LEFT JOIN Views V ON A1.ID = V.AdID group by A1.ID CREATE TABLE `Clicks` ( `AdID` int(10) NOT NULL default '0' [snip] ) CREATE TABLE `Views` ( `AdID` int(10) NOT NULL default '0' [snip] ) CREATE TABLE `Ads` ( `ID` int(10) NOT NULL default '0' [snip] ) I have tried a lot of combinations for LEFT JOIN with no luck. I've read all the posts on this list and they don't seem to be doing what I am, or else I'm not seeing it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Yet another LEFT JOIN question
Try: select id, clicks, count(views.adId) as views from (select ads.id, count(clicks.adId) as clicks from ads inner join clicks on ads.id=clicks.adId group by id) as adsclicks left join views on id=views.adid group by id; Explanation: -- the following gives you a count of clicks for each ad select ads.id, count(clicks.adId) as clicks from ads inner join clicks on ads.id=clicks.adId group by id) -- if you save it to a temporary table, create temporary table adsclicks select ads.id, count(clicks.adId) as clicks from ads inner join clicks on ads.id=clicks.adId group by id) -- you then have a temporary table with a row for each ad and the click counts -- you can then left join that with the views table to get the views count, too. select id, clicks, count(views.adId) as views from adsclicks left join views on id=views.adid group by id; -- the query at the beginning of this message uses a subquery instead of creating and using a temporary table. Is the subquery better or faster? Try it and see--depends partly on whether you have to add a column to identify individual clicks and views. On the one hand, the count(distinct) solution looks at more rows; on the other hand, subqueries may not get as much optimization. I'd claim that the subquery describes better what you want, while the count(distinct) is a kludge to avoid the subquery. - Original Message - From: Ron Gilbert [EMAIL PROTECTED] To: Bill Easton [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, December 06, 2004 11:05 AM Subject: Re: Yet another LEFT JOIN question If you are using 4.1 or later, you could do a subquery to count the clicks, then left join that with the views. I am using 4.1. I tried to do a sub-query, but never got it run. Can you give me a quick example? Is the sub-query a better (faster) way to do this? Ron On Dec 6, 2004, at 6:19 AM, Bill Easton wrote: Ron, What's happening is that, when there are clicks and views for an ad, you are getting the number of clicks TIMES the number of views. A quick and dirty solution is to put a column, say id, in clicks which is different for each click, and similarly for views. Then, you can change your counts to count(distinct clicks.id) and count(distinct views.id). Note that, internally, MySQL will still find all of the (click, view) pairs, then sort them and remove duplicates--this may or may not be a problem, depending on usage. If you are using 4.1 or later, you could do a subquery to count the clicks, then left join that with the views. HTH Bill From: Ron Gilbert [EMAIL PROTECTED] Subject: Yet another LEFT JOIN question Date: Sat, 4 Dec 2004 12:08:43 -0800 I have three tables, 'Ads' is a list of ads, 'Clicks' is a simple list of every time a Ad was clicked on with the Ads ID, and 'Views' is a simple list of views that ad got, with the Ads ID. I am trying to SELECT a list of all the ads, with a count for clicks and a count for views, but my LEFT JOIN is not producing what I thought. If the click count is 0, then the view count is OK, but if not, then the Click count and view count are equal, but a much too large number. If I just SELECT for views or clicks, then it works OK, it's when they are combined that it falls apart. SELECT A1.ID, Count(C.AdID) AS Clicks, Count(V.AdID) AS Views FROM Ads A1 LEFT JOIN Clicks C ON A1.ID = C.AdID LEFT JOIN Views V ON A1.ID = V.AdID group by A1.ID CREATE TABLE `Clicks` ( `AdID` int(10) NOT NULL default '0' [snip] ) CREATE TABLE `Views` ( `AdID` int(10) NOT NULL default '0' [snip] ) CREATE TABLE `Ads` ( `ID` int(10) NOT NULL default '0' [snip] ) I have tried a lot of combinations for LEFT JOIN with no luck. I've read all the posts on this list and they don't seem to be doing what I am, or else I'm not seeing it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Yet another LEFT JOIN question
I have three tables, 'Ads' is a list of ads, 'Clicks' is a simple list of every time a Ad was clicked on with the Ads ID, and 'Views' is a simple list of views that ad got, with the Ads ID. I am trying to SELECT a list of all the ads, with a count for clicks and a count for views, but my LEFT JOIN is not producing what I thought. If the click count is 0, then the view count is OK, but if not, then the Click count and view count are equal, but a much too large number. If I just SELECT for views or clicks, then it works OK, it's when they are combined that it falls apart. SELECT A1.ID, Count(C.AdID) AS Clicks, Count(V.AdID) AS Views FROM Ads A1 LEFT JOIN Clicks C ON A1.ID = C.AdID LEFT JOIN Views V ON A1.ID = V.AdID group by A1.ID CREATE TABLE `Clicks` ( `AdID` int(10) NOT NULL default '0' [snip] ) CREATE TABLE `Views` ( `AdID` int(10) NOT NULL default '0' [snip] ) CREATE TABLE `Ads` ( `ID` int(10) NOT NULL default '0' [snip] ) I have tried a lot of combinations for LEFT JOIN with no luck. I've read all the posts on this list and they don't seem to be doing what I am, or else I'm not seeing it. Thanks, Ron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
left join question
Is it possible to do two left joins involving three tables in one query? select a.id, a.amount FROM t1 LEFT JOIN t2 ON (t1.id=t2.id) then LEFT JOIN t3 ON (t1.id=t3.id) Is this even possible? Any help would be greatly appreciated. Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: left join question
[snip] Is it possible to do two left joins involving three tables in one query? select a.id, a.amount FROM t1 LEFT JOIN t2 ON (t1.id=t2.id) then LEFT JOIN t3 ON (t1.id=t3.id) Is this even possible? [/snip] Yes, and the keys from table to table don't have to be the same, save for each JOIN.. SELECT a.id, a.amount, b.invoiceID FROM t1 a LEFT JOIN t2 b ON(a.id = b.id) LEFT JOIN t3 c ON(b.invoiceID = c.invoiceID) ---look Ma, different relation!) WHERE c.invoiceID IS NULL (c is empty for this query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: left join question
- Original Message - From: Richard Reina [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 14, 2004 8:26 AM Subject: left join question Is it possible to do two left joins involving three tables in one query? select a.id, a.amount FROM t1 LEFT JOIN t2 ON (t1.id=t2.id) then LEFT JOIN t3 ON (t1.id=t3.id) Is this even possible? Any help would be greatly appreciated. That's not a problem. For example: select t1.cola, t2.colb, t3, colc from table1 t1 left join table2 t2 on t1.col1 = t2.col1 left join table3 t3 on t2.col4 = t3.col8 where t1.col5 = 'M' order by t2.colb; Note that the second line of the join - left join table3 t3 on t2.col4 = t3.col8 - has nothing preceding the keywords 'left join' on that same line; the 'on' clause is what connects table3 to one of the other tables in the join. table3 could be joined to either table1 or table2 via the 'on' clause. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update/join question..
hi... a question on how to do an update on a table that depends on 'left/right' joins with other tables... i have the following select that works. select u1.urltype as type, p1.fileID as fileID, l1.process as process, l1.status as status from university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID =p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40'; i simply wnat to be able to update the latestParseStatusTBL based upon the joins between the tables... (something like...) update latestParseStatusTBL, university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID =p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40' set l1.process = '1', l1.status = '13'; i've tried a number of derivatives of this approach with no luck.. i'm missing something simple.. searching through mysql.com/google hasn't shed much light!! any ideas/comments/asssistance/thoughts/etc.. thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update/join question..
- Original Message - From: bruce [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 11:45 AM Subject: update/join question.. hi... a question on how to do an update on a table that depends on 'left/right' joins with other tables... i have the following select that works. select u1.urltype as type, p1.fileID as fileID, l1.process as process, l1.status as status from university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID =p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40'; i simply wnat to be able to update the latestParseStatusTBL based upon the joins between the tables... (something like...) update latestParseStatusTBL, university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID =p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40' set l1.process = '1', l1.status = '13'; i've tried a number of derivatives of this approach with no luck.. i'm missing something simple.. You haven't explained what you meant by with no luck; did the updates fail with an error message or without a message? If there was a message, what did it say? I'm guessing that they failed with an error message but that the message was cryptic I'm not overly fluent with MySQL yet but in 20 years of using DB2, I've always been told that you can't update a join, you can only update an individual table. I assume that is true of all relational databases, including MySQL, but I don't know that for an absolute fact. searching through mysql.com/google hasn't shed much light!! Perhaps because this is such a fundamental concept that no one thought it worth putting in a manual; they just assumed it would be told to you wherever you learned basic relational concepts. If so, that is assuming that everyone working with relational databases had some exposure to theory first; I think that's a very dubious assumption. any ideas/comments/asssistance/thoughts/etc.. There ought to be a clear error message every time you try to update, insert, or delete from a join that says these operations cannot be done on joins. The manuals for every relational database should state that Insert, Update, and Delete do not work on joins, both in the reference section for the Insert, Update, and Delete statements and in the concepts section. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update/join question..
Rhino, I hate to burst your bubble but JOINS __are allowed__ in MySQL updates. I use them all the time. http://dev.mysql.com/doc/mysql/en/UPDATE.html At the top of the page are the syntaxes for single-table and multiple-table updates: The key here is that whatever you put _between_ the words FROM and WHERE in a normal query will be what you need to UPDATE. (I personally discourage anyone from using the comma join method of declaring table joins so I won't use it in my examples. It's a valid syntax but if you forget to put the right comparisons into your WHERE clause, you end up with problems. It's even easier with using JOINS in an UPDATE as the WHERE clause is even farther away from the tables) This query will give me a list of all of the companies who have contracts managed by manager 15: SELECT DISTINCT t1.* FROM Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_id WHERE t2.Manager_ID = 15 Imagine a situation where you needed to update contract.ManagerID with a new manager (Manager 15 was promoted) but you are not reassigning ALL of the contract accounts to the same person. You want to split it up so that companies A-L go to manger 12 and companies M-Z to manager 34. You *CAN* do this in a single statement but that would confuse my example. I will do the reassignments in two statements so that you can see the pattern better: UPDATE Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_ID SET t2.Manager_Id = 12 WHERE t2.Manager_ID = 15 AND t1.Name 'm'; UPDATE Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_ID SET t2.Manager_Id = 34 WHERE t2.Manager_ID = 15 AND t1.Name = 'm'; The WHERE clause didn't need to change but what came after the FROM in a query has been moved to right after the UPDATE. The SET clause can assign values to or get values from ANY column from any of the tables participating in the UPDATE clause. Let me get back on topic... Bruce, you started with this SELECT: SELECT u1.urltype as type, p1.fileID as fileID, l1.process as process, l1.status as status FROM university_urlTBL as u1 RIGHT JOIN parsefileTBL as p1 on u1.ID =p1.university_urlID INNER JOIN latestParseStatusTBL as l1 on p1.fileID = l1.itemID WHERE u1.universityID='40'; You should be able to transform this query into: UPDATE university_urlTBL as u1 RIGHT JOIN parsefileTBL as p1 on u1.ID =p1.university_urlID INNER JOIN latestParseStatusTBL as l1 on p1.fileID = l1.itemID SET WHERE ... Your SET and WHERE clauses can refer to any column in any of the three tables participating in the JOINs. Guessing from your example they would look like SET l1.process = '1', l1.status = '13' WHERE u1.universityID = '40' BEGIN RANT If process, status, and universityID are numeric fields... DROP THOSE QUOTES. They are not necessary and require the engine to take an extra conversion. END RANT sorry... just had to get that off my chest. :-D Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on 09/14/2004 01:24:19 PM: - Original Message - From: bruce [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 11:45 AM Subject: update/join question.. hi... a question on how to do an update on a table that depends on 'left/right' joins with other tables... i have the following select that works. select u1.urltype as type, p1.fileID as fileID, l1.process as process, l1.status as status from university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID =p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40'; i simply wnat to be able to update the latestParseStatusTBL based upon the joins between the tables... (something like...) update latestParseStatusTBL, university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID =p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40' set l1.process = '1', l1.status = '13'; i've tried a number of derivatives of this approach with no luck.. i'm missing something simple.. You haven't explained what you meant by with no luck; did the updates fail with an error message or without a message? If there was a message, what did it say? I'm guessing that they failed with an error message but that the message was cryptic I'm not overly fluent with MySQL yet but in 20 years of using DB2, I've always been told that you can't update a join, you can only update an individual table. I assume that is true of all relational databases, including MySQL, but I don't know that for an absolute fact. searching through mysql.com/google hasn't shed much light!! Perhaps because this is such a fundamental concept that no one thought it worth putting in a manual; they just assumed it would be told
Re: update/join question..
Rhino, I hate to burst your bubble but JOINS __are allowed__ in MySQL updates. I use them all the time. http://dev.mysql.com/doc/mysql/en/UPDATE.html At the top of the page are the syntaxes for single-table and multiple-table updates: The key here is that whatever you put _between_ the words FROM and WHERE in a normal query will be what you need to UPDATE. (I personally discourage anyone from using the comma join method of declaring table joins so I won't use it in my examples. It's a valid syntax but if you forget to put the right comparisons into your WHERE clause, you end up with problems. It's even easier with using JOINS in an UPDATE as the WHERE clause is even farther away from the tables) This query will give me a list of all of the companies who have contracts managed by manager 15: SELECT DISTINCT t1.* FROM Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_id WHERE t2.Manager_ID = 15 Imagine a situation where you needed to update contract.ManagerID with a new manager (Manager 15 was promoted) but you are not reassigning ALL of the contract accounts to the same person. You want to split it up so that companies A-L go to manger 12 and companies M-Z to manager 34. You *CAN* do this in a single statement but that would confuse my example. I will do the reassignments in two statements so that you can see the pattern better: UPDATE Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_ID SET t2.Manager_Id = 12 WHERE t2.Manager_ID = 15 AND t1.Name 'm'; UPDATE Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_ID SET t2.Manager_Id = 34 WHERE t2.Manager_ID = 15 AND t1.Name = 'm'; The WHERE clause didn't need to change but what came after the FROM in a query has been moved to right after the UPDATE. The SET clause can assign values to or get values from ANY column from any of the tables participating in the UPDATE clause. Let me get back on topic... Bruce, you started with this SELECT: SELECT u1.urltype as type, p1.fileID as fileID, l1.process as process, l1.status as status FROM university_urlTBL as u1 RIGHT JOIN parsefileTBL as p1 on u1.ID =p1.university_urlID INNER JOIN latestParseStatusTBL as l1 on p1.fileID = l1.itemID WHERE u1.universityID='40'; You should be able to transform this query into: UPDATE university_urlTBL as u1 RIGHT JOIN parsefileTBL as p1 on u1.ID =p1.university_urlID INNER JOIN latestParseStatusTBL as l1 on p1.fileID = l1.itemID SET WHERE ... Your SET and WHERE clauses can refer to any column in any of the three tables participating in the JOINs. Guessing from your example they would look like SET l1.process = '1', l1.status = '13' WHERE u1.universityID = '40' BEGIN RANT If process, status, and universityID are numeric fields... DROP THOSE QUOTES. They are not necessary and require the engine to take an extra conversion. END RANT sorry... just had to get that off my chest. :-D Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on 09/14/2004 01:24:19 PM: - Original Message - From: bruce [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 11:45 AM Subject: update/join question.. hi... a question on how to do an update on a table that depends on 'left/right' joins with other tables... i have the following select that works. select u1.urltype as type, p1.fileID as fileID, l1.process as process, l1.status as status from university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID =p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40'; i simply wnat to be able to update the latestParseStatusTBL based upon the joins between the tables... (something like...) update latestParseStatusTBL, university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID =p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40' set l1.process = '1', l1.status = '13'; i've tried a number of derivatives of this approach with no luck.. i'm missing something simple.. You haven't explained what you meant by with no luck; did the updates fail with an error message or without a message? If there was a message, what did it say? I'm guessing that they failed with an error message but that the message was cryptic I'm not overly fluent with MySQL yet but in 20 years of using DB2, I've always been told that you can't update a join, you can only update an individual table. I assume that is true of all relational databases, including MySQL, but I don't know that for an absolute fact. searching through mysql.com/google hasn't shed much light!! Perhaps because this is such a fundamental concept that no one thought it worth putting in a manual; they just assumed it would be told
Re: update/join question..
pray, that you use mysql version =4 in version 3 it doesnt work... Oliver On Tue, 14 Sep 2004 14:09:43 -0400, [EMAIL PROTECTED] wrote: Rhino, I hate to burst your bubble but JOINS __are allowed__ in MySQL updates. I use them all the time. http://dev.mysql.com/doc/mysql/en/UPDATE.html At the top of the page are the syntaxes for single-table and multiple-table updates: The key here is that whatever you put _between_ the words FROM and WHERE in a normal query will be what you need to UPDATE. (I personally discourage anyone from using the comma join method of declaring table joins so I won't use it in my examples. It's a valid syntax but if you forget to put the right comparisons into your WHERE clause, you end up with problems. It's even easier with using JOINS in an UPDATE as the WHERE clause is even farther away from the tables) This query will give me a list of all of the companies who have contracts managed by manager 15: SELECT DISTINCT t1.* FROM Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_id WHERE t2.Manager_ID = 15 Imagine a situation where you needed to update contract.ManagerID with a new manager (Manager 15 was promoted) but you are not reassigning ALL of the contract accounts to the same person. You want to split it up so that companies A-L go to manger 12 and companies M-Z to manager 34. You *CAN* do this in a single statement but that would confuse my example. I will do the reassignments in two statements so that you can see the pattern better: UPDATE Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_ID SET t2.Manager_Id = 12 WHERE t2.Manager_ID = 15 AND t1.Name 'm'; UPDATE Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_ID SET t2.Manager_Id = 34 WHERE t2.Manager_ID = 15 AND t1.Name = 'm'; The WHERE clause didn't need to change but what came after the FROM in a query has been moved to right after the UPDATE. The SET clause can assign values to or get values from ANY column from any of the tables participating in the UPDATE clause. Let me get back on topic... Bruce, you started with this SELECT: SELECT u1.urltype as type, p1.fileID as fileID, l1.process as process, l1.status as status FROM university_urlTBL as u1 RIGHT JOIN parsefileTBL as p1 on u1.ID =p1.university_urlID INNER JOIN latestParseStatusTBL as l1 on p1.fileID = l1.itemID WHERE u1.universityID='40'; You should be able to transform this query into: UPDATE university_urlTBL as u1 RIGHT JOIN parsefileTBL as p1 on u1.ID =p1.university_urlID INNER JOIN latestParseStatusTBL as l1 on p1.fileID = l1.itemID SET WHERE ... Your SET and WHERE clauses can refer to any column in any of the three tables participating in the JOINs. Guessing from your example they would look like SET l1.process = '1', l1.status = '13' WHERE u1.universityID = '40' BEGIN RANT If process, status, and universityID are numeric fields... DROP THOSE QUOTES. They are not necessary and require the engine to take an extra conversion. END RANT sorry... just had to get that off my chest. :-D Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on 09/14/2004 01:24:19 PM: - Original Message - From: bruce [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 11:45 AM Subject: update/join question.. hi... a question on how to do an update on a table that depends on 'left/right' joins with other tables... i have the following select that works. select u1.urltype as type, p1.fileID as fileID, l1.process as process, l1.status as status from university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID =p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40'; i simply wnat to be able to update the latestParseStatusTBL based upon the joins between the tables... (something like...) update latestParseStatusTBL, university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID =p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40' set l1.process = '1', l1.status = '13'; i've tried a number of derivatives of this approach with no luck.. i'm missing something simple.. You haven't explained what you meant by with no luck; did the updates fail with an error message or without a message? If there was a message, what did it say? I'm guessing that they failed with an error message but that the message was cryptic I'm not overly fluent with MySQL yet but in 20 years of using DB2, I've always been told that you can't update a join, you can only update an individual table. I assume that is true of all relational databases, including MySQL, but I don't know that for an absolute fact. searching through mysql.com/google hasn't shed much light!! Perhaps because this is such a fundamental concept that no one thought it worth putting in a manual; they just
Fw: update/join question..
Sorry, I meant to send this reply to the whole mailing list, not just to Shawn. Rhino - Original Message - From: Rhino [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 2:18 PM Subject: Re: update/join question.. Okay, I stand corrected. Apparently, MySQL allows updates of joins. My apologies, I didn't mean to steer anyone down the wrong path. Thanks for setting me straight, Shawn! Rhino - Original Message - From: [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 2:07 PM Subject: Re: update/join question.. Rhino, I hate to burst your bubble but JOINS __are allowed__ in MySQL updates. I use them all the time. http://dev.mysql.com/doc/mysql/en/UPDATE.html At the top of the page are the syntaxes for single-table and multiple-table updates: The key here is that whatever you put _between_ the words FROM and WHERE in a normal query will be what you need to UPDATE. (I personally discourage anyone from using the comma join method of declaring table joins so I won't use it in my examples. It's a valid syntax but if you forget to put the right comparisons into your WHERE clause, you end up with problems. It's even easier with using JOINS in an UPDATE as the WHERE clause is even farther away from the tables) This query will give me a list of all of the companies who have contracts managed by manager 15: SELECT DISTINCT t1.* FROM Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_id WHERE t2.Manager_ID = 15 Imagine a situation where you needed to update contract.ManagerID with a new manager (Manager 15 was promoted) but you are not reassigning ALL of the contract accounts to the same person. You want to split it up so that companies A-L go to manger 12 and companies M-Z to manager 34. You *CAN* do this in a single statement but that would confuse my example. I will do the reassignments in two statements so that you can see the pattern better: UPDATE Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_ID SET t2.Manager_Id = 12 WHERE t2.Manager_ID = 15 AND t1.Name 'm'; UPDATE Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_ID SET t2.Manager_Id = 34 WHERE t2.Manager_ID = 15 AND t1.Name = 'm'; The WHERE clause didn't need to change but what came after the FROM in a query has been moved to right after the UPDATE. The SET clause can assign values to or get values from ANY column from any of the tables participating in the UPDATE clause. Let me get back on topic... Bruce, you started with this SELECT: SELECT u1.urltype as type, p1.fileID as fileID, l1.process as process, l1.status as status FROM university_urlTBL as u1 RIGHT JOIN parsefileTBL as p1 on u1.ID =p1.university_urlID INNER JOIN latestParseStatusTBL as l1 on p1.fileID = l1.itemID WHERE u1.universityID='40'; You should be able to transform this query into: UPDATE university_urlTBL as u1 RIGHT JOIN parsefileTBL as p1 on u1.ID =p1.university_urlID INNER JOIN latestParseStatusTBL as l1 on p1.fileID = l1.itemID SET WHERE ... Your SET and WHERE clauses can refer to any column in any of the three tables participating in the JOINs. Guessing from your example they would look like SET l1.process = '1', l1.status = '13' WHERE u1.universityID = '40' BEGIN RANT If process, status, and universityID are numeric fields... DROP THOSE QUOTES. They are not necessary and require the engine to take an extra conversion. END RANT sorry... just had to get that off my chest. :-D Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on 09/14/2004 01:24:19 PM: - Original Message - From: bruce [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 11:45 AM Subject: update/join question.. hi... a question on how to do an update on a table that depends on 'left/right' joins with other tables... i have the following select that works. select u1.urltype as type, p1.fileID as fileID, l1.process as process, l1.status as status from university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID =p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40'; i simply wnat to be able to update the latestParseStatusTBL based upon the joins between the tables... (something like...) update latestParseStatusTBL, university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID =p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40' set l1
another left join question - multiple left join statements
my test tbls cattbl dogtbl birdtbl namename name id --- catid --- dogid id id so dogtbl.catid = cattbl.id birdtbl.dogid = dogtbl.id my question is how i can use left joins to produce the results set with the names of all three cat/dog/bird... i've tried various derivatives of the following... mysql select cattbl.name as cat, - dogtbl.name as dog, birdtbl.name as bird - from dogtbl - left join cattbl on cattbl.id=dogtbl.catid - from birdtbl - left join dogtbl on birdtbl.dogid=dogtbl.id; i keep getting an error complaining about the 2nd from/left join... i know how to get the results using where/and logic... but i'm trying to get a better feel of the left join process... after looking at mysql/google, i'm still missing something... any comments/criticisms appreciated.. thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: another left join question - multiple left join statements
On Monday 05 July 2004 12:28 pm, bruce wrote: my test tbls cattbl dogtbl birdtbl namename name id --- catid --- dogid id id so dogtbl.catid = cattbl.id birdtbl.dogid = dogtbl.id my question is how i can use left joins to produce the results set with the names of all three cat/dog/bird... i've tried various derivatives of the following... mysql select cattbl.name as cat, - dogtbl.name as dog, birdtbl.name as bird - from dogtbl - left join cattbl on cattbl.id=dogtbl.catid - from birdtbl - left join dogtbl on birdtbl.dogid=dogtbl.id; i keep getting an error complaining about the 2nd from/left join... ... -bruce It's not really clear from the manual, but if you check the basic syntax of the SELECT statement: http://dev.mysql.com/doc/mysql/en/SELECT.html and the JOIN: http://dev.mysql.com/doc/mysql/en/JOIN.html you'll see that all the tables references (the FROM and the JOINS) go in a single spot in the SELECT statement: SELECT column list FROM table references WHERE conditions. So there should only be a single FROM followed first by the tables in the basic select (including any inner join) and then by any LEFT or RIGHT JOINs you wish to add. Generally, you only reference each table one time. select cattbl.name, dogtbl.name, birdtbl.name from dogtbl left join cattbl on cattbl.id=dogtbl.catid this is wrong (see comments above) - from birdtbl left join anothertablenametoaddtoyourquery on birdtbl.dogid=dogtbl.id; Good luck, Joihn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: another left join question - multiple left join statements
You only need to specify from tabel on the first table. Like this. select cattbl.name as cat, dogtbl.name as dog, birdtbl.name as bird from dogtbl left join cattbl on cattbl.id=dogtbl.catid left join birdtbl on birdtbl.dogid=dogtbl.id; -Eric On Mon, 5 Jul 2004 09:28:02 -0700, bruce [EMAIL PROTECTED] wrote: my test tbls cattbl dogtbl birdtbl namename name id --- catid --- dogid id id so dogtbl.catid = cattbl.id birdtbl.dogid = dogtbl.id my question is how i can use left joins to produce the results set with the names of all three cat/dog/bird... i've tried various derivatives of the following... mysql select cattbl.name as cat, - dogtbl.name as dog, birdtbl.name as bird - from dogtbl - left join cattbl on cattbl.id=dogtbl.catid - from birdtbl - left join dogtbl on birdtbl.dogid=dogtbl.id; i keep getting an error complaining about the 2nd from/left join... i know how to get the results using where/and logic... but i'm trying to get a better feel of the left join process... after looking at mysql/google, i'm still missing something... any comments/criticisms appreciated.. thanks -bruce -- 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]
Join question
Running 4.0.18 I am trying to run a query where the query gets the offer_ID of a certain customer from the offer table and displays in the results the offer_Name associated with the offer_ID. Right now the way the query is working it displays all the offers in the offer table regardless of the offer_ID assigned to the customer. Any thoughts? SELECT c.cst_SiteID, c.cst_IDC, a.asset_ID, o.offer_ID, o.offer_Name FROM customers c, assets a , offers o WHERE c.cst_SiteID = a.asset_SiteID AND cst_Name LIKE 'z%' Results: | cst_SiteID | cst_IDC | asset_ID | offer_ID | offer_Name | ++-+--+--++ | 6916 | 2 |18165 |3 | Basic Monitoring | | 6916 | 2 |18165 |8 | Unknown| | 6916 | 2 |18165 |1 | Advanced Managed | | 6916 | 2 |18165 |5 | Mixed Managed | | 6916 | 2 |18165 |6 | No Monitoring | | 6916 | 2 |18165 |2 | Advanced Monitoring| | 6916 | 2 |18165 |4 | Internally Managed | | 6916 | 2 |18165 |7 | Performance Monitoring | ++-+--+--++ customers Table +++--+-+ -+-+---+ | cst_ID | cst_SiteID | cst_Name | cst_IDC | cst_MgtType | cst_POC | cst_Offer | +++--+-+ -+-+---+ | 2418 | 897 | JTE (H.K.) Limited | 9 | 5 | 0 | 6 | | 2417 | 799 | Zape Corporation | 7 | 5 | 0 | 6 | | 2416 | 728 | Zone , Inc. | 9 | 5 | 0 | 6 | | 2415 | 702 | ZL Batavia, LLC | 16 | 1 | 0 | 1 | +++--+-+ -+-+---+ offers Table +--++--- --+ | offer_ID | offer_Name | offer_Search | +--++--- --+ |1 | Advanced Managed | Advanced Managed | |2 | Advanced Monitoring| Advanced Monitoring | |4 | Internally Managed | Internally Managed | |3 | Basic Monitoring | Basic Monitoring | |5 | Mixed Managed | Mixed Managed | |6 | No Monitoring | No Monitoring | |7 | Performance Monitoring | Performance Monitoring, Performance Managed | |8 | Unknown| Unknown | +--++--- --+ Sincerely, Chris Dietzler ATT Enhanced Network Services 858 812 4062 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join question
On Thu, 3 Jun 2004 13:06:54 -0700 Chris Dietzler [EMAIL PROTECTED] wrote: Running 4.0.18 I am trying to run a query where the query gets the offer_ID of a certain customer from the offer table and displays in the results the offer_Name associated with the offer_ID. Right now the way the query is working it displays all the offers in the offer table regardless of the offer_ID assigned to the customer. Any thoughts? SELECT c.cst_SiteID, c.cst_IDC, a.asset_ID, o.offer_ID, o.offer_Name FROM customers c, assets a , offers o WHERE c.cst_SiteID = a.asset_SiteID AND cst_Name LIKE 'z%' snip Global rule for joins - if you have N tables (or table aliases) involed a JOIN, you need N-1 JOIN conditions in your WHERE clause, or using INNER JOIN clauses. In your query cst_Name LIKE 'z%' is NOT a join condition, it's just a filter. Nothing actually joins your offers table in the above query. Try: SELECT c.cst_SiteID, c.cst_IDC, a.asset_ID, o.offer_ID, o.offer_Name FROM customers c, assets a , offers o WHERE c.cst_SiteID = a.asset_SiteID AND c.cst_Offer = o.offer_ID AND cst_Name LIKE 'z%'; Or in preferable INNER JOIN syntax which makes it easier to forget a JOIN condition: SELECT c.cst_SiteID, c.cst_IDC, a.asset_ID, o.offer_ID, o.offer_Name FROM customers c INNER JOIN assets a ON c.cst_SiteID = a.asset_SiteID INNER JOIN offers o ON c.cst_Offer = o.offer_ID WHERE cst_Name LIKE 'z%'; Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join question
On Thu, 3 Jun 2004 15:22:36 -0500 Josh Trutwin [EMAIL PROTECTED] wrote: Or in preferable INNER JOIN syntax which makes it easier to forget a JOIN condition: Oops - I meant harder to forget not easier to forget. Doh. SELECT c.cst_SiteID, c.cst_IDC, a.asset_ID, o.offer_ID, o.offer_Name FROM customers c INNER JOIN assets a ON c.cst_SiteID = a.asset_SiteID INNER JOIN offers o ON c.cst_Offer = o.offer_ID WHERE cst_Name LIKE 'z%'; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Join Question
Hi - I'm running 4.0.18, so I can't use subqueries. I need to run a query to get the sum of costs from multiple tables for a given master table. What I have right now which is clearly not going to work is: SELECT conveyor.serial, SUM(conveyorsupport.cost), SUM(conveyoraccessories.cost) from (conveyor LEFT JOIN conveyorsupport on conveyor.serial = conveyorsupport.serial) LEFT JOIN conveyoraccessories on conveyor.serial = conveyoraccessories.serial This will return a product for the sum of conveyor accessories (however many supports there are times the sum of accessories). I believe if I was running a later version, I could use a subqueries, or if it was supported, correlated sub queries in the from statement. Thanks, Roger Sample Data (other fields exist but I believe are superfluous) Conveyor: Serial 10 11 Support SerialCost 1100 1150 10001200 10001250 Accessory SerialCost 150 175 10001100 10001200 Desired Result SerialSupportCostAccessoryCost 10250125 11450300
Re: Outer join question
First, you database design. You don't need to separate actresses from
actors... Why do that? They are the same entity, a person, with only one
different attribute: the genre. So, you should join them in one single
table:
Actors
==
act_id
name
genre ENUM('m','f')
Then, the table DVD. If we mantain things as they are, we would need one
entry in table DVD for each actor/actress in the movie, we would be
repeating the title N times... Whenever you change it, you would need to
change N records... And is redundant information.
So, let's take DVD as an entity by itself. DVD would be:
DVD
===
dvd_id
title
description
year
other_fields_related...
and then, you relate the 2 tables with this one:
DVD_Actors
==
dvd_id
act_id
leader ENUM('yes','no')
(the table name may not be the happiest :-p)
The field leading tells you if that actor is the leading one or not...
I want to select the title of each movie, along with the corresponding
leading actor and/or actress name, but a straightforward join will only
return those movie titles that have NOT NULL values in BOTH the acto_id
and actr_id fields in the DVD table.
With this design, you could use:
SELECT M.title, A.name, A.genre, DA.leader
FROM DVD AS M
LEFT JOIN DVD_Actors AS DA ON (M.dvd_id=DA.dvd_id)
LEFT JOIN Actors AS A ON (DA.act_id=A.act_id)
Please, read the manual about LEFT JOIN.
My grey-haired memory tells me that an outer join for both the actor table
and the actress table is the answer, in that the query will return all
titles *even if* one or both fields are NULL. (At least that was the case
when I was using Oracle!)
And you were right.
So, can somebody please correct the following query (and explain the
syntax) so that it will work please? (I haven't tried putting an outer
join in it because I don't understand the syntax.)
In this example, you are using inner join... please, read the manual
about JOINs.
Select
actr.name,
acto.name,
dvd.title
from
actresses actr,
actors acto,
dvd
where
actr.actr_id = dvd.actr_id
and
acto.acto_id = dvd.acto_id
order by dvd.title;
(I used to put (+) at the end of the outer join line, but don't think this
will work in MYSQL - at least I don't see it in the manual.)
Thanks in advance for your kind help and sorry for the wordy question!
--
Diana Soares
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: Re: Outer join question]
Sorry, i meant gender, not genre.
-Forwarded Message-
First, you database design. You don't need to separate actresses from
actors... Why do that? They are the same entity, a person, with only one
different attribute: the genre. So, you should join them in one single
table:
Actors
==
act_id
name
genre ENUM('m','f')
Then, the table DVD. If we mantain things as they are, we would need one
entry in table DVD for each actor/actress in the movie, we would be
repeating the title N times... Whenever you change it, you would need to
change N records... And is redundant information.
So, let's take DVD as an entity by itself. DVD would be:
DVD
===
dvd_id
title
description
year
other_fields_related...
and then, you relate the 2 tables with this one:
DVD_Actors
==
dvd_id
act_id
leader ENUM('yes','no')
(the table name may not be the happiest :-p)
The field leading tells you if that actor is the leading one or not...
I want to select the title of each movie, along with the corresponding
leading actor and/or actress name, but a straightforward join will only
return those movie titles that have NOT NULL values in BOTH the acto_id
and actr_id fields in the DVD table.
With this design, you could use:
SELECT M.title, A.name, A.genre, DA.leader
FROM DVD AS M
LEFT JOIN DVD_Actors AS DA ON (M.dvd_id=DA.dvd_id)
LEFT JOIN Actors AS A ON (DA.act_id=A.act_id)
Please, read the manual about LEFT JOIN.
My grey-haired memory tells me that an outer join for both the actor table
and the actress table is the answer, in that the query will return all
titles *even if* one or both fields are NULL. (At least that was the case
when I was using Oracle!)
And you were right.
So, can somebody please correct the following query (and explain the
syntax) so that it will work please? (I haven't tried putting an outer
join in it because I don't understand the syntax.)
In this example, you are using inner join... please, read the manual
about JOINs.
Select
actr.name,
acto.name,
dvd.title
from
actresses actr,
actors acto,
dvd
where
actr.actr_id = dvd.actr_id
and
acto.acto_id = dvd.acto_id
order by dvd.title;
(I used to put (+) at the end of the outer join line, but don't think this
will work in MYSQL - at least I don't see it in the manual.)
Thanks in advance for your kind help and sorry for the wordy question!
--
Diana Soares
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outer join question
First, you database design. You don't need to separate actresses from actors... Why do that? They are the same entity, a person, with only one different attribute: the genre. So, you should join them in one single table: ... Actually, it is possible to be female and to be an Actor. For example, the Screen Actors' Guild officially considers the term Actor to be gender-neutral and applies this term to all people. ... Craig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Outer join question
Hi, I'm just getting into MYSQL after nearly 12 years away from relational databases and I'm trying to blow the cobwebs away. So please bear with me if this is a simple question! I am using a test database to teach myself MYSQL and am using my DVD collection as the subject. I have 3 tables in this example: Actresses === actr_id name Actors == acto_id name DVD == Title acto_id actr_id The acto_id and actr_id in the DVD table indicates the id for the leading actor or actress of the movie in question. Obviously, in the case of some movies, it may be an all-male or all-female cast, so the id fields in the DVD table are allowed to be NULL. I want to select the title of each movie, along with the corresponding leading actor and/or actress name, but a straightforward join will only return those movie titles that have NOT NULL values in BOTH the acto_id and actr_id fields in the DVD table. My grey-haired memory tells me that an outer join for both the actor table and the actress table is the answer, in that the query will return all titles *even if* one or both fields are NULL. (At least that was the case when I was using Oracle!) So, can somebody please correct the following query (and explain the syntax) so that it will work please? (I haven't tried putting an outer join in it because I don't understand the syntax.) Select actr.name, acto.name, dvd.title from actresses actr, actors acto, dvd where actr.actr_id = dvd.actr_id and acto.acto_id = dvd.acto_id order by dvd.title; (I used to put (+) at the end of the outer join line, but don't think this will work in MYSQL - at least I don't see it in the manual.) Thanks in advance for your kind help and sorry for the wordy question! Bjorn Barton-Pye Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Outer join question
Hi, I'm just getting into MYSQL after nearly 12 years away from relational databases and I'm trying to blow the cobwebs away. So please bear with me if this is a simple question! I am using a test database to teach myself MYSQL and am using my DVD collection as the subject. I have 3 tables in this example: Actresses === actr_id name Actors == acto_id name DVD == Title acto_id actr_id The acto_id and actr_id in the DVD table indicates the id for the leading actor or actress of the movie in question. Obviously, in the case of some movies, it may be an all-male or all-female cast, so the id fields in the DVD table are allowed to be NULL. I want to select the title of each movie, along with the corresponding leading actor and/or actress name, but a straightforward join will only return those movie titles that have NOT NULL values in BOTH the acto_id and actr_id fields in the DVD table. My grey-haired memory tells me that an outer join for both the actor table and the actress table is the answer, in that the query will return all titles *even if* one or both fields are NULL. (At least that was the case when I was using Oracle!) So, can somebody please correct the following query (and explain the syntax) so that it will work please? (I haven't tried putting an outer join in it because I don't understand the syntax.) Select actr.name, acto.name, dvd.title from actresses actr, actors acto, dvd where actr.actr_id = dvd.actr_id and acto.acto_id = dvd.acto_id order by dvd.title; (I used to put (+) at the end of the outer join line, but don't think this will work in MYSQL - at least I don't see it in the manual.) Thanks in advance for your kind help and sorry for the wordy question! Bjorn Barton-Pye Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outer join question
So, can somebody please correct the following query (and explain the syntax) so that it will work please? (I haven't tried putting an outer join in it because I don't understand the syntax.) Select actr.name, acto.name, dvd.title from actresses actr, actors acto, dvd where actr.actr_id = dvd.actr_id and acto.acto_id = dvd.acto_id order by dvd.title; (I used to put (+) at the end of the outer join line, but don't think this will work in MYSQL - at least I don't see it in the manual.) Thanks in advance for your kind help and sorry for the wordy question! i have not really used outer join, i'm still trying to fine tune my sql aswell but to get null values i use left join, to return not null values i use inner join, i select from the first table first though, is it better to select it at the end ? so soemthing like select * from dvd left join actresses actr on actr.actr_id=dvd.actr_id left join actors acto on acto.acto_id=dvd.acto_id or by dvd.title -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outer join question
so soemthing like select * from dvd left join actresses actr on actr.actr_id=dvd.actr_id left join actors acto on acto.acto_id=dvd.acto_id or by dvd.title totally forgot, to get a really good query especially when you use Innodb it doesnt like null values on foreign keys, i'd setup a row in the actors and actresses table like No Actor or No actress and then use that key for the null values and use INNER JOIN, check EXPLAIN aswell , it'll show what indexes are being used. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outer join question
Bjorn Barton-Pye wrote: I am using a test database to teach myself MYSQL and am using my DVD collection as the subject. I have 3 tables in this example: Actresses === actr_id name Actors == acto_id name DVD == Title acto_id actr_id The acto_id and actr_id in the DVD table indicates the id for the leading actor or actress of the movie in question. Obviously, in the case of some movies, it may be an all-male or all-female cast, so the id fields in the DVD table are allowed to be NULL. If you want to learn about outer joins this is a fine model. If you want to index your DVD collection, you should consider the following data model: DVD: dvd_ID PRIMARY KEY title NOT NULL Actor: actor_ID PRIMARY KEY name NOT NULL gender NOT NULL DVD_Actor: dvd_ID REFERENCES DVD actor_ID REFERNCES Actor Add more tables if you want to plan for 1 DVD having more as 1 movie or 1 movie having more as 1 DVD ;-) Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outer join question
DVD_Actor: dvd_ID REFERENCES DVD actor_ID REFERNCES Actor Is this how you setup a join table ? what exactly is the references keyword ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outer join question
[EMAIL PROTECTED] wrote: DVD_Actor: dvd_ID REFERENCES DVD actor_ID REFERNCES Actor Is this how you setup a join table ? Yes. what exactly is the references keyword ? It indicates a foreign key. Full syntax is something like: dvd_ID CONSTRAINT dvc_fk FOREIGN KEY REFERENCES DVD (dvd_ID) Read the manual *very* carefully before using foreign keys in MySQL when you have an Oracle background. Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outer join question
[EMAIL PROTECTED] wrote: DVD_Actor: dvd_ID REFERENCES DVD actor_ID REFERNCES Actor Is this how you setup a join table ? Yes. what exactly is the references keyword ? It indicates a foreign key. Full syntax is something like: dvd_ID CONSTRAINT dvc_fk FOREIGN KEY REFERENCES DVD (dvd_ID) Read the manual *very* carefully before using foreign keys in MySQL when you have an Oracle background. Jochem Ok i'm setting up the foreign key relations in the main table is that bad ? I use a join table for a one to many situation. The cool thing about setting it up in the main table is, say you delete an actor you can setup a cascade delete to delete all the dvd titles aswell ;) I'm using sqlyog which has a nice relationship setup feature. -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Self Join question - large table
Hello, I have a query that will be used alot on my website that involves a 42 million record table being self-joined. The primary table instance will be limited by an index resulting in 1 to about 50,000 rows being selected, then joined to the second instance of the table, which will retrieve one to five or so rows for each record in the source table. This table is totally static and is updated once per day during a maintenance cycle. Here's my question: Does it make sense (from a performance standpoint) to actually make a duplicate of this table so MySQL is joining two identical tables rather than self joining the same table? Thanks, -Hank __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Self Join question - large table
Absolutely not. First, you have the overhead of maintaining the two tables. Second, MySQL doesn't know that they are identical tables, so it will try to cache both of them. On a self join, MySQL does know they are identical, so it will only need to load the data into memory once. If there is enough memory. If you want the best performance and you have enough memory, the best thing you can do is load the table into a heap table. Essentially you are preloading the entire table into RAM, eliminating the disk as a performance bottleneck. You just need to refresh the heap table when you update the real table. On Nov 25, 2003, at 8:31 AM, Henry Hank wrote: Hello, I have a query that will be used alot on my website that involves a 42 million record table being self-joined. The primary table instance will be limited by an index resulting in 1 to about 50,000 rows being selected, then joined to the second instance of the table, which will retrieve one to five or so rows for each record in the source table. This table is totally static and is updated once per day during a maintenance cycle. Here's my question: Does it make sense (from a performance standpoint) to actually make a duplicate of this table so MySQL is joining two identical tables rather than self joining the same table? Thanks, -Hank __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ALTER TABLE z ADD INDEX... Speed: Follow up to LEFT JOIN question
What amount of time is reasonable to expect for indexing an FK int field on a simple table with about 30K records. It's running on my learning machine -- an aging Pentium 5/133 box running RH 8 which is ordinarily quite fast enough for my purposes. It's been running about six hours now, and I'm beginning to suspect something is hung (though I can query other tables just fine from another ssh session. The running query is: mysql ALTER TABLE city ADD INDEX idx_city_state_id (state_id); If the answer is Well, yes, you dolt, something is OBVIOUSLY crapping out, can you give me some suggestions for troubleshooting? Thx - Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE z ADD INDEX... Speed: Follow up to LEFT JOIN question
Hi Dan, 133MHz huh? :-) Well, how large is the table? Huge rows? How many other indexes are on the table and on how many columns? Are those columns large? If you had a full-text index on a large column, for example, it could take very long on that system, especially if you're using 3.23. Hard disk could be the slowdown too. What does SHOW PROCESSLIST say? I think it will say Copy to tmp table when making a copy of the data file. And then, when building the indexes, Repair with keycache, or Repair by sorting (faster than keycache). If there's CPU or disk activity (and it's not from other things...), then it's not hung. Just sit and wait. :-) Matt - Original Message - From: D. R. Hansen Sent: Wednesday, October 15, 2003 3:39 PM Subject: ALTER TABLE z ADD INDEX... Speed: Follow up to LEFT JOIN question What amount of time is reasonable to expect for indexing an FK int field on a simple table with about 30K records. It's running on my learning machine -- an aging Pentium 5/133 box running RH 8 which is ordinarily quite fast enough for my purposes. It's been running about six hours now, and I'm beginning to suspect something is hung (though I can query other tables just fine from another ssh session. The running query is: mysql ALTER TABLE city ADD INDEX idx_city_state_id (state_id); If the answer is Well, yes, you dolt, something is OBVIOUSLY crapping out, can you give me some suggestions for troubleshooting? Thx - Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE z ADD INDEX... Speed: Follow up to LEFT JOIN question
I ended up stopping and restarting MySQL -- which cleared up the problem. Index creation took around a minute. Dan At 05:38 PM 10/15/03, Matt W wrote: Hi Dan, 133MHz huh? :-) Well, how large is the table? Huge rows? How many other indexes are on the table and on how many columns? Are those columns large? If you had a full-text index on a large column, for example, it could take very long on that system, especially if you're using 3.23. Hard disk could be the slowdown too. What does SHOW PROCESSLIST say? I think it will say Copy to tmp table when making a copy of the data file. And then, when building the indexes, Repair with keycache, or Repair by sorting (faster than keycache). If there's CPU or disk activity (and it's not from other things...), then it's not hung. Just sit and wait. :-) Matt - Original Message - From: D. R. Hansen Sent: Wednesday, October 15, 2003 3:39 PM Subject: ALTER TABLE z ADD INDEX... Speed: Follow up to LEFT JOIN question What amount of time is reasonable to expect for indexing an FK int field on a simple table with about 30K records. It's running on my learning machine -- an aging Pentium 5/133 box running RH 8 which is ordinarily quite fast enough for my purposes. It's been running about six hours now, and I'm beginning to suspect something is hung (though I can query other tables just fine from another ssh session. The running query is: mysql ALTER TABLE city ADD INDEX idx_city_state_id (state_id); If the answer is Well, yes, you dolt, something is OBVIOUSLY crapping out, can you give me some suggestions for troubleshooting? Thx - Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LEFT JOIN question
Hi all; I was given a query today of the following: SELECT record_id, record_title,artist_name,label_name,record_catalog FROM record_profile LEFT JOIN artist_profile,label_profile ON record_profile.artist_id = artist_profile.artist_id OR record_profile.label_id = label_profile.label_id GROUP BY record_id The user is trying to LEFT JOIN the artist and label tables to the record table, and I realized that it has been quite a while since I did a LEFT JOIN two tables to the same source table. Anyone know how to do this? I can't remember if this is how it would be done: SELECT record_id, record_title,artist_name,label_name,record_catalog FROM record_profile LEFT JOIN artist_profile ON record_profile.artist_id = artist_profile.artist_id LEFT JOIN label_profile ON record_profile.label_id = label_profile.label_id GROUP BY record_id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Join question
Hi, I have posted a similar question, but can't find the answers. I'm sorry. What i'm trying to do is that i have 3 tables each has three columns and are indexed. so I want to do something like select everything in 3 tables and display only similar id. Select id from table1 where table1.cl1 = 1 and table1.cle2=5 and table1.cl3 IN(1,2,5,8) Join Select id from table2 where table2.cl1 = 4 and table2.cle2IN(10,12,81) and table2.cl3 IN(3,7) Join Select id from table3 where table3.distance BETWEEN 1 and 99 where table1.id = table2.id=table3.id Any help is appreciated Anthony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Join question
SELECT table1.id FROM table1, table2, table3 WHERE table1.cl1 = 1 AND table1.cle2=5 AND table1.cl3 IN(1,2,5,8) AND table2.cl1 = 4 AND table2.cle2 IN (10,12,81) AND table2.cl3 IN (3,7) AND table3.distance BETWEEN 1 AND 99 AND table1.id = table2.id AND table2.id = table3.id; That should do what you are looking for, otherwise let us know. Regards, Mike Hilyer www.vbmysql.com -Original Message- From: Anthony Ward [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 12:17 PM To: [EMAIL PROTECTED] Subject: Join question Hi, I have posted a similar question, but can't find the answers. I'm sorry. What i'm trying to do is that i have 3 tables each has three columns and are indexed. so I want to do something like select everything in 3 tables and display only similar id. Select id from table1 where table1.cl1 = 1 and table1.cle2=5 and table1.cl3 IN(1,2,5,8) Join Select id from table2 where table2.cl1 = 4 and table2.cle2IN(10,12,81) and table2.cl3 IN(3,7) Join Select id from table3 where table3.distance BETWEEN 1 and 99 where table1.id = table2.id=table3.id Any help is appreciated Anthony -- 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: Join question
Anthony, Do you mean this (not tested)? Select table1.id from table1 INNER JOIN table2 USING (id) INNER JOIN table3 USING (id) WHERE table1.cl1 = 1 and table1.cle2=5 AND table1.cl3 IN(1,2,5,8) AND table2.cl1 = 4 and table2.cle2 IN(10,12,81) AND table2.cl3 IN(3,7) AND table3.distance BETWEEN 1 and 99; HTH PB - Original Message - From: Anthony Ward To: [EMAIL PROTECTED] Sent: Thursday, June 05, 2003 1:17 PM Subject: Join question Hi, I have posted a similar question, but can't find the answers. I'm sorry. What i'm trying to do is that i have 3 tables each has three columns and are indexed. so I want to do something like select everything in 3 tables and display only similar id. Select id from table1 where table1.cl1 = 1 and table1.cle2=5 and table1.cl3 IN(1,2,5,8) Join Select id from table2 where table2.cl1 = 4 and table2.cle2IN(10,12,81) and table2.cl3 IN(3,7) Join Select id from table3 where table3.distance BETWEEN 1 and 99 where table1.id = table2.id=table3.id Any help is appreciated Anthony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join question
Hi, what is the difference between your way and Mike Hillyer way?? (I can see the INNER join). But thanx to both of you. Anthony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Join question
Syntax only. INNER JOIN table2 USING (id) INNER JOIN table3 USING (id) Equates to table1.id = table2.id AND table2.id = table3.id; Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Anthony Ward [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 12:53 PM To: [EMAIL PROTECTED] Subject: Re: Join question Hi, what is the difference between your way and Mike Hillyer way?? (I can see the INNER join). But thanx to both of you. Anthony -- 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: Join question
The diff is readability. I prefer the clarity of putting the condition under 'Join' and other cnditions under 'Where'. The MySQL optimiser actually moves the Join to the Where clause, so there ought to be no performance diff between the two. PB - - Original Message - From: Anthony Ward To: [EMAIL PROTECTED] Sent: Thursday, June 05, 2003 1:53 PM Subject: Re: Join question Hi, what is the difference between your way and Mike Hillyer way?? (I can see the INNER join). But thanx to both of you. Anthony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: embedded select / left join question
That does it! Thanks much. Jim On Saturday, March 29, 2003, at 06:14 PM, Bruce Feist wrote: Jim Miller wrote: I want to do a join and select of these tables that will give me all the Entrants who did NOT enter contest 1 (i.e., Mary and Bill). Try: select entrant.name from entrant left join contestEntries on entrant.id = contestEntries.entrant AND contestEntries.ContestNumber = 1 where contestEntries.entrant is null Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: embedded select / left join question
Jim Miller wrote: I want to do a join and select of these tables that will give me all the Entrants who did NOT enter contest 1 (i.e., Mary and Bill). Try: select entrant.name from entrant left join contestEntries on entrant.id = contestEntries.entrant AND contestEntries.ContestNumber = 1 where contestEntries.entrant is null Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
embedded select / left join question
This is probably a real newbie question, but, since that's what I am... Using MySQL 3.23, I have two tables, for instance: ContestEntries: id, entrant, contestNumber, etc. Entrant: id, name, etc. There are multiple contests, and Entrants can enter as many as they like. So, Entrant might look like: id=1, name=John id=2, name=Mary id=3, name=Bill and ContestEntries might look like: id=1, entrant=1, contestNumber=1 id=2, entrant=2, contestNumber=2 id=3, entrant=1, contestNumber=2 I want to do a join and select of these tables that will give me all the Entrants who did NOT enter contest 1 (i.e., Mary and Bill). I've been told that the right way to do this is with an embedded select, which MySQL 3.23 doesn't have. So, I've been trying to do it with a left join. The closest I've been able to come is something like: select entrant.name from entrant left join contestEntries on entrant.id = contestEntries.entrant where contestEntries.entrant is null or contestEntries.contestNumber != 1 This obviously doesn't work -- it finds Bill, since he didn't enter either contest, and it finds Mary, since there is no row in contestEntries for her where contestNumber = 1. But it also finds John, because of contestEntries id=3 -- its contestNumber is not equal to 1, and so the WHERE clause is satisfied. Does anyone have any suggestions for a way to do this? Again, I'm looking for a 3.23-compatible solution Thanks, Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Join Question
I have two tables. tbl_reports and tbl_personnel tbl_reports has these fields: rep_id, person1, person2, person3, person 4 tbl_personnel has these fields: per_id, fname, lname, mname What is the way to get each report back once, and have the fname, mname, and lname fields available to print to the screen in PHP for each person1, person2, person3, person4 (which contain per_id)? TIA - Charles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join Question
Usually it's best to work with normalized tables, which would make this trivial. tbl_reports isn't normalized, since it has a simulated array of persons in it. Could it be split into two tables: tbl_reports, with fields: rep_id (primary key) and other report-specific information you didn't mention below tbl_pers_rpt, with fields rep_id, person, (together they are the key) and other information you didn't mention below? If you must stay with the denormalized design, you can still do the query. I'll show you what it would look like for just two persons, and you can generalize. SELECT * FROM tbl_reports r LEFT JOIN tbl_personnel p1 ON r.person1 = p1.per_id LEFT JOIN tbl_personnel p2 ON r.person2 = p2.per_id ; If you need more reasons that denormalized tables are usually not a good idea, just ask. Bruce Feist Charles Kline wrote: tbl_reports has these fields: rep_id, person1, person2, person3, person 4 tbl_personnel has these fields: per_id, fname, lname, mname What is the way to get each report back once, and have the fname, mname, and lname fields available to print to the screen in PHP for each person1, person2, person3, person4 (which contain per_id)? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join Question
On Fri, 2003-03-28 at 01:39, Bruce Feist wrote: Usually it's best to work with normalized tables, which would make this trivial. tbl_reports isn't normalized, since it has a simulated array of persons in it. Could it be split into two tables: i'm interested on how to normalize a table... can you suggest me some reads ? TIA ALx -- alx [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join Question
alx wrote: On Fri, 2003-03-28 at 01:39, Bruce Feist wrote: Usually it's best to work with normalized tables, which would make this trivial. tbl_reports isn't normalized, since it has a simulated array of persons in it. Could it be split into two tables: i'm interested on how to normalize a table... can you suggest me some reads ? A reasonable, but too abridged, summary is at http://www.tomlauren.com/docs/databaseDesign.html#denormalization . A more complete description is at http://www.15seconds.com/issue/020522.htm . And, essentially *any* book on database design worthy of the name will devote a good chunk of space to it. And, I'll give a description right here. I can't help it; I used to teach database administration g. There are several normal forms, which are degrees of normalization. The goal of normalizing is to minimize weird stuff called anomalies, which I'll describe in a moment. The most common target is third normal form, which I'll describe in another moment. Anomalies occur when database operations which seem correct result in logically corrupt databases. For instance, let's imagine database representing a school, where all information on a teacher is carried in a course table instead of being in its own table. The course table might contain teacher name, teacher salary, date hired, course name, classroom number, and the time of day that the course meets, for example. Note: This is a *bad* design! We will make it even worse by adding room for up to 30 student names, for students taking the class. Now, let's say that all courses a teacher teaches are cancelled, and the teacher is going to be assigned new courses instead. We delete the course rows... and suddenly we've lost all records of the teacher, too! The teacher data should have been put in a different table as part of the normalization process. This is a delete anomaly. Similarly, with the above structure, what happens if the teacher's name was misspelled when first entered, so we updated a course row to reflect the correct spelling. Unless we do that to *all* course rows for the teacher, we now have an inconsistency -- it looks as though there are two different teachers, because their name is entered in two different ways. Redundancy leads to corruption; this anomaly was an update anomaly. As we go to stricter normal forms, we increase the stability of our data by making it less prone to corruption through anomalies. However, we tend to increase complexity and decrease performance as well (not always!), so there's a tradeoff. Third normal form is a usual compromise, in which the following conditions are met: 1) Every table has a primary (unique) key, which may consist of one or more fields. In the above example, we might try to make the combination of course time and teacher name the primary key; this would work as long as we didn't have two teachers with the same name. (It would be much better to have a unique Teacher ID.) 2) Every non-key field in a table depends on the *entire* key. (In the above example, we would be violating this -- date hired does not depend on the course time, just on the teacher name.) 3) Non-key fields depend only on key fields, nothing else. In other words, if you know the key, that should be enough to identify the value of any other field in the table. This means that there can be no repeating fields in the table. Our inclusion of student names would violate this; the name of a student depends on more than the teacher's name and time. (In fact, it violates the second condition as well... it's unrelated to the teacher's name and time!) Here is an alternative design consisting of more tables, which does not violate these rules: TEACHER teacher_id (primary key) teacher_name date_hired salary COURSE course_id (primary key) course_description PRESENTATION (a teacher teaching a course) teacher_id course_id time_given (primary key is combination of teacher_id, course_id, and time_given) STUDENT student_id (primary key) student name matriculation_date ATTENDEE (a student taking a course) teacher_id course_id time_given student_id (primary key is combination of teacher_id, course_id, time_given, and student_id) Normalization is part of the process of logical design; this part of the process is independant of the RDBMS being used to implement the system. After logical design comes physical design, in which you take advantage of the specific facilities offered by your RDBMS to implement it efficiently. For instance, new indexes would be assigned here to speed queries, and table types would be determined. Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Desperate Sum(), Group by/Join question - One Step closer...
* Peter D Bethke Ok, in regards to my previous dilemma, I've gotten it to: SELECT golf_pick_periods.id, golf_pick_periods.period_name, golf_pick_periods.period_start_date, golf_pick_periods.period_end_date, COUNT(DISTINCT golf_events.id) AS num_events, COUNT(golf_player_picks.id) AS period_picks_count FROM database.golf_pick_periods database.golf_player_picks LEFT JOIN database.golf_events ON (golf_events.event_start_date = golf_pick_periods.period_start_date AND golf_events.event_end_date = golf_pick_periods.period_end_date) WHERE golf_player_picks.picks_player_id = 1 AND golf_player_picks.picks_event_id = golf_events.id GROUP BY golf_pick_periods.id It's returning rows for periods where there is more than 1 pick (and counting those picks too). I need it to return all the periods. It's some kind of Left Join I'm sure but I can't figure how to reform the query to use a left join to return rows where the number of picks in a period is null. Arrg! afaikt, you need to move the part of the WHERE-clause related to golf_events to the ON-clause of the LEFT JOIN: ... LEFT JOIN database.golf_events ON golf_player_picks.picks_event_id = golf_events.id AND golf_events.event_start_date = golf_pick_periods.period_start_date AND golf_events.event_end_date = golf_pick_periods.period_end_date WHERE golf_player_picks.picks_player_id = 1 GROUP BY golf_pick_periods.id HTH, -- 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
Desperate Sum(), Group by/Join question
Hi all, I've got a mysql application that tracks golf events where players pick winners. Each event is associated by date with a pick period (ie a month). I'm trying to get a COUNT() function to count all the picks by pick period for a given player (in this case id = 1). The following SQL: SELECT golf_pick_periods.id, golf_pick_periods.contest_id, golf_pick_periods.period_name, golf_pick_periods.period_start_date, golf_pick_periods.period_end_date, COUNT(golf_player_picks.id) AS period_picks_count FROM database.golf_pick_periods LEFT JOIN database.golf_player_picks ON golf_player_picks.picks_player_id = 1 GROUP BY golf_pick_periods.id Yields the correct periods, but the SUM() is the same number for all of them when it should be different. Can you have more than one GROUP BY clause? Essentially I'm grouping together the periods and in each period counting the picks. Help! Best, Peter D Bethke - 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
Desperate Sum(), Group by/Join question
Hi all, I've got a mysql application that tracks golf events where players pick winners. Each event is associated by date with a pick period (ie a month). I'm trying to get a COUNT() function to count all the picks by pick period for a given player (in this case id = 1). The following SQL: SELECT golf_pick_periods.id, golf_pick_periods.contest_id, golf_pick_periods.period_name, golf_pick_periods.period_start_date, golf_pick_periods.period_end_date, COUNT(golf_player_picks.id) AS period_picks_count FROM database.golf_pick_periods LEFT JOIN database.golf_player_picks ON golf_player_picks.picks_player_id = 1 GROUP BY golf_pick_periods.id Yields the correct periods, but the SUM() is the same number for all of them when it should be different. Can you have more than one GROUP BY clause? Essentially I'm grouping together the periods and in each period counting the picks. Help! Best, Peter D Bethke - 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: Desperate Sum(), Group by/Join question - One Step closer...
Ok, in regards to my previous dilemma, I've gotten it to: SELECT golf_pick_periods.id, golf_pick_periods.period_name, golf_pick_periods.period_start_date, golf_pick_periods.period_end_date, COUNT(DISTINCT golf_events.id) AS num_events, COUNT(golf_player_picks.id) AS period_picks_count FROM database.golf_pick_periods database.golf_player_picks LEFT JOIN database.golf_events ON (golf_events.event_start_date = golf_pick_periods.period_start_date AND golf_events.event_end_date = golf_pick_periods.period_end_date) WHERE golf_player_picks.picks_player_id = 1 AND golf_player_picks.picks_event_id = golf_events.id GROUP BY golf_pick_periods.id It's returning rows for periods where there is more than 1 pick (and counting those picks too). I need it to return all the periods. It's some kind of Left Join I'm sure but I can't figure how to reform the query to use a left join to return rows where the number of picks in a period is null. Arrg! Peter - 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: Inner join question!
Hi Tore, Thank you so much. You are correct, I had duplicate records in the file. I haven't finish cleaning up the table. Once I finish cleaning and run the query and if I still duplicate, I will let you know. Till then, thank you so much for the answer. Unni --- Tore Bostrup [EMAIL PROTECTED] wrote: The only reason I could see for this would be if you have duplicates (across date num1 num2 time) in both tables, or quadruplicates in one. You join looks correct, so take a closer look at your data. Try running the following queries: SELECT A.date, num1, num2, A.time, Count(*) FROM A GROUP BY A.date, num1, num2, A.time HAVING Count(*) 1 SELECT B.date, num1, num2, B.time, Count(*) FROM B GROUP BY B.date, num1, num2, B.time HAVING Count(*) 1 HTH, Tore. - Original Message - From: Ramesh Pillai [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 27, 2003 10:36 PM Subject: Inner join question! All, I have two tables like the following table A date num1 num2 time table B date num1 num2 time When I run a query like the following select * from A as a inner join B as b on a.num1 = b.num1 and a.date=b.date and a.num2=b.num2 and a.time=b.time I am getting the results repeated 4 times, could someone tell me why I am getting 4 rows and how can I elliminate it? Thanks. Ramesh __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - 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 __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Inner join question!
All, I have two tables like the following table A date num1 num2 time table B date num1 num2 time When I run a query like the following select * from A as a inner join B as b on a.num1 = b.num1 and a.date=b.date and a.num2=b.num2 and a.time=b.time I am getting the results repeated 4 times, could someone tell me why I am getting 4 rows and how can I elliminate it? Thanks. Ramesh __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Inner join question!
The only reason I could see for this would be if you have duplicates (across date num1 num2 time) in both tables, or quadruplicates in one. You join looks correct, so take a closer look at your data. Try running the following queries: SELECT A.date, num1, num2, A.time, Count(*) FROM A GROUP BY A.date, num1, num2, A.time HAVING Count(*) 1 SELECT B.date, num1, num2, B.time, Count(*) FROM B GROUP BY B.date, num1, num2, B.time HAVING Count(*) 1 HTH, Tore. - Original Message - From: Ramesh Pillai [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 27, 2003 10:36 PM Subject: Inner join question! All, I have two tables like the following table A date num1 num2 time table B date num1 num2 time When I run a query like the following select * from A as a inner join B as b on a.num1 = b.num1 and a.date=b.date and a.num2=b.num2 and a.time=b.time I am getting the results repeated 4 times, could someone tell me why I am getting 4 rows and how can I elliminate it? Thanks. Ramesh __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - 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: RE: Join-question
Hello Joseph, Thursday, December 05, 2002, 8:39:18 PM, you wrote: NJ Victoria, NJ I am trying to get the hang of this also. In your statement below you NJ show, what appears to me, two tables, mytest ( I see this one ) and t1. NJ Where did table t1 come from? Could you explain your sql a little more NJ if that would not be to much of a problem? Thank you for you patience. 'mytable' - ia a real table. t1, t2, and t3 are aliases. I use a self join - join 'mytable' with the 'mytable' on the conditions t1.rootid=t2.uid to get the name for 'rootid' and then one more self join to get the name for 'parentid'. NJ -joseph NJ -Original Message- NJ From: Victoria Reznichenko [mailto:[EMAIL PROTECTED]] NJ Sent: Thursday, December 05, 2002 9:01 AM NJ To: [EMAIL PROTECTED] NJ Subject: re: Join-question NJ Michelle, NJ Thursday, December 05, 2002, 5:46:03 PM, you wrote: MdB I believe this question is solved by a join, but I MdB haven't really got a hang of it. MdB My table: MdB -- MdB | uid | rootid | parentid | name | MdB -- MdB | 1 | 0 | 0| name1| MdB | 2 | 1 | 1| name2| MdB | 3 | 1 | 2| name3| MdB | 4 | 1 | 3| name4| MdB | 5 | 1 | 2| name5| MdB ... MdB How do I get this (WHERE uid=5): MdB -- MdB | rootid_name | parentid_name | name | MdB -- MdB | name1 | name2 | name5| MdB -- MdB If you need more info, please tell me. NJ Yes, JOIN is what you need. NJ Something like that: mysql SELECT t1.uid, t1.name, t2.name, t3.name - FROM mytest t1 - LEFT JOIN mytest t2 ON t1.rootid=t2.uid - LEFT JOIN mytest t3 ON t1.parentid=t3.uid - WHERE t1.uid=5; NJ +--+---+---+---+ NJ | uid | name | name | name | NJ +--+---+---+---+ NJ |5 | name5 | name1 | name2 | NJ +--+---+---+---+ NJ 1 row in set (0.00 sec) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: Join-question
* Michelle de Beer I believe this question is solved by a join, but I haven't really got a hang of it. My table: -- | uid | rootid | parentid | name | -- | 1 | 0 | 0| name1| | 2 | 1 | 1| name2| | 3 | 1 | 2| name3| | 4 | 1 | 3| name4| | 5 | 1 | 2| name5| ... How do I get this (WHERE uid=5): -- | rootid_name | parentid_name | name | -- | name1 | name2 | name5| -- Try two self joins: SELECT r.name rootid_name,p.name parentid_name, name FROM tablename t LEFT JOIN tablename r ON r.uid = t.rootid LEFT JOIN tablename p ON p.uid = t.parentid WHERE t.uid = 5; -- Roger sql - 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: Join-question
- Original Message - From: Michelle de Beer [EMAIL PROTECTED] I believe this question is solved by a join, but I haven't really got a hang of it. My table: -- | uid | rootid | parentid | name | -- | 1 | 0 | 0| name1| | 2 | 1 | 1| name2| | 3 | 1 | 2| name3| | 4 | 1 | 3| name4| | 5 | 1 | 2| name5| ... How do I get this (WHERE uid=5): -- | rootid_name | parentid_name | name | -- | name1 | name2 | name5| -- Here you go. select a.name, b.name, c.name from yourtable as a, yourtable as b, yourtable as c where a.uid=c.rootid and b.uid=c.parentid and uid=5; or, as left joins: select a.name, b.name, c.name from yourtable as c left join yourtable as a on a.uid=c.rootid left join yourtable as b on b.uid=c.parentid where uid=5; Ryan sql to the hizzo query to the hizza - 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: Join-question
Hi: You might find these articles from O'Reilly Network will help clear the water. http://www.onlamp.com/pub/ct/19 Doug On Thu, 5 Dec 2002 07:46:03 -0800 (PST), Michelle de Beer wrote: I believe this question is solved by a join, but I haven't really got a hang of it. My table: -- | uid | rootid | parentid | name | -- | 1 | 0 | 0| name1| | 2 | 1 | 1| name2| | 3 | 1 | 2| name3| | 4 | 1 | 3| name4| | 5 | 1 | 2| name5| ... How do I get this (WHERE uid=5): -- | rootid_name | parentid_name | name | -- | name1 | name2 | name5| -- If you need more info, please tell me. Any thoughts? // Michelle sql, query __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.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 - 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
Join-question
I believe this question is solved by a join, but I haven't really got a hang of it. My table: -- | uid | rootid | parentid | name | -- | 1 | 0 | 0| name1| | 2 | 1 | 1| name2| | 3 | 1 | 2| name3| | 4 | 1 | 3| name4| | 5 | 1 | 2| name5| ... How do I get this (WHERE uid=5): -- | rootid_name | parentid_name | name | -- | name1 | name2 | name5| -- If you need more info, please tell me. Any thoughts? // Michelle sql, query __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.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
re: Join-question
Michelle, Thursday, December 05, 2002, 5:46:03 PM, you wrote: MdB I believe this question is solved by a join, but I MdB haven't really got a hang of it. MdB My table: MdB -- MdB | uid | rootid | parentid | name | MdB -- MdB | 1 | 0 | 0| name1| MdB | 2 | 1 | 1| name2| MdB | 3 | 1 | 2| name3| MdB | 4 | 1 | 3| name4| MdB | 5 | 1 | 2| name5| MdB ... MdB How do I get this (WHERE uid=5): MdB -- MdB | rootid_name | parentid_name | name | MdB -- MdB | name1 | name2 | name5| MdB -- MdB If you need more info, please tell me. Yes, JOIN is what you need. Something like that: mysql SELECT t1.uid, t1.name, t2.name, t3.name - FROM mytest t1 - LEFT JOIN mytest t2 ON t1.rootid=t2.uid - LEFT JOIN mytest t3 ON t1.parentid=t3.uid - WHERE t1.uid=5; +--+---+---+---+ | uid | name | name | name | +--+---+---+---+ |5 | name5 | name1 | name2 | +--+---+---+---+ 1 row in set (0.00 sec) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: Basic SQL join question
SELECT * FROM Projects INNER JOIN KeywordLink ON Projects.Id = KeywordLink.Pid INNER JOIN Keywords KeywordLink.Kid = Keywords.Id WHERE Keyword LIKE '%historical%' AND Keyword like '%scenic%'; 1. Note the single quotes. 2. You can place the join logic in the WHERE clause but I prefer the clarity obtained by keeping it in JOIN clauses and using the WHERE only to contain the include if logic. hth, Arthur -Original Message- From: Adam Randall [mailto:[EMAIL PROTECTED]] Sent: Saturday, September 21, 2002 5:18 AM To: [EMAIL PROTECTED] Subject: Basic SQL join question Okay, I've thought about this every way I can conceive of, but I cannot figure out the sql query logic involved in joining three tables together. Here is what I am trying to do: I have three tables: keywords keywordlink projects keywords has these fields: id keyword keywordlink has these fields: id pid kid projects has a lot of fields, but it's primary key is ID What keywords holds is the keywords used in the various different tables in the database. keywordlink associates a project with several keywords: example keywords: id | keyword 1 | landscape 2 | historical 3 | scenic example keywordlink: id | pid | kid 1 | 1 | 2 2 | 1 | 3 3 | 2 | 1 4 | 2 | 3 example projects: id | name 1 | example 2 | extra Now, what I am trying to do is basically search the keywords database for keyword names, and then return a list of project names that are associated with those keywords. If the keywords were stored in the projects database, this is basically what I would want it to do (assume all the keywords are stored in a field called keywords in the projects table): SELECT * FROM projects where keywords like %historical% and keywords like %scenic%; This would return to me the projects that have historical and scenic in the keywords field. Now, how do I do this same operation with it broken out like I have above. The reason I am not storing the keywords in the projects table is that it would be quite a chore in changing the keywords in the system if I did that (modify one keyword, modify all the projects, etc). Anyone have any words of advice for me? Adam. -- --- Adam Randall http://www.xaren.net/ [EMAIL PROTECTED] http://nt.xaren.net/ [EMAIL PROTECTED] Macintosh users are a special case. They care passionately about the Mac OS and would rewire their own bodies to run on Mac OS X if such a thing were possible. -- Peter H. Lewis - 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: Basic SQL join question
SELECT * FROM Projects INNER JOIN KeywordLink ON Projects.Id = KeywordLink.Pid INNER JOIN Keywords KeywordLink.Kid = Keywords.Id WHERE Keyword LIKE '%historical%' AND Keyword like '%scenic%'; Out of curiousity, does the order matter? I have a JOIN with about 6 tables, some are very small, some are quite large. I know with LEFT JOIN if I switched the order of the tables around, the queries could speed up or slow down dramatically. Is the same true with INNER JOIN? Still trying to completely grasp JOINs, getting closer though... Also, shouldn't the second INNER JOIN have an ON keyword? Josh 1. Note the single quotes. 2. You can place the join logic in the WHERE clause but I prefer the clarity obtained by keeping it in JOIN clauses and using the WHERE only to contain the include if logic. hth, Arthur -Original Message- From: Adam Randall [mailto:[EMAIL PROTECTED]] Sent: Saturday, September 21, 2002 5:18 AM To: [EMAIL PROTECTED] Subject: Basic SQL join question Okay, I've thought about this every way I can conceive of, but I cannot figure out the sql query logic involved in joining three tables together. Here is what I am trying to do: I have three tables: keywords keywordlink projects keywords has these fields: id keyword keywordlink has these fields: id pid kid projects has a lot of fields, but it's primary key is ID What keywords holds is the keywords used in the various different tables in the database. keywordlink associates a project with several keywords: example keywords: id | keyword 1 | landscape 2 | historical 3 | scenic example keywordlink: id | pid | kid 1 | 1 | 2 2 | 1 | 3 3 | 2 | 1 4 | 2 | 3 example projects: id | name 1 | example 2 | extra Now, what I am trying to do is basically search the keywords database for keyword names, and then return a list of project names that are associated with those keywords. If the keywords were stored in the projects database, this is basically what I would want it to do (assume all the keywords are stored in a field called keywords in the projects table): SELECT * FROM projects where keywords like %historical% and keywords like %scenic%; This would return to me the projects that have historical and scenic in the keywords field. Now, how do I do this same operation with it broken out like I have above. The reason I am not storing the keywords in the projects table is that it would be quite a chore in changing the keywords in the system if I did that (modify one keyword, modify all the projects, etc). Anyone have any words of advice for me? Adam. - 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: Basic SQL join question
Oops! Quite right -- ON is necessary after the word JOIN. That was written in the email compiler :-) Sorry I should have proofread it before hitting Send. Arthur - Original Message - From: Josh Trutwin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 24, 2002 12:46 PM Subject: RE: Basic SQL join question SELECT * FROM Projects INNER JOIN KeywordLink ON Projects.Id = KeywordLink.Pid INNER JOIN Keywords KeywordLink.Kid = Keywords.Id WHERE Keyword LIKE '%historical%' AND Keyword like '%scenic%'; Out of curiousity, does the order matter? I have a JOIN with about 6 tables, some are very small, some are quite large. I know with LEFT JOIN if I switched the order of the tables around, the queries could speed up or slow down dramatically. Is the same true with INNER JOIN? Still trying to completely grasp JOINs, getting closer though... Also, shouldn't the second INNER JOIN have an ON keyword? Josh 1. Note the single quotes. 2. You can place the join logic in the WHERE clause but I prefer the clarity obtained by keeping it in JOIN clauses and using the WHERE only to contain the include if logic. hth, Arthur -Original Message- From: Adam Randall [mailto:[EMAIL PROTECTED]] Sent: Saturday, September 21, 2002 5:18 AM To: [EMAIL PROTECTED] Subject: Basic SQL join question Okay, I've thought about this every way I can conceive of, but I cannot figure out the sql query logic involved in joining three tables together. Here is what I am trying to do: I have three tables: keywords keywordlink projects keywords has these fields: id keyword keywordlink has these fields: id pid kid projects has a lot of fields, but it's primary key is ID What keywords holds is the keywords used in the various different tables in the database. keywordlink associates a project with several keywords: example keywords: id | keyword 1 | landscape 2 | historical 3 | scenic example keywordlink: id | pid | kid 1 | 1 | 2 2 | 1 | 3 3 | 2 | 1 4 | 2 | 3 example projects: id | name 1 | example 2 | extra Now, what I am trying to do is basically search the keywords database for keyword names, and then return a list of project names that are associated with those keywords. If the keywords were stored in the projects database, this is basically what I would want it to do (assume all the keywords are stored in a field called keywords in the projects table): SELECT * FROM projects where keywords like %historical% and keywords like %scenic%; This would return to me the projects that have historical and scenic in the keywords field. Now, how do I do this same operation with it broken out like I have above. The reason I am not storing the keywords in the projects table is that it would be quite a chore in changing the keywords in the system if I did that (modify one keyword, modify all the projects, etc). Anyone have any words of advice for me? Adam. - 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: Basic SQL join question
if you want a way to quickly experiment with sql joins, try corereader. it's a free download from http://www.corereader.com/ after you make a data connection, you press the load button to load the metadata. after that, everything is point and click to select from drop-down lists. that makes it super quick and easy to experiment with joins until you get a feel for them. when you find the query that you want, you can open the sql frame to see the sql statement that it built. you can also save the statements into a library for future use. the negative is that it runs only under ms windows. the positive is that it will query any data source on any platform from mainframes to spreadsheets, including mysql. ( ok, so i'm proud of my work. :) ) SELECT * FROM Projects INNER JOIN KeywordLink ON Projects.Id = KeywordLink.Pid INNER JOIN Keywords KeywordLink.Kid = Keywords.Id WHERE Keyword LIKE '%historical%' AND Keyword like '%scenic%'; Out of curiousity, does the order matter? I have a JOIN with about 6 tables, some are very small, some are quite large. I know with LEFT JOIN if I switched the order of the tables around, the queries could speed up or slow down dramatically. Is the same true with INNER JOIN? Still trying to completely grasp JOINs, getting closer though... Also, shouldn't the second INNER JOIN have an ON keyword? Josh 1. Note the single quotes. 2. You can place the join logic in the WHERE clause but I prefer the clarity obtained by keeping it in JOIN clauses and using the WHERE only to contain the include if logic. hth, Arthur -Original Message- From: Adam Randall [mailto:[EMAIL PROTECTED]] Sent: Saturday, September 21, 2002 5:18 AM To: [EMAIL PROTECTED] Subject: Basic SQL join question Okay, I've thought about this every way I can conceive of, but I cannot figure out the sql query logic involved in joining three tables together. Here is what I am trying to do: I have three tables: keywords keywordlink projects keywords has these fields: id keyword keywordlink has these fields: id pid kid projects has a lot of fields, but it's primary key is ID What keywords holds is the keywords used in the various different tables in the database. keywordlink associates a project with several keywords: example keywords: id | keyword 1 | landscape 2 | historical 3 | scenic example keywordlink: id | pid | kid 1 | 1 | 2 2 | 1 | 3 3 | 2 | 1 4 | 2 | 3 example projects: id | name 1 | example 2 | extra Now, what I am trying to do is basically search the keywords database for keyword names, and then return a list of project names that are associated with those keywords. If the keywords were stored in the projects database, this is basically what I would want it to do (assume all the keywords are stored in a field called keywords in the projects table): SELECT * FROM projects where keywords like %historical% and keywords like %scenic%; This would return to me the projects that have historical and scenic in the keywords field. Now, how do I do this same operation with it broken out like I have above. The reason I am not storing the keywords in the projects table is that it would be quite a chore in changing the keywords in the system if I did that (modify one keyword, modify all the projects, etc). Anyone have any words of advice for me? Adam. -- John Ragan [EMAIL PROTECTED] 501-228-0317 http://www.CoreReader.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
Basic SQL join question
Okay, I've thought about this every way I can conceive of, but I cannot figure out the sql query logic involved in joining three tables together. Here is what I am trying to do: I have three tables: keywords keywordlink projects keywords has these fields: id keyword keywordlink has these fields: id pid kid projects has a lot of fields, but it's primary key is ID What keywords holds is the keywords used in the various different tables in the database. keywordlink associates a project with several keywords: example keywords: id | keyword 1 | landscape 2 | historical 3 | scenic example keywordlink: id | pid | kid 1 | 1 | 2 2 | 1 | 3 3 | 2 | 1 4 | 2 | 3 example projects: id | name 1 | example 2 | extra Now, what I am trying to do is basically search the keywords database for keyword names, and then return a list of project names that are associated with those keywords. If the keywords were stored in the projects database, this is basically what I would want it to do (assume all the keywords are stored in a field called keywords in the projects table): SELECT * FROM projects where keywords like %historical% and keywords like %scenic%; This would return to me the projects that have historical and scenic in the keywords field. Now, how do I do this same operation with it broken out like I have above. The reason I am not storing the keywords in the projects table is that it would be quite a chore in changing the keywords in the system if I did that (modify one keyword, modify all the projects, etc). Anyone have any words of advice for me? Adam. -- --- Adam Randall http://www.xaren.net/ [EMAIL PROTECTED] http://nt.xaren.net/ [EMAIL PROTECTED] Macintosh users are a special case. They care passionately about the Mac OS and would rewire their own bodies to run on Mac OS X if such a thing were possible. -- Peter H. Lewis - 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
JOIN-Question
Hi there! I have a question on JOINs. First the system: table1: name: t1 entries: name field1 field2 test 1 2 test2 3 4 table2: name t2 entries: name field3 field4 test 5 6 test 7 8 table3: name t3 entries: name field5 field6 test 9 10 test 11 12 test2 13 14 test2 15 16 And here the problem: with the query SELECT a.*, b.*, c.* FROM t1 a INNER JOIN t2 b USING(name) INNER JOIN t3 c USING(name) WHERE b.field3=7 OR c.field6=16; I only get 1 entry with name=test. By using LEFT JOIN I only get name=test either. For sure, because in table2 there is no test2 entry. The only 2 ways I know to get also test2: 1. INSERT INTO t2 VALUES(test2,NULL,NULL) 2. temporary table But I don't want to have such dummy entries or a temporary table. Does anybody know what I have to do to with: SELECT a.*,b.*,c.* FROM ?? WHERE b.field3=5 OR c.field6=16 the result: namefield1 field2 field3field4 field5 field6 - test 12 5 6 9 10 test 12 5 6 1112 test2 34 NULLNULL 1516 I hope that someone can help me! Thank a lot, Peter __ WEB.DE MyPage - Ultimatives Kommunikationstool! Ihre Message sofort online! Domain aenderbar! http://www.das.ist.aber.ne.lustige.sache.ms/ - 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: JOIN-Question
Peter, If you would like to get such resultset namefield1 field2 field3field4 field5 field6 - test 12 5 6 9 10 test 12 5 6 1112 test2 34 NULLNULL 1516 then you need to use left join for t2, but based only on these information I couldn't help you to implement this query (I don't know how to group by rows). Please, give more information about why do you need exactly this resultset. Best regards, Mikhail. - Original Message - From: Peter Stöcker [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 19, 2002 11:03 AM Subject: JOIN-Question Hi there! I have a question on JOINs. First the system: table1: name: t1 entries: name field1 field2 test 1 2 test2 3 4 table2: name t2 entries: name field3 field4 test 5 6 test 7 8 table3: name t3 entries: name field5 field6 test 9 10 test 11 12 test2 13 14 test2 15 16 And here the problem: with the query SELECT a.*, b.*, c.* FROM t1 a INNER JOIN t2 b USING(name) INNER JOIN t3 c USING(name) WHERE b.field3=7 OR c.field6=16; I only get 1 entry with name=test. By using LEFT JOIN I only get name=test either. For sure, because in table2 there is no test2 entry. The only 2 ways I know to get also test2: 1. INSERT INTO t2 VALUES(test2,NULL,NULL) 2. temporary table But I don't want to have such dummy entries or a temporary table. Does anybody know what I have to do to with: SELECT a.*,b.*,c.* FROM ?? WHERE b.field3=5 OR c.field6=16 the result: namefield1 field2 field3field4 field5 field6 - test 12 5 6 9 10 test 12 5 6 1112 test2 34 NULLNULL 1516 I hope that someone can help me! Thank a lot, Peter __ WEB.DE MyPage - Ultimatives Kommunikationstool! Ihre Message sofort online! Domain aenderbar! http://www.das.ist.aber.ne.lustige.sache.ms/ - 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
