inner join issue

[jarvis]

Hi all,

 

Hope someone can help. I've got the following query but it's returning the wrong result!

Code:

SELECT categories.category_id, categories.category, thumbs.file_name, thumbs.image_description, uploads.file_name, uploads.image_description 
FROM ( 
categories 
INNER JOIN thumbs ON categories.category_id = thumbs.category_id 
) 
INNER JOIN uploads ON thumbs.thumb_id = uploads.upload_id 
WHERE categories.category_id =19 

 

Although the above query works, the file_name is incorrect. It's pulling the right name for the thumbs.filename but not for uploads.file_name, this leads me to think it's the wau the uploads table is joined but I cannot see for looking!

 

I have 4 tables

Categories

Code:

CREATE TABLE IF NOT EXISTS `categories` ( 
  `category_id` tinyint(3) unsigned NOT NULL auto_increment, 
  `category` varchar(255) collate latin1_german2_ci NOT NULL default '', 
  `cat_type` enum('0','1') collate latin1_german2_ci NOT NULL default '0', 
  PRIMARY KEY  (`category_id`) 
) 

Category associations

Code:

CREATE TABLE IF NOT EXISTS `category_associations` ( 
  `ca_id` smallint(4) unsigned NOT NULL auto_increment, 
  `upload_id` tinyint(3) unsigned NOT NULL default '0', 
  `category_id` tinyint(3) unsigned NOT NULL default '0', 
  `date_submitted` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, 
  `approved` char(1) collate latin1_german2_ci NOT NULL default 'N', 
  PRIMARY KEY  (`ca_id`) 
) 

Thumbs

Code:

CREATE TABLE IF NOT EXISTS `thumbs` ( 
  `thumb_id` int(10) unsigned NOT NULL auto_increment, 
  `category_id` int(10) unsigned NOT NULL default '0', 
  `file_name` varchar(255) collate latin1_german2_ci NOT NULL default '', 
  `file_size` int(6) unsigned NOT NULL default '0', 
  `file_type` varchar(30) collate latin1_german2_ci NOT NULL default '', 
  `image_description` varchar(255) collate latin1_german2_ci default NULL, 
  `date_entered` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, 
  PRIMARY KEY  (`thumb_id`), 
  KEY `file_name` (`file_name`), 
  KEY `date_entered` (`date_entered`) 
) 

Uploads

Code:

CREATE TABLE IF NOT EXISTS `uploads` ( 
  `upload_id` int(10) unsigned NOT NULL auto_increment, 
  `category_id` int(10) unsigned NOT NULL default '0', 
  `file_name` varchar(255) collate latin1_german2_ci NOT NULL default '', 
  `file_size` int(6) unsigned NOT NULL default '0', 
  `file_type` varchar(30) collate latin1_german2_ci NOT NULL default '', 
  `image_description` varchar(255) collate latin1_german2_ci default NULL, 
  `date_entered` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, 
  PRIMARY KEY  (`upload_id`), 
  KEY `file_name` (`file_name`), 
  KEY `date_entered` (`date_entered`) 
) 

 

 

A form is filled in, uploading a thumb and main image to a set category. The form handles multiple uploads. however, the query at the top seems wrong. Any ideas?

 

TIA

 

[DavidAM]

I don't see anything wrong with the query however,

 

thumb_id and upload_id are both AUTO_INCREMENT, yet you JOIN them as if they are expected to be the same value.  If every insert into thumbs has a related insert into uploads AND IN THE SAME SEQUENCE, and neither has ever failed, these IDs might be equal, but I would not depend on it.

 

If you SELECT thumbs.thumb_id, thumbs.file_name, uploads.upload_id, uploads.file_name

FROM thumbs JOIN uploads on thumb_id = upload_id

 

DO you get the correct filenames?  If not you need to re-think your database design and/or your INSERT process.

 

[jarvis]

Thanks DavidAM, if i use

SELECT thumbs.thumb_id, thumbs.file_name, uploads.upload_id, uploads.file_name 
FROM thumbs JOIN uploads on thumb_id = upload_id
WHERE categories.category_id =19

I get the following error

Unknown column 'categories.category_id' in 'field list'

 

have I used your query incorrectly?

Thanks

[jarvis]

Ok using your sql worked, however, I need to use it as a replacement to the below:

$images_query = 'SELECT categories.category_id, categories.category, thumbs.file_name, thumbs.image_description, uploads.file_name, uploads.image_description FROM (categories INNER JOIN thumbs ON categories.category_id = thumbs.category_id) INNER JOIN uploads ON thumbs.thumb_id = uploads.upload_id WHERE categories.category_id = '.$row['category_id'];

As soon as I try adding in the category id part, it bombs the query with the error above????

thanks

[DavidAM]

I left the categories out of the example query because I was thinking your thumbs_id might not be in sync with uploads_id.  When you run the query that I provided, do you get the right filenames?  Does the file for thumb_id = 1 match up to the file for upload_id = 1, and for = 2 and = 3, and so forth throughout your tables?

 

If they are correct, I do not see why your original query would not work.

 

The last query you sent looks fine too, but why do you have the parenthesis in there?

FROM (categories INNER JOIN thumbs ON categories.category_id = thumbs.category_id) INNER JOIN

 

why not just

FROM categories INNER JOIN thumbs ON categories.category_id = thumbs.category_id INNER JOIN