Jump to content
JessH

Trying to display data from two tables

Recommended Posts

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

Share this post


Link to post
Share on other sites

Something like

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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 by JessH

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.