Could you please help me with group by firstName and surName columns in my select query ?
to view 4 rows instead of 8 rows without NULL values
i joined these two tables in MySQL:
CREATE TABLE IF NOT EXISTS `users` (
`ID` int NOT NULL AUTO_INCREMENT,
`userName` varchar(100) NOT NULL,
`firstName` varchar(100) NOT NULL,
`surName` varchar(100) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `userName` (`userName`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3;
INSERT INTO `users` (`ID`, `userName`, `firstName`, `surName`) VALUES
(1, 'Toni_889', 'Toni', 'Saba'),
(2, 'Rani_185', 'Rani', 'Brown'),
(3, 'Mariaaa111', 'Maria', 'Rosee'),
(4, 'DDD_Ron', 'David', 'Rondy');
COMMIT;
CREATE TABLE IF NOT EXISTS `addresses` (
`ID` int NOT NULL AUTO_INCREMENT,
`type` char(1) NOT NULL,
`user_id` int NOT NULL,
`city` varchar(100) NOT NULL,
`street` varchar(100) NOT NULL,
`country` varchar(2) NOT NULL,
PRIMARY KEY (`ID`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb3;
INSERT INTO `addresses` (`ID`, `type`, `user_id`, `city`, `street`, `country`) VALUES
(1, 'B', 3, 'Paris', 'Pariska 22', 'FR'),
(2, 'D', 3, 'Berlin', 'Avenue 33', 'GR'),
(3, 'B', 1, 'Damascus', 'Midan st 49', 'SY'),
(4, 'D', 1, 'Prague', 'Vinohradska 22', 'CZ'),
(5, 'B', 2, 'Prague', 'Italiska 36', 'CZ'),
(6, 'D', 2, 'London', 'Avnue 28', 'UK'),
(7, 'B', 4, 'Amsterdam', 'Sparta st 88', 'NL'),
(8, 'D', 4, 'Rome', 'Clombus 61', 'IT');
SELECT u.firstName firstName, u.surName surName ,
(SELECT a.city from addresses WHERE a.type = 'B' and a.user_id = u.ID limit 1 ) as BILLING_CITY,
(SELECT a.street from addresses WHERE a.type = 'B' and a.user_id = u.ID limit 1) as BILLING_STREET ,
(SELECT a.country from addresses WHERE a.type = 'B' and a.user_id = u.ID limit 1) as BILLING_COUNTRY ,
(SELECT a.city from addresses WHERE a.type = 'D' and a.user_id = u.ID limit 1) as DELIVERY_CITY ,
(SELECT a.street from addresses WHERE a.type = 'D' and a.user_id = u.ID limit 1) as DELIVERY_STREET ,
(SELECT a.country from addresses WHERE a.type = 'D' and a.user_id = u.ID limit 1) as DELIVERY_COUNTRY
FROM users u
JOIN addresses a
on a.user_id = u.ID;
but i got users duplicated in rows with null values like this screenshot :
i expected 4 rows only in result without null
3
Answers
I was able to join into the address table twice once with billing (B) and once with Delivery (D). This should give you the 4 rows.
https://www.db-fiddle.com/f/iL3DwTo6U5m7AM8ch5C5xt/0
Conditional aggregation it is called
fiddle
Join with
addresses
twice. Use theWHERE
conditions to filter each reference to the table to a specific type.