从MySQL中的4个表中选择数据

Question

对于MySQL语句，我有一个问题：

  SELECT 
      oxarticles.OXTITLE AS TITLE, 
      oxmanufacturers.OXTITLE_1 AS OXMANTITLE,
      oxarticles.OXDISTEAN AS OXDISTEAN,
      oxarticles.OXMPN AS MPN,
      oxarticles.OXPRICE AS OXPRICE,
      oxarticles.OXSTOCK AS OXSTOCK, 
      oxarticles.OXARTNUM AS OXARTNUM, 
      oxseo.OXSEOURL AS OXSEOURL, 
      oxartextends.OXLONGDESC_1 AS OXLONGDESC 
  FROM `oxarticles` 
    INNER JOIN `oxartextends` ON oxarticles.OXID = oxartextends.OXID 
    INNER JOIN `oxmanufacturers` ON oxarticles.OXID = oxmanufacturers.OXID 
    INNER JOIN `oxseo` ON oxarticles.OXID = oxseo.OXOBJECTID;

我的问题是表oxarticles和oxmanufacturers有两个相同的列名OXID和OXTITLE_1，但是上面的代码不起作用。请帮帮忙。

Answer 1

您正在尝试将商品id与制造商id匹配。这是错误的。您需要使用制造商id (而不是项目id )将您的项目表加入到制造商表。

在您的示例中，这是oxarticles.OXMANUFACTURERID，表示产品表中的制造商id和制造商表中的oxmanufacturers.OXID。

SELECT 
      oxarticles.OXTITLE AS TITLE, 
      oxmanufacturers.OXTITLE_1 AS OXMANTITLE,
      oxarticles.OXDISTEAN AS OXDISTEAN,
      oxarticles.OXMPN AS MPN,
      oxarticles.OXPRICE AS OXPRICE,
      oxarticles.OXSTOCK AS OXSTOCK, 
      oxarticles.OXARTNUM AS OXARTNUM, 
      oxseo.OXSEOURL AS OXSEOURL, 
      oxartextends.OXLONGDESC_1 AS OXLONGDESC 
  FROM `oxarticles` 
    INNER JOIN `oxartextends` 
      ON oxarticles.OXID = oxartextends.OXID 
    INNER JOIN `oxmanufacturers` 
      ON oxarticles.OXMANUFACTURERID = oxmanufacturers.OXID 
--                                             ^^^
--                                             Here's the manufacturer id 
--                                             in the article table

    INNER JOIN `oxseo` 
      ON oxarticles.OXID = oxseo.OXOBJECTID;