How do you cross-index tables in php/MySQL?

[dibbkd]

Here's what I have, two tables, "MWPAT" and "MWCAS", both have a unique key of "CHARTNUMBER".

The MWPAT table has patient name, address, phone, etc, and the MWCAS table has their case information, like insurance, guarantor, etc...

I want to print a list out that contains information from both tables, i.e.:

Joe Smith 910-822-2384 Cigna
Amy Dotts 910-488-9283 Medicaid

Any suggestions?

[Lars_Berg]

Why two tables?

select * from MWPAT natural join MWCAS

Does all patients occur in both tables? Depending on this you may need an outer join.

[dibbkd]

Thanks for the quick response Lars. There are two reasons I have two separate tables. The first is that it's a one to many relationship, each Patient can have multiple Cases (mwpat vs. mwcas).
The second reason is that I'm actually retrieving this data via ODBC driver from another a "Case Management" application called Medisoft, and they have it broke down into multiple tables.

I used the "select * from MWPAT natural join MWCAS" query you suggested, but I'm still not getting the output from the MWCAS table.

In the code below, this line:
$localb=mysql_result($result,$i,"localb");

is the line that gets info from the MWCAS table "localb", the other fields are from MWPAT.

Here's what my while statement looks like:

while ($i < $num)
{
$firstname=mysql_result($result,$i,"firstname");
$lastname=mysql_result($result,$i,"lastname");
$ssn=mysql_result($result,$i,"ssn");
$healthid=mysql_result($result,$i,"healthid");

$localb=mysql_result($result,$i,"localb");

$nummed++;
echo "
      <tr>
      <td>$nummed</td>		
      <td>$firstname $lastname</td>
      <td></td>
      <td>$ssn</td>
      <td></td>
      <td>$healthid</td>
      <td>$localb</td>
      </tr>";
echo "<tr><td colspan='9'><hr></td></tr>";
$i++;

}