我想从3个不同的表中选择项目,我正在使用 UNION
,但列数不同,因此返回列数错误,是否有其他方法使用join来获得满意的结果?
SELECT *
FROM `ads` JOIN ad_car
ON ads.id_ads = ad_car.main_ad
WHERE ads_cat = :ads_cat AND
ads.ad_status = :adStatus
UNION
SELECT *
FROM `ads` JOIN ad_vehpro
ON ads.id_ads = ad_vehpro.main_ad
WHERE ads_cat = :ads_cat AND
ads.ad_status = :adStatus
ORDER BY date_renewed
DESC LIMIT 4
编辑:我添加表格:
CREATE TABLE IF NOT EXISTS `ads` (
`id_ads` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_profiles_key` varchar(20) NOT NULL,
`id_shops` int(4) NOT NULL DEFAULT '1',
`ads_cat` int(2) NOT NULL COMMENT 'main category: vehicule, immobilier,...',
`ads_scat` int(3) NOT NULL COMMENT 'sous cat: voiture, moto,...',
`form_id` varchar(30) NOT NULL COMMENT 'form key',
`city_id` int(5) NOT NULL,
`district_id` int(5) NOT NULL,
`adtype_id` int(1) NOT NULL DEFAULT '1' COMMENT '1: offre, 2: demande',
`ads_title` varchar(200) NOT NULL,
`ads_description` longtext NOT NULL,
`ads_price` int(11) NOT NULL,
`num_img` int(2) NOT NULL DEFAULT '1' COMMENT 'number of images',
`num_vid` int(2) NOT NULL DEFAULT '0' COMMENT 'number of video',
`num_com` int(3) NOT NULL DEFAULT '0' COMMENT 'number of comments',
`to_pin` int(1) NOT NULL DEFAULT '0' COMMENT 'ad that will be pinned. 0: no, 1: yes',
`date_inserted` varchar(20) NOT NULL,
`date_modified` varchar(20) NOT NULL,
`date_renewed` varchar(20) NOT NULL,
`date_pinned` varchar(20) NOT NULL DEFAULT '0',
`ad_status` int(1) NOT NULL DEFAULT '1' COMMENT '0: not active, 1: active, 2: deleted, 3: pinned',
PRIMARY KEY (`id_ads`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;
CREATE TABLE IF NOT EXISTS `ad_car` (
`id_ad` int(10) unsigned NOT NULL AUTO_INCREMENT,
`main_ad` int(5) NOT NULL COMMENT 'this will refer to main table ads (id_ads)',
`id_profiles` varchar(20) NOT NULL,
`city_id` int(5) NOT NULL,
`district_id` int(5) NOT NULL COMMENT 'district id from districts table',
`category_id` int(2) NOT NULL,
`make_id` int(5) NOT NULL,
`model_id` int(5) NOT NULL,
`model_year` int(4) NOT NULL,
`engine` int(2) NOT NULL,
`fuel` int(1) NOT NULL,
`mileage` bigint(7) NOT NULL,
`transmission` int(1) NOT NULL,
`wheed_drive` int(1) NOT NULL,
`in_color` varchar(7) NOT NULL,
`ex_color` varchar(7) NOT NULL,
`seats` int(2) NOT NULL,
`doors` int(1) NOT NULL,
`tax` varchar(5) NOT NULL,
`warranty` int(1) NOT NULL,
`hands` int(1) NOT NULL,
`security` longtext NOT NULL,
`comfort` longtext NOT NULL,
`aesthetic` longtext NOT NULL,
`adreference` varchar(10) NOT NULL,
`ad_price` bigint(20) NOT NULL,
`ad_priceneg` int(1) NOT NULL,
`when_inserted` varchar(20) NOT NULL,
`when_modified` varchar(20) NOT NULL,
`ad_status` int(1) NOT NULL DEFAULT '1' COMMENT '0: not active, 1: active, 2: deleted',
`not_listed` longtext NOT NULL,
PRIMARY KEY (`id_ad`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;
CREATE TABLE IF NOT EXISTS `ad_vehpro` (
`id_ad` int(10) unsigned NOT NULL AUTO_INCREMENT,
`main_ad` int(5) NOT NULL COMMENT 'this will refer to main table ads (id_ads)',
`id_profiles` varchar(20) NOT NULL,
`city_id` int(5) NOT NULL,
`district_id` int(5) NOT NULL COMMENT 'district id from districts table',
`category_id` int(2) NOT NULL,
`vehpro_type` int(1) NOT NULL,
`make_id` int(5) NOT NULL,
`model_id` int(5) NOT NULL,
`model_year` int(4) NOT NULL,
`engine` int(2) NOT NULL,
`fuel` int(1) NOT NULL,
`mileage` bigint(7) NOT NULL,
`transmission` int(1) NOT NULL,
`wheed_drive` int(1) NOT NULL,
`in_color` varchar(7) NOT NULL,
`ex_color` varchar(7) NOT NULL,
`seats` int(2) NOT NULL,
`doors` int(1) NOT NULL,
`tax` varchar(5) NOT NULL,
`warranty` int(1) NOT NULL,
`hands` int(1) NOT NULL,
`security` longtext NOT NULL,
`comfort` longtext NOT NULL,
`aesthetic` longtext NOT NULL,
`adreference` varchar(10) NOT NULL,
`ad_price` bigint(20) NOT NULL,
`ad_priceneg` int(1) NOT NULL,
`when_inserted` varchar(20) NOT NULL,
`when_modified` varchar(20) NOT NULL,
`ad_status` int(1) NOT NULL DEFAULT '1' COMMENT '0: not active, 1: active, 2: deleted',
`not_listed` longtext NOT NULL,
PRIMARY KEY (`id_ad`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
编辑
我尝试使用left join,但它跳过了一些字段:
$sqlAds = "SELECT * FROM `ads`"
. " LEFT JOIN ad_car ON ads.id_ads = ad_car.main_ad"
. " LEFT JOIN ad_vehpro ON ads.id_ads = ad_vehpro.main_ad"
. " WHERE ads_cat = :ads_cat AND ads.ad_status = :adStatus ORDER BY date_renewed DESC";
提前谢谢
1条答案
按热度按时间mznpcxlj1#
列出明确需要的列。提供
NULL
其余值:注意:如果您知道两个子查询之间没有重复项,请使用
UNION ALL
而不是UNION
.UNION
会产生删除重复项的开销。