How to use inner join in select with mysql?

Question

I want to fetch record from mysql database on following condition

I have the following tables:

• organization_ Table

  organizationId | organizationname
  

• campaign Table

  camp_id | camp_name | adv_id | organizationId
  

• advertise Table

  adv_id | adv_name
  

• ad_display Table

  ad_displayId | adv_id | camp_id |
  

Now from ad_display table I want to fetch record like this:

| adv_name | camp_name | organizationname

With WHERE Condtion as Where organizationId == ?

So basically I want the advertise list of campaign where organization_id=?

Note: there should be multiple entry for same adv_id in camapign table and same camp_id with multiple entry in ad_display

Answer 1

Try this:

SELECT ad.ad_displayId, a.adv_name, c.camp_name, o.organizationname 
FROM ad_display ad 
INNER JOIN advertise a ON ad.adv_id = a.adv_id 
INNER JOIN campaign c ON ad.camp_id = c.camp_id 
INNER JOIN organization o ON c.organizationId = o.organizationId 
WHERE o.organizationId = ?;

Answer 2

Maybe something like this?

SELECT Distinct
  advertise.adv_name,
  campaign.camp_name,
  organization_.organizationname
FROM
  advertise inner join campaign
  on advertise.adv_id = campaign.adv_id
  inner join organization_
  on campaign.organizationID = organization_.organizationID
WHERE
  organization_.organizationID = 1

Answer 3

SELECT  advertise.adv_name, 
        campaign.camp_name, 
        organization_Table.organizationname
FROM   ad_display 
    INNER JOIN advertise ON ad_display.adv_id = advertise.adv_id 
    INNER JOIN campaign ON ad_display.camp_id = campaign.camp_id 
    INNER JOIN organization_ Table ON campaign.organizationId = organization_Table.organizationId
    AND organization_Table.organizationId=?