CODEWITHSUNDEEP
phpmysqljoin
Andrew Fox
Andrew Fox

Reputation: 890

How do I `JOIN` these tables properly?

I have a table being used to store aircraft inspection schedules for the current week, it is called aircraft_sched. There are two others that are relevant, one is called aircraft_sched_options which I need to JOIN to the first table, and the last table is called aircraft.

aircraft_sched:

column 1: AC_Reg (VARCHAR)(10),(PK),(FK -> `aircraft` PK)
column 2: Sched_Day1 (INT)(1),(FK -> `aircraft_sched_options` PK)
column 3: Sched_Day2 (INT)(1),(FK -> `aircraft_sched_options` PK)
column 4: Sched_Day3 (INT)(1),(FK -> `aircraft_sched_options` PK)
column 5: Sched_Day4 (INT)(1),(FK -> `aircraft_sched_options` PK)
column 6: Sched_Day5 (INT)(1),(FK -> `aircraft_sched_options` PK)
column 7: Sched_Day6 (INT)(1),(FK -> `aircraft_sched_options` PK)
column 8: Sched_Day7 (INT)(1),(FK -> `aircraft_sched_options` PK)

aircraft_sched_options:

column 1: SchedOpt_ID (INT)(1),(PK)
column 2: SchedOpt_Name (VARCHAR)(10)
column 3: SchedOpt_Color (VARCHAR)(7),

aircraft

column 1: AC_Reg (VARCHAR)(10),(PK)
column 2: AC_SN  (VARCHAR)(6)
column 3: AC_Year (VARCHAR)(4)

When a new aircraft is added to the system, I have it so it also adds it to the aircraft_sched table. I don't think this is the right way, but that's how it is right now. So the aircraft_sched table is always populated with the AC_Reg and the Sched_DayX cell is either a 0 for NULL or a SchedOpt_ID number corresponding to the schedule type selected.

The problem I'm facing is when I try to JOIN the Sched_DayX column to the SchedOpt_ID column. It sort of works when I only JOIN one column, but if I try to do more than one, then the row just disappears from my results.

Here is my code that "sort of" works:

<?php
$sql = ("
          SELECT 
             * 
          FROM 
             aircraft_sched 
          INNER JOIN aircraft_sched_options AS aso1 
             ON aircraft_sched.Sched_Day1 = aso1.SchedOpt_ID
");

if(!$result_sql = $mysqli->query($sql))
{
    echo QueryCheck("getting the aircraft schedule ","from the aircraft sched",$mysqli) . "Error No: " .$mysqli->errno;
}

while($ACSched = $result_sql->fetch_assoc())
{
    echo "<tr>";
    echo "<td class=\"ACSched_Reg\">" . $ACSched['AC_Reg'] . "</td>";
    echo "<td align=\"center\" style=\"background:" . $ACSched['SchedOpt_Color'] . ";\">" . $ACSched['SchedOpt_Name'] . "</td>";
    echo "<td align=\"center\" style=\"background:" . $ACSched['SchedOpt_Color'] . ";\">" . $ACSched['SchedOpt_Name'] . "</td>";
    echo "<td align=\"center\" style=\"background:" . $ACSched['SchedOpt_Color'] . ";\">" . $ACSched['SchedOpt_Name'] . "</td>";
    echo "<td align=\"center\" style=\"background:" . $ACSched['SchedOpt_Color'] . ";\">" . $ACSched['SchedOpt_Name'] . "</td>";
    echo "<td align=\"center\" style=\"background:" . $ACSched['SchedOpt_Color'] . ";\">" . $ACSched['SchedOpt_Name'] . "</td>";
    echo "<td align=\"center\" style=\"background:" . $ACSched['SchedOpt_Color'] . ";\">" . $ACSched['SchedOpt_Name'] . "</td>";
    echo "<td align=\"center\" style=\"background:" . $ACSched['SchedOpt_Color'] . ";\">" . $ACSched['SchedOpt_Name'] . "</td>";
    echo "</tr>";
}
?>

When I say it "sort of" works, I mean that it actually displays something as a result. The reason it doesn't work is because it shows the same result in each cell even if there is only one day with a schedule type assigned.

When I add the second JOIN to the query, like this:

$sql = ("
          SELECT 
            * 
          FROM 
            aircraft_sched 
          INNER JOIN aircraft_sched_options AS aso1 
            ON aircraft_sched.Sched_Day1 = aso1.SchedOpt_ID
          INNER JOIN aircraft_sched_options AS aso2 
            ON aircraft_sched.Sched_Day2 = aso2.SchedOpt_ID
 ");

...then it just doesn't return any rows which have a record.

I have been reading on JOINs and how they work and the only way I can think of to fix the problem is to have a separate table for each AC_Reg so that each day of the week can be set to a UNIQUE column, but I don't believe that is the best way to accomplish the task.

EDIT:

Here are some screen shots to give a better visual.

aircraft_sched:

aircraft_sched

aircraft_sched_options:

aircraft_sched_options

My Code:

php code

Screen Display:

what displays on the screen

Upvotes: 0

Views: 112

Answers (2)

Stephan
Stephan

Reputation: 8090

If i understood your question correctly you need a join with table tbl_two for each column Day:

SELECT 
    aircraft_sched.AC_SN,
    IF(
       aso1.SchedOpt_Name IS NULL OR aso1.SchedOpt_ID = 0, 
       '-', 
       aso1.SchedOpt_Name
    ) as option1,
    IF(
       aso2.SchedOpt_Name IS NULL OR aso2.SchedOpt_ID = 0, 
       '-', 
       aso2.SchedOpt_Name
    ) as option2
FROM 
    aircraft_sched 
LEFT JOIN aircraft_sched_options AS aso1 
    ON aircraft_sched.Sched_Day1 = aso1.SchedOpt_ID
LEFT JOIN aircraft_sched_options AS aso2 
    ON aircraft_sched.Sched_Day2 = aso2.SchedOpt_ID

....

EDIT: I've updated the query and used LEFT JOIN instead of INNER JOIN to get the options because as you said some might be NULL

UPDATE : removed the join with aircraft and added check if option is null or id is 0 the - will be displayed

Upvotes: 1

Ken Cheung
Ken Cheung

Reputation: 1808

Follow the logic of your query, only rows with Day1 == Day2 == Day3 == ... == Day7 in tbl_one AND the value also equal to tbl_two.Day_ID should show up, can this help?

SELECT * FROM tbl_one INNER JOIN tbl_two
    ON tbl_one.Day1 = tbl_two.Day_ID 
WHERE 
      tbl_one.Day1 = tbl_one.Day2
  AND tbl_one.Day1 = tbl_one.Day3
  AND tbl_one.Day1 = tbl_one.Day4
  AND tbl_one.Day1 = tbl_one.Day5
  AND tbl_one.Day1 = tbl_one.Day6
  AND tbl_one.Day1 = tbl_one.Day7

Upvotes: 0

Related Questions