I have MariaDB version 10.2.14-MariaDB. I have a table that looks like this:
CREATE TABLE `card_data_link` (
`card_id` INT(11) NULL DEFAULT NULL,
`data_id` INT(11) NULL DEFAULT NULL,
INDEX `card_id` (`card_id`) USING BTREE,
INDEX `data_id` (`data_id`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
It has this data:
card_id data_id
"63" "21"
"34" "3"
"34" "21"
"34" "21"
It joins to this one:
CREATE TABLE `card_data` (
`data_id` INT(11) NOT NULL AUTO_INCREMENT,
`data` TEXT NULL DEFAULT NULL COLLATE 'utf8_general_ci',
PRIMARY KEY (`data_id`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=51
;
It has data that looks like this:
data_id data
"21" "{R}"
"3" "{3}"
If I run this query:
select
cdl.card_id,
cdl.data_id,
cd.data
from
card_data_link cdl
left join
card_data cd on cd.data_id = cdl.data_id
group by
cdl.card_id
,cd.data_id
results:
card_id data_id data
"34" "3" "{3}"
"34" "21" "{R}"
"63" "21" "{R}"
Is there a way I can get the second
"34" "21" "{R}"
row to show up so it returns:
card_id data_id data
"34" "3" "{3}"
"34" "21" "{R}"
"34" "21" "{R}"
"63" "21" "{R}"
It's like it's doing a select distinct, even though I'm not putting in distinct. Am I not grouping correctly? Thanks!
Best Answer
GROUP BY
aggregates the result values of theSELECT
statement based on its arguments.Removing the
GROUP BY
clause and arguments from your SQL will allow duplicates to be presented.For MariaDB recommend
sql_mode=ONLY_FULL_GROUP_BY
(not default, ref: manual) to ensure that SQL statements are correctly formed with respect toGROUP BY
.