RE: [PHP-DB] RE: regarding multiple joins
that worked perfectly! thank you so much! -jon -Original Message- From: Rick Widmer [mailto:[EMAIL PROTECTED]] Sent: Friday, November 22, 2002 6:08 PM To: Jonathan Narong; David Elliott; Jonathan Narong on PHP-DB Subject: Re: [PHP-DB] RE: regarding multiple joins At 05:26 PM 11/22/02 -0800, Jonathan Narong wrote: >sorry for my confusing email... i'll try to clarify what i was asking: > >i have two tables (i shortened them to only show the parts relating to what >i'm trying to do now) ... > >CREATE TABLE articles ( > id smallint(4) unsigned zerofill NOT NULL auto_increment, > title varchar(40) NOT NULL default '', > public char(1) NOT NULL default 'y', > author smallint(6) default NULL, > editor smallint(6) default NULL, > photog1 smallint(6) default NULL, > photog2 smallint(6) default NULL, > >CREATE TABLE people ( > id tinyint(3) unsigned NOT NULL auto_increment, > name varchar(30) NOT NULL default '', Try this... SELECT title, a.name AS AuthorName, b.name AS EditorName, c.name AS Photog1Name, d.name AS Photog2Name FROM articles, people a, people b, people c people d WHERE author = a.id AND editor = b.id AND photog1 = c.id AND photog2 = d.id AND what ever else you want in the where clause; Rick -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] RE: regarding multiple joins
At 05:26 PM 11/22/02 -0800, Jonathan Narong wrote: sorry for my confusing email... i'll try to clarify what i was asking: i have two tables (i shortened them to only show the parts relating to what i'm trying to do now) ... CREATE TABLE articles ( id smallint(4) unsigned zerofill NOT NULL auto_increment, title varchar(40) NOT NULL default '', public char(1) NOT NULL default 'y', author smallint(6) default NULL, editor smallint(6) default NULL, photog1 smallint(6) default NULL, photog2 smallint(6) default NULL, CREATE TABLE people ( id tinyint(3) unsigned NOT NULL auto_increment, name varchar(30) NOT NULL default '', Try this... SELECT title, a.name AS AuthorName, b.name AS EditorName, c.name AS Photog1Name, d.name AS Photog2Name FROM articles, people a, people b, people c people d WHERE author = a.id AND editor = b.id AND photog1 = c.id AND photog2 = d.id AND what ever else you want in the where clause; Rick -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] RE: regarding multiple joins
sorry for my confusing email... i'll try to clarify what i was asking: i have two tables (i shortened them to only show the parts relating to what i'm trying to do now) ... CREATE TABLE articles ( id smallint(4) unsigned zerofill NOT NULL auto_increment, title varchar(40) NOT NULL default '', public char(1) NOT NULL default 'y', author smallint(6) default NULL, editor smallint(6) default NULL, photog1 smallint(6) default NULL, photog2 smallint(6) default NULL, PRIMARY KEY (id), KEY author (author), KEY editor (editor), KEY photog1 (photog1), KEY photog2 (photog2), ) TYPE=MyISAM; CREATE TABLE people ( id tinyint(3) unsigned NOT NULL auto_increment, name varchar(30) NOT NULL default '', PRIMARY KEY (id), ) TYPE=MyISAM; those are my two tables. basically 'articles' holds all of the info relating to that article, and people just contains all the possible people that might be an author, editor, or photographer. when i display an article, it queries the database a total of 5 times. (once to retrieve all of the info from 'articles' and 4 more times to retrieve info from 'people.' i know that this isn't that efficient. i wish to use JOINs so that only 1 query would be made, and that the name of the author, editor, etc can be pulled up instead of just their index values (which i know query the database again to pull out the name). i have a feeling i would need a rather complex JOIN but i'm not sure how it looks exactly. any help would be appreciated. thank you very much. -jon -Original Message- From: David Elliott [mailto:[EMAIL PROTECTED]] Sent: Friday, November 22, 2002 2:18 AM To: Jonathan Narong on PHP-DB Subject: Re: regarding multiple joins -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Greetings Jonathan On 22 November 2002 at 01:55:10 -0800 (which was 09:55 where I live) Jonathan Narong graced us with these comments > i have an table 'articles' which has multiple fields, such as author, > editor, photographer, etc.. each of these fields contains an index number > which relates to another table 'people' that lists everyone. basically i > just want to select everything with the names displaying, instead of id > numbers. not sure exactly what you want. I have a table called country. It contains all ID's.The name of which is in an item table, so it has to use the item table a number of time so that it can display just names to the user and not ID's == One I made earlier = select a.ItemId CID ,b.name country ,c.name Currency ,d.name Region ,e.name VAT ,f.name Delivery from country a ,item b ,item c ,item d ,item e ,item f where a.itemid = b.itemid and a.CurrId = c.itemid and a.GeoRegId = d.itemid and a.VaTID = e.itemid and a.DelID = f.itemid order by f.name == One I made earlier = HTH - -- Best regards, ___ David |David Elliott| Software Engineer| _| [EMAIL PROTECTED] | PGP Key ID 0x650F4534 | | "It should be a while...he's very thorough." -- Trapper on Hawkeye | -BEGIN PGP SIGNATURE- Version: 6.5.8ckt http://www.ipgpp.com/ iQA+AwUBPd4EYvmK8eZlD0U0EQLBuwCWJZVbOfWxqmMd+4KgqKmIgRs3agCggA2g qzjRbhBT9szMn6EJI4lHZhE= =e0St -END PGP SIGNATURE- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: regarding multiple joins
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Greetings Jonathan On 22 November 2002 at 01:55:10 -0800 (which was 09:55 where I live) Jonathan Narong graced us with these comments > i have an table 'articles' which has multiple fields, such as author, > editor, photographer, etc.. each of these fields contains an index number > which relates to another table 'people' that lists everyone. basically i > just want to select everything with the names displaying, instead of id > numbers. not sure exactly what you want. I have a table called country. It contains all ID's.The name of which is in an item table, so it has to use the item table a number of time so that it can display just names to the user and not ID's == One I made earlier = select a.ItemId CID ,b.name country ,c.name Currency ,d.name Region ,e.name VAT ,f.name Delivery from country a ,item b ,item c ,item d ,item e ,item f where a.itemid = b.itemid and a.CurrId = c.itemid and a.GeoRegId = d.itemid and a.VaTID = e.itemid and a.DelID = f.itemid order by f.name == One I made earlier = HTH - -- Best regards, ___ David |David Elliott| Software Engineer| _| [EMAIL PROTECTED] | PGP Key ID 0x650F4534 | | "It should be a while...he's very thorough." -- Trapper on Hawkeye | -BEGIN PGP SIGNATURE- Version: 6.5.8ckt http://www.ipgpp.com/ iQA+AwUBPd4EYvmK8eZlD0U0EQLBuwCWJZVbOfWxqmMd+4KgqKmIgRs3agCggA2g qzjRbhBT9szMn6EJI4lHZhE= =e0St -END PGP SIGNATURE- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: regarding multiple joins
hi, you could do this with a join, but i experienced single querys to be faster if you always have only one person_id to look for in person. if it was many person_ids and your query would end like "...person_id in (1,2,3, 9998)" a join would by faster, I guess. feal free to correct me, if I'm wrong :-) btw: a join could look like this: SELECT * FROM article AS a LEFT JOIN person AS p1 ON a.author_id=p1.person_id LEFT JOIN person AS p2 ON a.editor_id=p2.person_id LEFT JO.. So with saving time() befor and after the query(s) you can do your own benchmarking regards, Bastain Jonathan Narong schrieb: > this is a problem i've been struggling with for a little while. any attempt > to find help online has not produced any useful information. this is > regarding joins, which i'm not an expert in, but hopefully one of you can > help me out. > > i have an table 'articles' which has multiple fields, such as author, > editor, photographer, etc.. each of these fields contains an index number > which relates to another table 'people' that lists everyone. basically i > just want to select everything with the names displaying, instead of id > numbers. > > the way i'm currently doing this is pretty inefficient; on the display page, > i have multiple queries. first to extract everything from the articles > table, and then a query for each field (i.e. SELECT name FROM authors WHERE > (author_id = $id). This works, although I'm pretty sure there's a much more > efficient way of doing this with one query. > > I know a method would be to have separate tables for authors, editors, > photographers, etc... but in this case, it would be simpler to just relate > everything to the one "people" table. THis is because any particular person > could take on multiple tasks, or different ones, for different articles. > > Hope this all makes sense. I have a feeling aliases will be need to used, > although I'm not exactly sure how. > > Thanks for any help you guys might be able to provide. ;-) > > -jon -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
