Display missing rows from junction table

[maxasrg]

I have a table with offers and a table with addons to the offers
This is a many-to-many relationship
Each offer can have multiple addons
Each addon can belong to multiple offers

I would like to display a list of of offers along with the addons belonging to each offer ,
which I have tried to do using a junction table - offers_addons

say -
offer 1 has
addon 2
addon 5

offer 2 has
addon 3

However when I display the offers I only see one addon below each offer
offer 1 displays addon 2 only

This is my mysql and tables

SELECT
offers.heading,
offers.description,
offers.image,
offers.status,
offers.extent,

addons.addon_title,
addons.addon_desc,
addons.thumb,
addon_available
FROM
offers, offers_addons
JOIN
addons ON offers_addons.idde = addons.idde
WHERE
offers_addons.id = offers.id
GROUP BY offers.id

id, heading, description, image, status, extent

'1', 'addon here', addon description', 'texecoz.png', 'OFF', '2021-09-13'
'2', 'this is addon', 'addon another description', 'texecoz.png', 'OFF', '2021-09-13'
'3', 'addon title', 'new addon description', 'images.jpg', 'OFF', NULL
'4', 'another title', 'latest addon description', 'santa.png', 'ON', '2021-09-13'
'5', 'this is a heading', 'the addon description', 'paxton.png', 'ON', '2021-09-13'

addons

idde, addon_title, addon_desc, thumb, addon_available

'1', 'testing', 'product description','0410.jpg' '2021-05-25',
'2', 'some text', 'another description', 'default.png' ', NULL,
'3', 'new text', 'new description', ', 'default.png' '2021-05-30',
'4', 'here is more text', 'latest description', '44445.jpg' ', NULL,
'5', 'helllo there', 'the description','alhua.png' ', NULL,

offers_addons

id, idde

'2', '1'
'3', '1'
'4', '1'
'2', '2'
'1', '3'
'4', '5'
'3', '5'
'5', '1'
'5', '4'

Edited May 22, 2021 by maxasrg

[Barand]

That's because you are using GROUP BY. This for aggregations and gives one row per offer_id.

Try changing it to ORDER BY

[maxasrg]

Hi Barand

If I remove GROUP BY or change it to ORDER BY I see two instances of what was shown previously.

With GROUP BY I see the proper data only not all of it.

[Barand]

Using ORDER BY I get this with your posted data

mysql> SELECT offers.heading
    ->      , offers.description
    ->      , offers.image
    ->      , offers.status
    ->      , offers.extent
    ->      , addons.addon_title
    ->      , addons.addon_desc
    ->      , addons.thumb
    ->      , addon_available
    -> FROM  offers
    ->       JOIN  offers_addons USING (id)
    ->       JOIN  addons USING (idde)
    -> ORDER BY offers.id;
+-------------------+---------------------------+-------------+--------+------------+-------------------+---------------------+-------------+-----------------+
| heading           | description               | image       | status | extent     | addon_title       | addon_desc          | thumb       | addon_available |
+-------------------+---------------------------+-------------+--------+------------+-------------------+---------------------+-------------+-----------------+
| addon here        | addon description         | texecoz.png | OFF    | 2021-09-13 | new text          | new description     | default.png | 2021-05-30      |
| this is addon     | addon another description | texecoz.png | OFF    | 2021-09-13 | some text         | another description | default.png | NULL            |
| this is addon     | addon another description | texecoz.png | OFF    | 2021-09-13 | testing           | product description | 0410.jpg    | 2021-05-25      |
| addon title       | new addon description     | images.jpg  | OFF    | NULL       | helllo there      | the description     | alhua.png   | NULL            |
| addon title       | new addon description     | images.jpg  | OFF    | NULL       | testing           | product description | 0410.jpg    | 2021-05-25      |
| another title     | latest addon description  | santa.png   | ON     | 2021-09-13 | testing           | product description | 0410.jpg    | 2021-05-25      |
| another title     | latest addon description  | santa.png   | ON     | 2021-09-13 | helllo there      | the description     | alhua.png   | NULL            |
| this is a heading | the addon description     | paxton.png  | ON     | 2021-09-13 | testing           | product description | 0410.jpg    | 2021-05-25      |
| this is a heading | the addon description     | paxton.png  | ON     | 2021-09-13 | here is more text | latest description  | 44445.jpg   | NULL            |
+-------------------+---------------------------+-------------+--------+------------+-------------------+---------------------+-------------+-----------------+

 

[maxasrg]

Hi Barand

Thanks for helping with this issue.

I used the mysql code you suggested and got the same duplication as before so changed only the word ORDER to GROUP and got the same items displaying as before.

Would it help if I submitted the full script for the page including the PHP? 

[Barand]

If you are running the same query I posted on the same data that you posted you should get the same results as me. So which is different?