Mariadb – possible to INclude duplicate rows

duplicationmariadbquery

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 the SELECT 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 to GROUP BY.