Inner Join 2 tables to 3 tables -Add another table

14th August 2014

MySql

Best way to combine 3 tables data together I have found is the following script.


$result = mysql_query("SELECT *
FROM visit
JOIN login ON (login.id = visit.visitby)
JOIN residents ON (residents.id = visit.resid)");

$result = mysql_query("SELECT *

FROM visit

JOIN login ON (login.id = visit.visitby)

JOIN residents ON (residents.id = visit.resid)");

Before you do it the hard way try the easy way…just add a WHERE CLAUSE


SELECT `login`.`fname` , `residents`.`fname` , `visit` . *
FROM login, visit, residents
LIMIT 0 , 30

SELECT `login`.`fname` , `residents`.`fname` , `visit` . *

FROM login, visit, residents

LIMIT 0 , 30

This SQL joins two tables together where the campaign id is the same.

If the results back are many, then I may want to check another table to see if something is true.

We can add another join statement “INNER JOIN table3 ON table3.campaignid = table1.campaignid” as long as it has the campaignid in it also. Then we just add the statement “AND table3.impressions_month != 0”

We could join many tables like this…….

ORIGINAL VERSION (Two tables)


$result2 = mysql_query("SELECT * FROM table1
INNER JOIN ( SELECT MAX(table1.id) AS id FROM table1
INNER JOIN table2 ON table2.campaignid = table1.campaignid
WHERE table1.date='$thisday'
AND table1.client ='$boardname'
AND table2.enabled != 'disabled'
GROUP BY table1.campaign )
AS m ON m.id = table1.id
order by table1.campaign ASC
");

$result2 = mysql_query("SELECT * FROM table1

INNER JOIN ( SELECT MAX(table1.id) AS id FROM table1

INNER JOIN table2 ON table2.campaignid = table1.campaignid

WHERE table1.date='$thisday'

AND table1.client ='$boardname'

AND table2.enabled != 'disabled'

GROUP BY table1.campaign )

AS m ON m.id = table1.id

order by table1.campaign ASC

");

UPDATED VERSION (three tables)


$result2 = mysql_query("SELECT * FROM table1
INNER JOIN ( SELECT MAX(table1.id) AS id FROM table1
INNER JOIN table2 ON table2.campaignid = table1.campaignid
INNER JOIN table3 ON table3.campaignid = table1.campaignid
WHERE table1.date='$thisday'
AND table1.client ='$boardname'
AND table2.enabled != 'disabled'
AND table3.impressions_month != 0
GROUP BY table1.campaignid )
AS m ON m.id = table1.id
order by table1.campaign ASC
");

$result2 = mysql_query("SELECT * FROM table1

INNER JOIN ( SELECT MAX(table1.id) AS id FROM table1

INNER JOIN table2 ON table2.campaignid = table1.campaignid

INNER JOIN table3 ON table3.campaignid = table1.campaignid

WHERE table1.date='$thisday'

AND table1.client ='$boardname'

AND table2.enabled != 'disabled'

AND table3.impressions_month != 0

GROUP BY table1.campaignid )

AS m ON m.id = table1.id

order by table1.campaign ASC

");

Another more full example


// t1="messages";
// t2="workers";
// t3="jobs";


$sql = "SELECT * FROM messages
INNER JOIN ( SELECT 
    messages.id AS id, 
    workers.fname AS fname,
    workers.sname AS sname,
    jobs.jobname AS jobname,
    jobs.refnumber AS jobref
FROM messages
INNER JOIN workers ON workers.id = messages.workerid
INNER JOIN jobs ON jobs.refnumber = messages.jobref)
AS m ON m.id = messages.id
";


// $sql = "SELECT * FROM messages JOIN workers ON messages.workerid = workers.id";
if(!$result = $mysqli->query($sql)){
    die('There was an error running the query [' . $mysqli->error . ']');
}
 
while($row = $result->fetch_assoc()){
    $message[] = $row['message'];
    $fname[] = $row['fname'];
    $sname[] = $row['sname'];
    $time[] = $row['time'];
    $jobname[] = $row['jobname'];
    $jobref[] = $row['jobref'];
}

// t1="messages";

// t2="workers";

// t3="jobs";

$sql = "SELECT * FROM messages

INNER JOIN ( SELECT

messages.id AS id,

workers.fname AS fname,

workers.sname AS sname,

jobs.jobname AS jobname,

jobs.refnumber AS jobref

FROM messages

INNER JOIN workers ON workers.id = messages.workerid

INNER JOIN jobs ON jobs.refnumber = messages.jobref)

AS m ON m.id = messages.id

// $sql = "SELECT * FROM messages JOIN workers ON messages.workerid = workers.id";

if(!$result = $mysqli->query($sql)){

die('There was an error running the query [' . $mysqli->error . ']');

}

while($row = $result->fetch_assoc()){

$message[] = $row['message'];

$fname[] = $row['fname'];

$sname[] = $row['sname'];

$time[] = $row['time'];

$jobname[] = $row['jobname'];

$jobref[] = $row['jobref'];

}

If you have a fname in both tables you are trying to get results from, you can do this….


$resulth = mysql_query("SELECT *, login.fname AS myfname
FROM visit
JOIN login ON (login.id = visit.visitby)
JOIN residents ON (residents.id = visit.resid) order by visit.id desc limit 10");

//then echo out the 'AS'
echo $row['myfname']