php - MYSQL 和 PHP 中的 JOIN 查询

Question

我要从 MYSQL 中的所有表生成特定信息。我的数据库中有 5 个表。即:

1. 广告商

   CREATE TABLE `advertiser` (   
       `Adv_id` int(11) NOT NULL AUTO_INCREMENT,   
       `Name` char(20) NOT NULL,   
       `F_Name` char(20) NOT NULL,   
       `Address` varchar(40) NOT NULL,   
       `CNIC` int(13) NOT NULL,   
       `Contact` int(11) NOT NULL,   
       `Monthly_fee` varchar(10) NOT NULL,   
       `Region` varchar(10) NOT NULL,   
       `Reg_date` varchar(10) NOT NULL,   
        PRIMARY KEY (`Adv_id`) 
   ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
   

2. 公司信息

   CREATE TABLE `company_information` (
       `Company_id` int(11) NOT NULL AUTO_INCREMENT,
       `Company_Name` varchar(20) NOT NULL,
       `Company_Contact` int(11) NOT NULL,
       `Company_Address` varchar(30) NOT NULL,
       PRIMARY KEY (`Company_id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   

3. 广告

   CREATE TABLE `advertisement` (
       `Ads_id` int(11) NOT NULL AUTO_INCREMENT,
       `Adv_id_id` int(11) NOT NULL,
       `Company_id` int(11) NOT NULL,
       `Ads_Title` varchar(20) NOT NULL,
       `Ads_Description` varchar(40) NOT NULL,
       `Ads_Image` varchar(50) NOT NULL,
        PRIMARY KEY (`Ads_id`),
        KEY `Adv_id` (`Adv_id_id`),
        KEY `Company_id` (`Company_id`),
        CONSTRAINT `Adv_id` FOREIGN KEY (`Adv_id_id`) REFERENCES `advertiser` (`Adv_id`) ON DELETE CASCADE ON UPDATE CASCADE,
        CONSTRAINT `advertisement_ibfk_1` FOREIGN KEY (`Company_id`) REFERENCES `company_information` (`Company_id`) ON DELETE CASCADE ON UPDATE CASCADE
   ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
   

4. 广告类型

   CREATE TABLE `ads_type` (
       `Ads_type_id` int(11) NOT NULL AUTO_INCREMENT,
       `Advertisement_id` int(11) NOT NULL,
       `Full_Channel_Ads` varchar(30) NOT NULL,
       `Logo_Ads` varchar(30) NOT NULL,
       `Com_Break_Ads` varchar(30) NOT NULL,
       PRIMARY KEY (`Ads_type_id`),
       KEY `Advertisement_id` (`Advertisement_id`),
       CONSTRAINT `Advertisement_id` FOREIGN KEY (`Advertisement_id`) REFERENCES `advertisement` (`Ads_id`) ON DELETE CASCADE ON UPDATE CASCADE
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   

5. 广告日期

   CREATE TABLE `ads_date` (
       `Ads_date_id` int(11) NOT NULL AUTO_INCREMENT,
       `Ads_id` int(11) NOT NULL,
       `Starting_Date` varchar(30) NOT NULL,
       `Expiry_Date` varchar(30) NOT NULL,
       PRIMARY KEY (`Ads_date_id`),
       KEY `Ads_id` (`Ads_id`),
       CONSTRAINT `Ads_id` FOREIGN KEY (`Ads_id`) REFERENCES `advertisement` (`Ads_id`) ON DELETE CASCADE ON UPDATE CASCADE
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   

我要找回

• 姓名、CNIN 和来自表 Advertiser 的联系方式
• Company_Name from company_information
• Ads_Title 来自广告
• Starting_Date 和 Expiry_Date 从 ads_date 开始。

此查询适用于上述行:

SELECT * 
    FROM ads_date a
    JOIN advertisement ci ON ci.Ads_id = a.Ads_id
    JOIN company_information ar ON ar.Company_id = ci.Company_id
    JOIN advertiser ad ON ad.Adv_id = ci.Advertiser_id
    WHERE Expiry_Date >= CURDATE()

问题:我还想从 ads_type 中检索 Advertisement_type。如何使用 JOIN 查询执行此操作？谁能解释一下？

Answer 1

您需要另一个加入:

SELECT * 
FROM   ads_date a
JOIN   advertisement ci ON ci.Ads_id = a.Ads_id
JOIN   company_information ar ON ar.Company_id = ci.Company_id
JOIN   advertiser ad ON ad.Adv_id = ci.Advertiser_id
JOIN   ads_type at ON  at.Advertisement_id = ci.Ads_id -- Here
WHERE  Expiry_Date >= CURDATE()