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

MySQL

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 ?

Best Answer

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.

Related Solutions

Mysql – Select rows where 2 cells not the same

The particular EAN to render would be determined by the database.

Assuming your tablename is august15, then

using the sample data below +----+-------+---------+---------------+ | id | owner | variant | EAN | +----+-------+---------+---------------+ | 1 | 101 | black | 1111111111111 | +----+-------+---------+---------------+ | 2 | 102 | blue | 1111111111112 | +----+-------+---------+---------------+ | 3 | 103 | white | 1111111111113 | +----+-------+---------+---------------+ | 4 | 103 | white | 1111111111114 | +----+-------+---------+---------------+ | 5 | 103 | black | 1111111111115 | +----+-------+---------+---------------+ | 6 | 104 | white | 1111111111116 | +----+-------+---------+---------------+ | 7 | 104 | black | 1111111111117 | +----+-------+---------+---------------+

This query SELECT * FROM August15 GROUP BY owner, variant; would result

+----+-------+---------+---------------+ | id | owner | variant | EAN | +----+-------+---------+---------------+ | 1 | 101 | black | 1111111111111 | +----+-------+---------+---------------+ | 2 | 102 | blue | 1111111111112 | +----+-------+---------+---------------+ | 5 | 103 | black | 1111111111115 | +----+-------+---------+---------------+ | 3 | 103 | white | 1111111111113 | +----+-------+---------+---------------+ | 7 | 104 | black | 1111111111117 | +----+-------+---------+---------------+ | 6 | 104 | white | 1111111111116 | +----+-------+---------+---------------+

to get exactly what you need change the query to SELECT * FROM August15 GROUP BY owner, variant ORDER BY id ASC;

Sample Data SET FOREIGN_KEY_CHECKS=0;



-- Table structure for august15

DROP TABLE IF EXISTS august15;
CREATE TABLE august15 (
  id int(11) NOT NULL AUTO_INCREMENT,
  owner int(11) NOT NULL,
  variant varchar(50) NOT NULL,
  ean varchar(11) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

-- Records of august15

INSERT INTO august15 VALUES ('1', '101', 'black', '11111111111'); INSERT INTO august15 VALUES ('2', '102', 'blue', '11111111112'); INSERT INTO august15 VALUES ('3', '103', 'white', '11111111113'); INSERT INTO august15 VALUES ('4', '103', 'white', '11111111114'); INSERT INTO august15 VALUES ('5', '103', 'black', '11111111115'); INSERT INTO august15 VALUES ('6', '104', 'white', '11111111116'); INSERT INTO august15 VALUES ('7', '104', 'black', '11111111117');

Mysql – Select from two tables with not direct relationship

The COUNT is too high, correct? That is because the JOINs get more records before the GROUP BY comes along. I suggest you remove the ORDER BY and SELECT more fields so you can see which rows are coming redundantly. That may lead to splitting it into

SELECT ...
FROM ( SELECT person, COUNT(...) FROM ... JOIN ... ) X
JOIN some other tables

AND W.home_work_id IN (SELECT home_work_id FROM fixes GROUP BY home_work_id)

optimizes very poorly; change it to yet another JOIN. Why do you have the GROUP BY there? Perhaps you meant DISTINCT? Perhaps in this case, do

JOIN ( SELECT DISTINCT home_work_id FROM fixes ) Q ON Q.home_work_id = W.home_work_id

Best Answer

Related Solutions

Mysql – Select rows where 2 cells not the same

Mysql – Select from two tables with not direct relationship

Related Question