0

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!

  • 2
    if you want duplicates, don't use group by – danblack Jul 11 at 2:36
  • That worked, thank you, sir! – raphael75 Jul 11 at 2:42
  • 2
    The only way this query would make any sense is to have a COUNT (see fiddle) with your GROUP BY - how do you distinguish between dupes? You also need a FOREIGN KEY declaration (CONSTRAINT cdl_cd_fk FOREIGN KEY (data_id) REFERENCES card_data (data_id) - see fiddle). In future could you please supply your data as DML and not just plain text - ideally in a fiddle (but be sure and post the DML here also). – Vérace Jul 11 at 5:46
3

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.

| improve this answer | |

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.