MySQL DB - how to group by firstName and surName columns in my select query?-CodePudding

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

CodePudding user response：

Conditional aggregation it is called

SELECT
  u.firstName firstName, u.surName surName,
  MAX(CASE WHEN a.type = 'B' THEN a.city END) as BILLING_CITY,
  MAX(CASE WHEN a.type = 'B' THEN a.street END) as BILLING_STREET ,
  MAX(CASE WHEN a.type = 'B' THEN a.country END) as BILLING_COUNTRY ,
  MAX(CASE WHEN a.type = 'D' THEN a.city END) as DELIVERY_CITY,
  MAX(CASE WHEN a.type = 'D' THEN a.street END) as DELIVERY_STREET ,
  MAX(CASE WHEN  a.type = 'D' THEN a.country END) as DELIVERY_COUNTRY 
  FROM
`addresses` a JOIN `users` u ON a.`user_id` = u.`ID`
GROUP BY u.firstName, u.surName

| firstName | surName | BILLING\_CITY | BILLING\_STREET | BILLING\_COUNTRY | DELIVERY\_CITY | DELIVERY\_STREET | DELIVERY\_COUNTRY |
|:----------|:--------|:--------------|:----------------|:-----------------|:---------------|:-----------------|:------------------|
| Maria     | Rosee   | Paris         | Pariska 22      | FR               | Berlin         | Avenue 33        | GR                |
| Toni      | Saba    | Damascus      | Midan st 49     | SY               | Prague         | Vinohradska 22   | CZ                |
| Rani      | Brown   | Prague        | Italiska 36     | CZ               | London         | Avnue 28         | UK                |
| David     | Rondy   | Amsterdam     | Sparta st 88    | NL               | Rome           | Clombus 61       | IT                |

fiddle

CodePudding user response：

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.

SELECT 
u.firstName FirstName
, u.surName SurName
, b.Billing_City
, b.Billing_Street
, b.Billing_Country
, d.Delivery_City
, d.Delivery_Street
, d.Delivery_Country

FROM users u
INNER JOIN
( 
  SELECT 
  City AS Billing_City
  , Street AS Billing_Street
  , Country AS Billing_Country
  , user_id
 FROM addresses a
  WHERE a.type = 'B'
 
 ) b ON u.ID = b.user_id
 
 INNER JOIN
( 

  SELECT 
  City AS Delivery_City
  , Street AS Delivery_Street
  , Country AS Delivery_Country
  , user_id
 FROM addresses a
  WHERE a.type = 'D'
 
 ) d ON u.ID = d.user_id
 
 ORDER BY u.FirstName

https://www.db-fiddle.com/f/iL3DwTo6U5m7AM8ch5C5xt/0

CodePudding user response：

Join with addresses twice. Use the WHERE conditions to filter each reference to the table to a specific type.

select u.firstName firstName, u.surName surName,
    a1.city AS billing_city, a1.street AS billing_street, a1.country AS billing_country,
    a2.city AS delivery_city, a2.street AS delivery_street, a2.country AS billing_country
FROM users AS u
JOIN addresses AS a1 ON u.id = a1.user_id
JOIN addresses AS a2 on u.id = a2.user_id
WHERE a1.type = 'B' AND a2.type = 'D'