Trying to display data from two tables

[JessH]

Been a looooong time since I have done any PHP/MySQL coding and I am pulling out the old toolbox and knocking 10 years of rust off of them. Even then I was mediocre at best.

 

Using MySQL Version   5.6.35

 

I have two Tables 'Members' and 'CheckIn'. Each table has a common data field called 'Callsign'.  The 'CheckIn' table also has a 'Status' field I want to retrieve

 

I want to be able to display all of the 'Callsign' records from 'Members' to display and have their 'Status' appear next to the Callsign. 

 

Here is the problem though. There may not be a matching 'Callsign' record in the Status.  which means the Callsign row from the 'Members' table would be empty

 

So Result would look something like this:

 

OPS-1 |  Active

OPS-2 |  Active

OPS-3 |                 <------- OPS-3 is not be present in the CheckIn table

OPS-4 | Active

 

*Note: Status.Callsign is not a unique field so the Callsign may be present multiple times in the 'Status' table.

 

I've tried various join statements and have even tried doing a nested "SELECT" statement but to no avail.

Anyone willing to help kncok the rust of an old tool

 

TIA

[Barand]

Something like

SELECT the, columns, you, want
FROM members
    LEFT JOIN checkin USING (callsign)

Edited March 8, 2017 by Barand

[JessH]

REALLY???  That simple???

 

I thought I even tried that syntax in my hour and a half of experimentation. Works like a Charm!!!!

 

Thanks Barand, I'm sure I'll be back with more questions

 

-Jess

[JessH]

Back with a follow up Question.

 

Initially it looked like the solution worked perfectly, until I started doing some testing. This is totally my fault as I forgot (or didn't think) about the fact that the 'Status' table may have numerous instances of 'Callsign"

 

While 'Members' Will have only one instance of 'Callsign, Not true with 'Status' Table.  So current result displays is as follows:

 

OPS-1  | Portable

OPS-2  | Active

OPS-3  | Portable

            | Released    <------- OPS-3 is in the 'Status' table twice

OPS-4  | Active

 

Any advice?

[JessH]

Upon Further experimentation I have found the solution, sort of.  Using PHPMyAdmin to play around with a SELECT statement I finally get the desired results. Now the problem I have is when I plug it in to the PHP page, I do not get the desired results.

 

Any and all help would be appreciated

 

$query = "SELECT SKY_Members.Callsign, NCO_CheckIn.*
FROM SKY_Members
LEFT JOIN NCO_CheckIn USING (callsign)
WHERE EVENT_ID IS NULL OR EVENT_ID <> '20170308ZZ'";

 

NOTE: The above script works in PHPMyAdmin but not on my .php page

Edited March 9, 2017 by JessH