Is this possible? If so, how do I structure my SELECT statement to achieve these results?
I am creating an HTML table in a PHP file that gets various rows from a MySQL database. This is my query:
// Define the query:
$query = "SELECT b.birdID, b.nameGeneral, b.nameSpecific,
b.genus, b.species, b.family, b.populationTrend, f.food, h.habitat
FROM bird_habitat AS bh
JOIN bird_food AS bf ON (bf.birdID_FK = bh.birdID_FK)
JOIN food AS f ON (f.foodID = bf.foodID_FK)
JOIN habitat AS h ON (h.habitatID = bh.habitatID_FK)
JOIN birds AS b ON (b.birdID = bh.birdID_FK AND b.birdID = bf.birdID_FK)
ORDER BY b.birdID ASC";
The problem is with the bird_habitat and bird_food tables. Each bird can have 2 different foods that it can eat and can also live in 2 different habitats. For example, a blackbird can eat seeds and insects and live in marshes and fields. When I run the SELECT statement, I get 4 results for each bird where I only want to get 2 results. Using the blackbird example, I get these rows returned:
**Bird** **Food** **Habitat**
1 Seeds Marsh
1 Insects Marsh
1 Seeds Field
1 Insects Field
What I want to get returned is this:
**Bird #** **Food** **Habitat**
1 Seeds Marsh
1 Insects Field
So basically, I want to return the unique foods that each bird can eat and the unique habitat they can live in without the redundant fields being repeated. Is this possible? If so, how do I structure my SELECT statement to achieve these results?
Here is a link to the page that the HTML table is displayed if you want to look at what is returned from the SELECT query above so that you can get a better idea of what I am trying to do:
http://www.jasonkmccoy.com/AB-Tech/web250/Mod07/McCoy_edit_delete/index.php
As you will see if you visit the page, where there are birds that have 2 foods and 2 habitats, I need to remove 2 rows so that only unique fields are returned.