JOIN Query In MYSQL and PHP

2018-06-21 06:11:44

I am going to generate specific information from all the tables in MYSQL. I have 5 tables in my database. Ie:

advertiser

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;

company_information

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;

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;

ads_type

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;

ads_date

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;

I want to retrive

Name, CNIN, and Contact from table Advertiser

Company_Name from company_information

Ads_Title from Advertisement

Starting_Date and Expiry_Date From ads_date.

This Query work well for the above rows :

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()

Problem: I also want to retrive Advertisement_type from ads_type. how can I do this with a JOIN query? Can any one explain?

你需要另一个join ：

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()

请尝试以下查询：

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

    WHERE Expiry_Date >= CURDATE()

链接地址: http://www.djcxy.com/p/59702.html

上一篇: Web应用程序监控最佳实践

下一篇: 在MYSQL和PHP中加入查询