ansaurus

Question

query sql and get result for foreign key's value(not id)

Answer 1

A:

use a simple left join:

SELECT imageID, Image, IMAGE.categoryID, CATEGORIES.Category
FROM IMAGES
LEFT JOIN CATEGORIES
ON IMAGES.categoryID = CATEGORIES.categoryID
WHERE image = 'exampleImage'

use table aliases to make the statement shorter:

SELECT imageID, Image, i.categoryID, c.Category
FROM IMAGES i
LEFT JOIN CATEGORIES c
ON i.categoryID = c.categoryID
WHERE image = 'exampleImage'

knittl 2010-08-31 16:39:57

@knittl Though a left join will work they really should be avoided if you can. They are much more expensive than an inner join. From the question the op mentioned that he had a foreign key relationship with the category table which means that there will always be a matching value. So he really should be using an inner join and not left. He would only want to use a left if there was a possibility of no matching value but still wanted to see the image result.

spinon 2010-08-31 16:53:18

@knittl Thank you very much for your advice :)

slexAtrukov 2010-08-31 17:07:22

@spinon: he can still have categories with NULL – those lines will be omitted in the result when using an inner join. it depends on the schema, the desired result, etc.

knittl 2010-08-31 17:50:25

@knittl I was saying that since there is a foreign key relationship between images and categories there should never be a record in the image table with a category id that doesn't match the category table. When this is the case you wouldn't use a left join because it is a much more expensive process and there is no need since they always match. If there was a chance that the id in the image table wasn't in the category table (i.e. Maybe a linking table with all matching categories to an image) then you would use a left join because there is a possibility that there might not be a match.

spinon 2010-08-31 18:05:29

@spinon: foreign key relations can still contain NULL values

knittl 2010-08-31 18:11:28

@knittl Not if they are setup correctly. The whole idea behind a foreign key constraint is that you are telling the db engine to enforce this rule so that no child record (image) can be inserted with a category id that does not exist in the category table. Now maybe he didn't set that up like that. But that is how foreign key constraints work. Here is the mysql link:http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.htmlI know your solution can work in the scenario you mentioned but unless there is no other option this is not a good design decision for this relationship.

spinon 2010-08-31 19:58:34

@spinon: NULL is a special value, not an ID from some other column. if you define categoryId as NULL it allows NULL values. in the OP's case this would mean that an image does not have a category set

knittl 2010-08-31 20:20:14

Answer 2

A:

You want to use an inner join so it returns the records that match based on the join criteria.

SELECT Images.CategoryID, Category.Category
FROM IMAGES
INNER JOIN Category ON Category.CategoryID = Images.CategoryID
WHERE image = exampleimage

spinon 2010-08-31 16:44:01

ansaurus

tags:

views:

answers:

query sql and get result for foreign key's value(not id)

related questions