从3个不同的表中选择项目

I would like to select items from 3 different tables, I am using UNION, but the number of columns is different, so it returns an error of number of columns, is there any other way using JOIN to get an appreciated result?

我想从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

EDITED: I add the tables:

编辑：我添加表格：

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 ;

EDITED

EDITED

I tried using LEFT JOIN but, it skips some fields:

我尝试使用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";

Thanks in advance

提前致谢

1 个解决方案

#1

List the columns you explicitly want. Provide NULLvalues for the rest:

列出您明确需要的列。为其余部分提供NULL值：

SELECT a.*, c.col1, c.col2
FROM `ads` a JOIN
      ad_car c
      ON a.id_ads = c.main_ad 
WHERE ads_cat = :ads_cat AND 
      a.ad_status = :adStatus 
UNION 
SELECT a.*, v.col1, NULL as col2
FROM ads a JOIN
     ad_vehpro v
     ON a.id_ads = v.main_ad 
WHERE ads_cat = :ads_cat AND 
      a.ad_status = :adStatus 
ORDER BY date_renewed DESC
LIMIT 4;

Note: If you know there are no duplicates between the two subqueries, use UNION ALL rather than UNION. UNION incurs the overhead of removing duplicates.

注意：如果您知道两个子查询之间没有重复项，请使用UNION ALL而不是UNION。 UNION会产生删除重复项的开销。

#1