MySQL Forums :: Newbie :: JOIN 2 different records from the same table?
JOIN 2 different records from the same table?
Posted by: Peter Helles
Date: April 21, 2005 11:28PM
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,
PRIMARY KEY (`transaction_id`))
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',
UNIQUE KEY `resource_id` (`resources_id`),
UNIQUE KEY `resources_id` (`resources_id`))
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
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
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.