mysql - 连接两个没有键连接的表

Question

我需要进行如下查询

SELECT * from exp_channel_fields join exp_channel_titles ON  entry_id = 31558 and channel_id = 5

这样查询应该获取 channel 字段和值，其中值存储在另一个表中。

CREATE TABLE IF NOT EXISTS `exp_channel_fields` (
  `field_id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `site_id` int(4) unsigned NOT NULL DEFAULT '1',
  `group_id` int(4) unsigned NOT NULL,
  `field_name` varchar(32) NOT NULL,
  `field_label` varchar(50) NOT NULL,
  `field_instructions` text,
  `field_type` varchar(50) NOT NULL DEFAULT 'text',
  `field_list_items` text NOT NULL,
  `field_pre_populate` char(1) NOT NULL DEFAULT 'n',
  `field_pre_channel_id` int(6) unsigned DEFAULT NULL,
  `field_pre_field_id` int(6) unsigned DEFAULT NULL,
  `field_ta_rows` tinyint(2) DEFAULT '8',
  `field_maxl` smallint(3) DEFAULT NULL,
  `field_required` char(1) NOT NULL DEFAULT 'n',
  `field_text_direction` char(3) NOT NULL DEFAULT 'ltr',
  `field_search` char(1) NOT NULL DEFAULT 'n',
  `field_is_hidden` char(1) NOT NULL DEFAULT 'n',
  `field_fmt` varchar(40) NOT NULL DEFAULT 'xhtml',
  `field_show_fmt` char(1) NOT NULL DEFAULT 'y',
  `field_order` int(3) unsigned NOT NULL,
  `field_content_type` varchar(20) NOT NULL DEFAULT 'any',
  `field_settings` text,
  PRIMARY KEY (`field_id`),
  KEY `group_id` (`group_id`),
  KEY `field_type` (`field_type`),
  KEY `site_id` (`site_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=117 ;

CREATE TABLE IF NOT EXISTS `exp_channel_data` (
  `entry_id` int(10) unsigned NOT NULL,
  `site_id` int(4) unsigned NOT NULL DEFAULT '1',
  `channel_id` int(4) unsigned NOT NULL,
  `field_id_1` text,
  `field_ft_1` tinytext,
  `field_id_3` text,
  `field_ft_3` tinytext,
  `field_id_4` int(11) DEFAULT '0',
  `field_ft_4` tinytext,
  `field_id_5` text,
  `field_ft_5` tinytext,
  `field_id_6` text,
  `field_ft_6` tinytext,
  `field_id_7` text,
  `field_ft_7` tinytext,
  `field_id_8` text,
  `field_ft_8` tinytext,
  `field_id_10` text,
  `field_ft_10` tinytext,
  `field_id_11` text,
  `field_ft_11` tinytext,
  `field_id_12` text,
  `field_ft_12` tinytext,
  `field_id_15` text,
  `field_ft_15` tinytext,
  `field_id_16` text,
  `field_ft_16` tinytext,
  `field_id_18` text NOT NULL,
  `field_ft_18` tinytext,
  `field_id_19` text NOT NULL,
  `field_ft_19` tinytext,
  `field_id_20` text NOT NULL,
  `field_ft_20` tinytext,
  `field_id_21` text NOT NULL,
  `field_ft_21` tinytext,
  `field_id_22` int(10) DEFAULT NULL,
  `field_ft_22` tinytext,
  `field_dt_22` varchar(50) DEFAULT NULL,
  `field_id_23` text,
  `field_ft_23` tinytext,
  `field_id_24` text,
  `field_ft_24` tinytext,
  `field_id_25` text,
  `field_ft_25` tinytext,
  `field_id_26` text,
  `field_ft_26` tinytext,
  `field_id_27` text,
  `field_ft_27` tinytext,
  `field_id_28` text,
  `field_ft_28` tinytext,
  `field_id_29` text,
  `field_ft_29` tinytext,
  `field_id_30` text,
  `field_ft_30` tinytext,
  `field_id_31` text,
  `field_ft_31` tinytext,
  `field_id_32` text,
  `field_ft_32` tinytext,
  `field_id_33` text,
  `field_ft_33` tinytext,
  `field_id_34` text,
  `field_ft_34` tinytext,
  `field_id_35` text,
  `field_ft_35` tinytext,
  `field_id_36` text,
  `field_ft_36` tinytext,
  `field_id_37` text,
  `field_ft_37` tinytext,
  `field_id_38` text,
  `field_ft_38` tinytext,
  `field_id_39` text,
  `field_ft_39` tinytext,
  `field_id_40` text,
  `field_ft_40` tinytext,
  `field_id_41` text,
  `field_ft_41` tinytext,
  `field_id_42` text,
  `field_ft_42` tinytext,
  `field_id_43` text,
  `field_ft_43` tinytext,
  `field_id_44` text,
  `field_ft_44` tinytext,
  `field_id_46` text,
  `field_ft_46` tinytext,
  `field_id_47` text,
  `field_ft_47` tinytext,
  `field_id_48` text,
  `field_ft_48` tinytext,
  `field_id_49` text,
  `field_ft_49` tinytext,
  `field_id_50` text,
  `field_ft_50` tinytext,
  `field_id_51` text,
  `field_ft_51` tinytext,
  `field_id_52` text,
  `field_ft_52` tinytext,
  `field_id_53` text,
  `field_ft_53` tinytext,
  `field_id_54` text,
  `field_ft_54` tinytext,
  `field_id_55` text,
  `field_ft_55` tinytext,
  `field_id_56` text,
  `field_ft_56` tinytext,
  `field_id_57` text,
  `field_ft_57` tinytext,
  `field_id_58` text,
  `field_ft_58` tinytext,
  `field_id_62` text,
  `field_ft_62` tinytext,
  `field_id_63` text,
  `field_ft_63` tinytext,
  `field_id_64` text,
  `field_ft_64` tinytext,
  `field_id_65` int(11) DEFAULT '0',
  `field_ft_65` tinytext,
  `field_id_66` text,
  `field_ft_66` tinytext,
  `field_id_67` text,
  `field_ft_67` tinytext,
  `field_id_68` text,
  `field_ft_68` tinytext,
  `field_id_70` text,
  `field_ft_70` tinytext,
  `field_id_71` text,
  `field_ft_71` tinytext,
  `field_id_72` text,
  `field_ft_72` tinytext,
  `field_id_73` text NOT NULL,
  `field_ft_73` tinytext,
  `field_id_74` text NOT NULL,
  `field_ft_74` tinytext,
  `field_id_75` text NOT NULL,
  `field_ft_75` tinytext,
  `field_id_77` text NOT NULL,
  `field_ft_77` tinytext,
  `field_id_78` text,
  `field_ft_78` tinytext,
  `field_id_80` text NOT NULL,
  `field_ft_80` tinytext,
  `field_id_81` text NOT NULL,
  `field_ft_81` tinytext,
  `field_id_82` text NOT NULL,
  `field_ft_82` tinytext,
  `field_id_83` text NOT NULL,
  `field_ft_83` tinytext,
  `field_id_84` text,
  `field_ft_84` tinytext,
  `field_id_85` text,
  `field_ft_85` tinytext,
  `field_id_86` text,
  `field_ft_86` tinytext,
  `field_id_87` text,
  `field_ft_87` tinytext,
  `field_id_88` text,
  `field_ft_88` tinytext,
  `field_id_90` text,
  `field_ft_90` tinytext,
  `field_id_91` text,
  `field_ft_91` tinytext,
  `field_id_92` text,
  `field_ft_92` tinytext,
  `field_id_93` text,
  `field_ft_93` tinytext,
  `field_id_94` text,
  `field_ft_94` tinytext,
  `field_id_95` text,
  `field_ft_95` tinytext,
  `field_id_97` text,
  `field_ft_97` tinytext,
  `field_id_99` text,
  `field_ft_99` tinytext,
  `field_id_100` text,
  `field_ft_100` tinytext,
  `field_id_101` text,
  `field_ft_101` tinytext,
  `field_id_102` text,
  `field_ft_102` tinytext,
  `field_id_103` text,
  `field_ft_103` tinytext,
  `field_id_104` text,
  `field_ft_104` tinytext,
  `field_id_105` text,
  `field_ft_105` tinytext,
  `field_id_106` text,
  `field_ft_106` tinytext,
  `field_id_107` text,
  `field_ft_107` tinytext,
  `field_id_108` text,
  `field_ft_108` tinytext,
  `field_id_109` text,
  `field_ft_109` tinytext,
  `field_id_110` text,
  `field_ft_110` tinytext,
  `field_id_111` text,
  `field_ft_111` tinytext,
  `field_id_112` text,
  `field_ft_112` tinytext,
  `field_id_113` text,
  `field_ft_113` tinytext,
  `field_id_114` text,
  `field_ft_114` tinytext,
  `field_id_115` text,
  `field_ft_115` tinytext,
  `field_id_116` text,
  `field_ft_116` tinytext,
  PRIMARY KEY (`entry_id`),
  KEY `channel_id` (`channel_id`),
  KEY `site_id` (`site_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Answer 1

如果你想加入匹配的字段，只需使用

SELECT * from exp_channel_fields join exp_channel_titles ON  entry_id = channel_id;

如果字段不匹配(具有不同的值)，您可以像这样加入:

SELECT * from exp_channel_fields, exp_channel_titles WHERE entry_id = 31558 and channel_id = 5