Jump to content

Display missing rows from junction table


maxasrg

Recommended Posts

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 by maxasrg
Link to comment
Share on other sites

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            |
+-------------------+---------------------------+-------------+--------+------------+-------------------+---------------------+-------------+-----------------+

 

Link to comment
Share on other sites

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? 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.