MySQL Forums
Forum List  »  Newbie

JOIN 2 different records from the same table?
Posted by: Peter Helles
Date: April 21, 2005 11:28PM

Hello
I've been trying to get my sql statement to JOIN two tables.
These are my 2 tables:

CREATE TABLE `dev_transaction`
(`transaction_id` INT(4) NOT NULL AUTO_INCREMENT,
`transaction_name` VARCHAR(100),
`transaction_information` TEXT,
`transaction_auther_resource_id` INT(11),
`transaction_assignedto_resource_id` INT(11),
`transaction_date` DATE,
`transaction_date_updated` DATE,
PRIMARY KEY (`transaction_id`))
TYPE=MyISAM ROW_FORMAT=DYNAMIC
PACK_KEYS=0 CHECKSUM=0 DELAY_KEY_WRITE=0 MIN_ROWS=28 MAX_ROWS=34 AVG_ROW_LENGTH=198 AUTO_INCREMENT=107

CREATE TABLE `dev_resources`
(`resources_id` INT(4) NOT NULL DEFAULT '0',
`resources_Name` CHAR(20),
`resources_Email` CHAR(30),
UNIQUE KEY `resource_id` (`resources_id`),
UNIQUE KEY `resources_id` (`resources_id`))
TYPE=MyISAM ROW_FORMAT=FIXED
PACK_KEYS=0 CHECKSUM=0 DELAY_KEY_WRITE=0 MIN_ROWS=29 MAX_ROWS=29 AVG_ROW_LENGTH=66

So the first table named "dev_transaction" should be linked to the second table named "dev_resources" on 2 records which are "transaction_auther_resource_id" and "transaction_assignedto_resource_id" which both link to the "resources_id" field in the "dev_resources" table.
Now I have to get my sql statement to return all the fields from the first table with the name of the resources in the second table joined in, but the problem is that the "transaction_auther_resource_id" and the "transaction_assignedto_resource_id" field link to different records in the "dev_resources" talbe. Can anybody crack that nut for me please?

I tried this:

SELECT dev_transaction.*, dev_resources.resources_name
FROM dev_transaction
LEFT OUTER JOIN dev_resources ON (dev_transaction.transaction_auther_resource_id = dev_resources.resources_id)
LEFT OUTER JOIN dev_resources ON (dev_transaction.transaction_assignedto_resource_id = dev_resources.resources_id)
;

But it doesn't work because apparently you can only JOIN a table once I think.
Any help is most appreciated. Cheers

Peter

Options: ReplyQuote


Subject
Written By
Posted
JOIN 2 different records from the same table?
April 21, 2005 11:28PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.