Table Join Query displays data from one table only

Chavista · October 26, 2012, 2:04pm

Imagine a database table - let’s call it Table1 - that contains animal taxa displayed in a parent-child relationship, like this:

Felidae (Family)
> Panthera (Genus)
>> Lion (Species)
>> Tiger (Species)
> Lynx (Genus)
>> Lynx (Species)
>> Bobcat (Species)

For example, the genus Lynx is the parent of the species lynx and bobcat, the genus Panthera is the parent of the species lion and tiger and the cat family (Felidae) is the parent of Panthera and Lynx.

I have such a database table working on my computer; I can display a variety of information about animals on a website I’m working on. However, I decided to create a series of tables that will include a new taxonomic rank - SUBFAMILIES.

I want to display a table that looks something like this:

Big Cats (Subfamily)
>> Lion (Species)
>> Tiger (Species)
Small Cats (Subfamily)
>> Lynx (Species)
>> Bobcat (Species)

Notice that I’m displaying two taxonomic ranks - subfamily and species, but NOT the intermediate rank, genus.

So I’m trying to figure out how to do that. The obvious solution is to add the subfamilies to my database table. However, that creates some problems, so I’d like to put them in a separate dartabase table. So I simply copied Table1, deleted all the species names and added the subfamilies. I had to keep the genera in order to establish a parent-child relationship with the subfamilies. So, once again, here’s a snapshot of Table1, followed by Table2…

TABLE1
Felidae (Family)
> Panthera (Genus)
>> Lion (Species)
>> Tiger (Species)
> Lynx (Genus)
>> Lynx (Species)
>> Bobcat (Species)

TABLE2
Felidae (Family)
> Big Cats (Subfamily)
>> Panthera (Genus)
> Small Cats (Subfamily)
>> Lynx (Genus)

Now I want to somehow join these tables so I can display the following:

Big Cats (Subfamily)
>> Lion (Species)
>> Tiger (Species)
Small Cats (Subfamily)
>> Lynx (Species)
>> Bobcat (Species)

The following code, where $MyURL = ‘Felidae’ (The URL is MySite/Life/Felidae) works to a point, but it only displays data from Table2.


$Children = mysql_query ("SELECT L.N, L.Taxon, L.Parent, L.MyFam, L.Rank, P.N, P.Taxon2, P.Parent, P.MyFam, P.Rank
 FROM gz_life AS L
 RIGHT JOIN gz_parataxa AS P on P.Taxon2 = L.Taxon
 WHERE P.MyFam = '$MyURL'
 Order By P.N, P.Rank");

In other words, it displays subfamilies and genera. I want it to 1) continue displaying subfamilies, 2) NOT display genera and 3) display species names from Table1.

I’ve tried several variations, including left join instead of right join. Also, I’m not sure if I have to display the results in one row…


echo <<<EOD
   <tr>
     <td>$Taxon</td>
   </tr>
EOD;

…or two, with one row to display the subfamilies from Table2 and a second row to display the species names from Table1:


echo <<<EOD
   <tr>
     <td>$Taxon</td>
   </tr>
   <tr>
     <td>$Species</td>
   </tr>
EOD;

Sorry for the long, convoluted explanation, and thanks for any tips.

DarthGuido · October 26, 2012, 2:32pm

Could you please post the test data in the two tables with the correct table and column names? Right now I don’t see much connection between the “table” data (which isn’t the table data but how you want to display the data) and the query.

Chavista · October 26, 2012, 10:15pm

The two tables are nearly identical in structure, with the following column names - N Taxon Parent MyFam MyOrd MyCla Rank

However, I renamed column Taxon to Taxon2 in Table2 (actually gz_parataxa). Each table has the same data except that Table1 (gz_life) lists species but not subfamilies, while gz_parataxa lists subfamilies but not species.

I use Table1 - gz_life to display pages, where a page’s URL equals a value in the column Taxon. For example, MySite/Life/Felidae is a dynamic page produced by the value Felidae. I created the second table - gz_parataxa - so I could display subfamily names.

The column “Parent” is self-explanatory. The columns MyFam, MyOrd and MyCla display each taxon’s family (e.g. Felidae), order (e.g. Carnivora) and class (e.g. mam = mammals). It makes it easier for me to select all values belonging to a particular animal family, order or class.

Thanks!