homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

help with table joins

10+ Year Member

Msg#: 85 posted 8:00 pm on Oct 11, 2005 (gmt 0)

im joining multiple tables but now Ive run into a smal problem:
most tables contain a field where a keep track of who submitted the item, so it contains a user id (INT)
I join it with the users table on this id, naturally. But I also store who last modified the item and who approved it, so there are 3 fields that need to be joined basically with the users table to fetch the names of the users that go with them, all in one qyery would be nicest.

Ofcourse I could simply add 2 queries, but this page is already very heavy on the queries, around 20 or so, so I need to keep it to a minimum if possible :)

any ideas?

edit: mysql naturally ;)



WebmasterWorld Senior Member txbakers us a WebmasterWorld Top Contributor of All Time 10+ Year Member

Msg#: 85 posted 8:35 pm on Oct 11, 2005 (gmt 0)

if you already have 20 queries, two more won't hurt anything.


10+ Year Member

Msg#: 85 posted 9:08 pm on Oct 11, 2005 (gmt 0)



WebmasterWorld Senior Member 5+ Year Member

Msg#: 85 posted 10:34 pm on Oct 11, 2005 (gmt 0)

It can be done in one query using table aliasing.

For example if you have the following

a table called submission with fields
all id's found in the table user

and a table called user

Then you can use the following select, the bold items is where I'm aliasing the tables in the SQL statement.

select submitter.name,
-- add any other fields here
from submission,
user submitter,
user editor,
user approver
where submission.submitterid = submitter.id
and submission.lasteditid = editor.id
and submission.approverid = approver.id
-- add any other conditions to limit the selection

There is also another way to structure the above which is using the "inner/left outer join" type syntax, but that depends on whether it is suported by the version of SQL you are using.
Also you wil have to consider if one of the three id's in the submission table can be null.

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved