Page 1 of 1

Help understanding query (JOIN's)

Posted: Fri Mar 06, 2009 8:33 am
by spamyboy
pickle | Please use [ code=php ], [ code=text ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.


I want to take record `dictionary`.`id` = 1, then get all `synonym`.`synonym` records that are related by `dictionary`.`id`=`synonym`.`word` and finally return result all `dictionary`.`word` with `id` of those related synonyms.

Code: Select all

CREATE TABLE IF NOT EXISTS `dictionary` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `word` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
 
INSERT INTO `dictionary` (`id`, `word`) VALUES
(1, 'masina'),
(2, 'stakles'),
(3, 'mechanizmas');
 
CREATE TABLE IF NOT EXISTS `synonyms` (
  `word` int(10) NOT NULL,
  `synonym` int(10) NOT NULL,
  UNIQUE KEY `word` (`word`,`synonym`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO `synonyms` (`word`, `synonym`) VALUES
(1, 2),
(1, 3);
Here is how I get `synonym`.`synonym`

Code: Select all

SELECT *
FROM `dictionary`
JOIN `synonyms`
ON `synonyms`.`word`=`dictionary`.`id`
WHERE `id` = 1;
Now by that returned `synonym`.`synonym` I need to get results from `dcitionary` by `dcitionary`.`id`
Any ideas?


pickle | Please use [ code=php ], [ code=text ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.