PDA

View Full Version : having trouble querying a table with two foreign key refering to the same column



mohtasham1983
July 24th, 2008, 02:47 AM
Hi,

I have a table called matchResult whose structure is like this:



CREATE TABLE `matchResult` (
`match_id` int(16) NOT NULL auto_increment,
`team1` int(3) NOT NULL,
`team2` int(3) NOT NULL,
PRIMARY KEY (`match_id`),
KEY `team1` (`team1`),
KEY `team2` (`team2`),
CONSTRAINT `matchResult_ibfk_1` FOREIGN KEY (`team1`) REFERENCES `teams` (`team_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `matchResult_ibfk_2` FOREIGN KEY (`team2`) REFERENCES `teams` (`team_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


As you can see, both team1 and team2 are foreign keys that refer to team_id in teams table.

The teams table structure is like this:



CREATE TABLE `teams` (
`team_id` int(3) NOT NULL auto_increment,
`team` varchar(60) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`team_id`)
) ENGINE=InnoDB AUTO_INCREMENT=112 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


I'm trying to do a join query so that I can see something like this:



match_id team1 team2
1 Arsenal Man U
2 Chelsea Milan


I used:


(select m.match_id,t.team from teams t,matchResult m
where m.team1=t.team_id) UNION (select m.match_id,t.team
from teams t,matchResult m where m.team2=t.team_id);


This lists everything in two columns and it's not what I'm looking for.

I'm wondering if it is possible to achieve what I'm looking for in mysql?

Thanks in advance,

pmasiar
July 24th, 2008, 04:55 AM
(select m.match_id,t.team from teams t,matchResult m
where m.team1=t.team_id) UNION (select m.match_id,t.team
from teams t,matchResult m where m.team2=t.team_id);



You want those two teams to be independent? So use different aliases for them



select m.match_id,t1.team t2.team
from teams t1, teams t2, matchResult m
where m.team1=t1.team_id and m.team2=t2.team_id;

mohtasham1983
July 24th, 2008, 05:05 AM
Thanks, I got it working.