skip to Main Content

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 :

enter image description here

i expected 4 rows only in result without null

3

Answers


  1. 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

    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
  3. 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'
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search