2

I have three tables, and duplicate column names also :) I want to join albums to products and images to albums. Images are many. Trying such query, it gives me duplicate products. Is there a chance to grab everything in one query?

        SELECT
        *, p.name as nazwa, a.name as nazwa_al, i.name as obrazek
        FROM products p
        JOIN 
        albums a on p.album_id=a.id
        JOIN
          (SELECT *, images.name AS nazwa_im FROM images ORDER BY images.order ASC) i
        ON i.album_id=a.id
        ORDER BY p.order ASC

Products

+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| id          | int(11) | NO   | PRI | NULL    | auto_increment |
| name        | text    | NO   |     | NULL    |                |
| description | text    | NO   |     | NULL    |                |
| album_id    | int(11) | YES  |     | NULL    |                |
| order       | int(11) | NO   |     | NULL    |                |
+-------------+---------+------+-----+---------+----------------+

Albums

+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| name  | text    | NO   |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+

Images

+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(11) | NO   | PRI | NULL    | auto_increment |
| name     | text    | NO   |     | NULL    |                |
| alt      | text    | NO   |     | NULL    |                |
| album_id | int(11) | NO   |     | NULL    |                |
| order    | int(11) | NO   |     | NULL    |                |
+----------+---------+------+-----+---------+----------------+

For the sake of simplicity, I don't want to modify structure of db. The easiest solution for me would be: one product=>one album=>many images

Somnath Muluk
  • 55,015
  • 38
  • 216
  • 226
cssBlaster21895
  • 3,670
  • 20
  • 33

1 Answers1

2

Use joins and use aliases to solve duplicate name error.

You can use distint or group by have results aligned as per same product id.

SELECT
*, p.name as nazwa, a.name as nazwa_al, i.name as obrazek
FROM 
products p
JOIN 
albums a on p.album_id = a.id
JOIN
images i ON i.album_id = a.id
GROUP BY p.id
ORDER BY p.order ASC

You need to use group_concat if multiple rows on right side. 

SELECT
*, p.name as nazwa, a.name as nazwa_al, group_concat(i.name) as obrazek
FROM 
products p
JOIN 
albums a on p.album_id = a.id
JOIN
images i ON i.album_id = a.id
GROUP BY p.id
ORDER BY p.order ASC
Somnath Muluk
  • 55,015
  • 38
  • 216
  • 226