configuring how to pull data out of 3 tables with mysql

Question

Here is how the the database is layout. I can connect to DB fine. I had it pullling from database two things but adding a third i can not get it to pull. I just need some help if i could ..

database - mmoore_drupal

table 1

table name = content_type_uprofile

data
vid= 19723
nid =19674
field_name_value = matthew moore


table 2

table name  = location_instance

data

lid = 1521
vid = 19723
nid = 19674

table 3

table name = location

data

lid = 1521
street =
city =
country =
latitude =
longitude = 

I am trying to pull name and then other info from the other two tables. But mainly i need to have name and other information from location. I thought i had to have the other table to associate the connection. Any help is appreciated.

$query = "SELECT content_type_uprofile.field_name_value,location.street,location.city
    FROM location_instance,location,content_type_uprofile
       WHERE location_instance.lid = location.lid and                               location_instance.nid=content_type_uprofile.nid"

$result = mysql_query($query) or die(mysql_error());


// Print out the contents of each row into a table 
while($row = mysql_fetch_array($result)){
    echo $row['nid']."-".$row['street']. " - ". $row['city'];
    echo "<br />";
}
?>

Answer 1

Use this SQL (explicit join syntax):

SELECT
  content_type_uprofile.nid,
  location.street,
  location.city
FROM
  content_type_uprofile
INNER JOIN location_instance
  ON (content_type_uprofile.nid = location_instance.nid)
INNER JOIN location
  ON (location_instance.lid = location.lid)

The SQL that you posted is using implicit join SQL syntax.

I think for some reason, I think the line in your SQL:

WHERE location_instance.lid = location.lid and location_instance.nid=content_type_uprofile.nid

is filtering out all the rows from your result set. I'm not sure because I avoid the implicit syntax.

You were also missing the nid field which your PHP code is looking for in the result set.

As long as your data is correct (i.e. the fields that you are joining on have the right values), the SQL that I posted will work for you.

Answer 2

You ever done something with join's?

select *.locaition,
    *.content_type_uprofile
from location_instance li
inner join location l on l.lid = li.lid
inner join content_type_uprofile ctu on ctu.vid = li.vid