multi table select

millercepbs

I'm trying to display a list of all my records. For some reason, my join only seems to be pulling a single record though. I'm pulling the data off a table called inventory, which has numeric foreign keys to other tables. Those other tables have a mathching primary key and a corresponding description.
To be more clear, here is what a sample would look like...
assetID department building
12345 3 6

but the tables for department and building would look like this...
departmentID departmentDesc
1 accounting
2 human resources
3 mail room

So I would like my list of records to display the corresponding descriptions and not the numeric values. I've used two different ways so far, but both only give one record:

select inventory.assetID,
inventory.room,
model.modelDesc,
status.statusDesc,
category.categoryDesc,
department.departmentDesc,
location.locationDesc
from
inventory,
status,
category,
department,
model,
location
where
inventory.category = category.categoryID
and inventory.location = location.locationID
and inventory.department = department.departmentID
and inventory.model = model.modelID
and inventory.status = status.statusID;

which is essentially the same as
select inventory.assetID,
inventory.room,
model.modelDesc,
status.statusDesc,
category.categoryDesc,
department.departmentDesc,
location.locationDesc
from
inventory join status on inventory.status = status.statusID
join category on inventory.category = category.categoryID
join department on inventory.department = department.departmentID
join model on inventory.model = model.modelID
join location on inventory.location = location.locationID;

nemonoman

Your first SQL is correct and should produce OK results.

So if you are getting unexpectedly fewer records, there are 2 possibilities.

Are you doing this in PHP and looping through the result set: like

$result=mysql_query($query);
while($row=mysql_fetch_array($result)){
 print_r($row);
}

Other possiblity is: you ONLY think your data is correct. Since you are doing inner joins the result set will ONLY show results when all joined elements have corresponding records.

if there is ANY missing corresponding record for ANY of these joins == NO RECORD WILL BE SHOWN

inventory.category = category.categoryID
and inventory.location = location.locationID
and inventory.department = department.departmentID
and inventory.model = model.modelID
and inventory.status = status.statusID;

Desk check your data CAREFULLY. I'll bet some record isn't there, or not exactly equivalent (e.g. varchar '6 ' will NOT == integer '6')

millercepbs

Well to respond to the first question, there is no reult= sort of tag. It is an autogenerated result by Macromedia Dreamweaver MX....dynamic table for just this sort of thing. I'm not real familiar with php scripting, but is this the sort of thing you are talking about?
<?php } while ($row_inventory = mysql_fetch_assoc($inventory)); ?>

And the second question, again the data fetched/posted within the dreamweaver code. I assume that the database would refuse the entry if the data types were different. Am I wrong on that one?

What's so weird about this is that I used the very same query as the first one I posted here in a previous version of the page and it worked fine. That's why I'm so confused at why it wont work now. I've deleted this table and all the associated code numerous times but get the same result. Madness I tell you, madness.

Thanks for your continued help!

nemonoman

OK, so you're trying to figure out why autogenerated code isn't providing the results you want? No wonder you are having trouble

The while code means that the code IS looping. That's all good.

The SQL query is a JOIN, so if

if there is ANY missing corresponding record for ANY of these joins == NO RECORD WILL BE SHOWN

You need to look at your data.

Something's missing in the category, location, department, model or status records.
I only takes 1 missing record in any of the tables above for the entire query to fail.

Also best practice is that that datatypes are the same in all comparisons, like
inventory.category and category.categoryID should both be integers, like that.

If using varchar or text for comparisions, remember that '1 ' is different than ' 1'.

There are ways around this, but they won't be autogenerated by Dreamweaver.

Best is for you to check and fix your data as required.

millercepbs

Nevermind about the last question. I only had two records in the table and so when only 1 showed up, I thought there was a problem with the query. Apparently the data types didn't add correctly like you suggested. When I started adding more records, they showed up in the query, so all is good now.