Trying to use a JOIN or a UNION for 3 or more tables

[boba fett]

 

Hello - I'm still working on my database to show the research data pertaining to individual snakes at my workplace. I am trying to use a JOIN statement to allow me to access at least three tables, possibly up to five tables by the time I have all the information from our researchers, all at once.

 

The three tables I am currently trying to join are:

specieslist - this shows general information regarding pretty much all snakes of a particular species. I currently have about 30 species in that table. Following advice from phpacademy, I made a column for a species key, which this table shares with the one listed below, eachsnake, as its JOIN point.

 

eachsnake - this table has the more specific information about each individual snake, such as, time and date acquired, size, weight, donor, and the snake's individual ID number, called its Avid number. This is a nine-digit number unique to each animal. This table has in common a species key which is the JOIN point for the specieslist table.

 

HPLC - this table will hold the first set of research data, and will use the Avid number as the JOIN point between it and the eachsnake table. This data will mostly consist of 5 or more graphs and charts along with text explanations about the meanings of these charts. 5 columns will contain URLs to the locations of the graphs, while 5 others will contain the text information. No snake will have the same set of graphs, which is why I am using the Avid number as the unique key.

 

I will have other types of scientific research data to add as time goes on, but for now am just trying to understand HOW to do this and get the information I need posted on the results page.

 

The problem I am having is that the various JOIN and UNION SQL statements I've tried have returned odd results. I am new to PHP and My SQL, so I realize there are probably plenty of syntax errors with my attempts, and could use help adjusting them to make it do more what I want AND/OR ideas of other ways to accomplish my goal of having species data, individual snake data and research data all appear on one page, without making a huge, all-inclusive table with all info, research included, for each snake.

 

   1. SELECT * FROM eachsnake JOIN specieslist JOIN HPLC ON eachsnake.key = specieslist.key AND eachsnake.Avid = HPLC.Avid

 

returns only 9 out of the expected 717, as does just

 

   1. SELECT *
   2. FROM eachsnake  JOIN HPLC ON eachsnake.Avid = HPLC.Avid 

 

When I add a LEFT JOIN to the above code, I get the correct number of returns, but the Avid number refuses to show on my results page, and I can't seem to add in the species data at all.

 

In addition, some combinationI stumbled across yesterday that I can't remember this morning somehow gave me 31,000 returns (crazy!) :shock: .

 

I tried a UNION statement earlier, but that was cumbersome as I had to make sure the number of columns in each statement was equal or it wouldn't accept it.

 

Thanks - any help is appreciated.

[boba fett]

this code works for anyone that needs it

 

SELECT * FROM eachsnake LEFT JOIN HPLC ON eachsnake.Avid = HPLC.Avid JOIN specieslist ON eachsnake.Key = specieslist.Key