homepage Welcome to WebmasterWorld Guest from 54.167.238.60
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, Moderators: physics

Databases Forum

    
help with table joins
dmmh




msg:1580134
 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 ;)

 

txbakers




msg:1580135
 8:35 pm on Oct 11, 2005 (gmt 0)

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

dmmh




msg:1580136
 9:08 pm on Oct 11, 2005 (gmt 0)

lol

Dijkgraaf




msg:1580137
 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
submitterid
lasteditid
approverid
all id's found in the table user

and a table called user
id
name

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

select submitter.name,
editor.name
approver.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