Welcome to WebmasterWorld Guest from 107.20.110.201

Forum Moderators: open

Message Too Old, No Replies

help with table joins

     

dmmh

8:00 pm on Oct 11, 2005 (gmt 0)

10+ Year Member



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

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

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



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

dmmh

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

10+ Year Member



lol

Dijkgraaf

10:34 pm on Oct 11, 2005 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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.

 

Featured Threads

Hot Threads This Week

Hot Threads This Month