MySQL Join Statement pulling items multiple times from the same field in a DB

I have a table that has two columns. One is image_id and one is image_name. Like this

image_id | image_name
--------------------
1        | terrier.jpg
2        | schnauzer.jpg
3        | beagle.jpg

And I have another table like this:

item           |  image_1 | image_2
------------------------------------
Friendly Dogs  |   1      |    2 
Loyal Dogs     |   2      |    3

The first table let's call images and the second table is dogs.

I开发者_如何转开发 tried the following SQL but it didn't work:

SELECT * FROM dogs INNER JOIN  images ON images.image_id = dogs.image_1 AND INNER JOIN images ON images_image_id = dogs.image_2

But this errors out. I'm missing somethings where I can specify names some kind of alias or something for image_name so it doesn't overlap in the query.

What I want is an array that has image_1 and image_2 in it rather than image_name.

Can someone help out? Thanks.

I want in the array two arrays or objects:

[item] => Friendly Dogs [image_1] => terrier.jpg [image_2] => schnauzer.jpg

and

[item] => Loyal Dogs [image_1] => schnauzer.jpg [image_2] => beagle.jpg

You can JOIN a table multiple times:

SELECT d.item AS dog, i1.image_name AS im1, i2.image_name AS im2
FROM dogs AS d JOIN images AS i1 ON (i1.image_id = d.image_1)
               JOIN images AS i2 ON (i2.image_id = d.image_2)

    SELECT d.item, i1.image_name image_1, i2.image_name image_2
      FROM dogs d
INNER JOIN images i1 
        ON d.image_1 = i1.image_id
INNER JOIN images i2 
        ON d.image_2 = i2.image_id

I am assuming all the dogs will have an image on the image_1 and image_2 column.

NOTE: The dogs table is not in the First normal form. It would be normalized if you created another table to hold the relationships.

dogs table

dog_id | item           
------------------------
1      | Friendly Dogs  
2      | Loyal Dogs     

dogs_images table

dog_id | image_id
-----------------
1      | 1
1      | 2
2      | 2
2      | 3

Update

The query for the relationship table would be:

   SELECT d.dog_id, d.item, i.image_name
     FROM dogs d
LEFT JOIN dogs_images di
       ON d.dog_id = di.dog_id
LEFT JOIN images i
       ON di.image_id = i.image_id

The result would be something like:

dog_id | item           | image_name   
------------------------------------
1      | Friendly Dogs  | terrier.jpg
1      | Friendly Dogs  | schnauzer.jpg
2      | Loyal Dogs     | schnauzer.jpg
2      | Loyal Dogs     | beagle.jpg