0

I have two mysql tables of images ojm_pages_articles_photos, ojm_images_optimized but I want to select rows that are common to them and also rows that are NOT common to them. In case common rows are found, they should be grouped but in case they are not, the single rows should be selected. The problem in my code is that it only selects and group records that are found both in the two tables but i want records to also be selected when they are found only in table ojm_pages_articles_photos and not in the other one.

SELECT DISTINCT `ojm_pages_articles_photos`.`id_pages_articles_photos`, 
                `ojm_pages_articles_photos`.`url_articles_photo`, 
                `ojm_pages_articles_photos`.`id_ojm_peoples` AS `id_ojm_peoples_uploader` 
FROM    `ojm_pages_articles_photos`, 
        `ojm_images_optimized`  
WHERE `ojm_pages_articles_photos`.`url_articles_photo` LIKE '%jpeg%'
  AND `ojm_pages_articles_photos`.`url_articles_photo`=`ojm_images_optimized`.`url_image_original`
GROUP BY    `ojm_images_optimized`.`url_image_optimized`
ORDER BY    `ojm_pages_articles_photos`.`id_pages_articles_photos` DESC, 
            `ojm_images_optimized`.`optimized_is_smaller`='Oui'

I used AND (ojm_pages_articles_photos.url_articles_photo=ojm_images_optimized.url_image_original) GROUP BY ojm_images_optimized.url_image_optimized

to select where the file is in both tables and group it but there sometimes where the files are only in table ojm_pages_articles_photos but not in the other.

How to select from those two tables where the rows may be or may not be equal and group it if they are equal ?

3
  • they should be grouped And how it is to look like? Show DDLs. Show data example and desured result for it. PS. Your GROUP BY is incorrect (no aggregate function used). And combination of GROUP BY and DISTINCT is strange in close ti all cases.
    – Akina
    Oct 11 '19 at 9:35
  • They are grouped but it does not display the file that is not in two tables. It only display those that are in the two tables
    – John Max
    Oct 11 '19 at 9:37
  • You use INNER JOIN in cartesian form. It cannot show record which is absent in one of tables.
    – Akina
    Oct 11 '19 at 9:40
0

FULL JOIN is not implemented in MySQL.

Possible realization (model):

SELECT filename,
       CASE SUM(table_from) WHEN 1 THEN 'In first table'
                            WHEN 2 THEN 'In second table'
                            ELSE        'In both tables'
                            END AS where_it_is
FROM ( SELECT filename, 1 AS table_from
       FROM table1
       UNION DISTINCT
       SELECT filename, 2
       FROM table2 ) AS combined_data_array
GROUP BY filename

If the field in question is unique in each separate table then use UNION ALL instead of UNION DISTINCT.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.