CODEWITHSUNDEEP
mysql
JustNone
JustNone

Reputation: 15

SQL return multiple this same rows

I'm trying to SELECT information about clients but PMA returns me multiple rows.

SELECT `clients_agreements`.`date_start`,
       `buildings`.`id`, 
       `buildings`.`street`, 
       `buildings`.`street_nr`,
       `clients`.`building_id`,
       `clients_agreements`.`user_id` 
FROM `clients_agreements` 
LEFT JOIN `buildings` On `clients_agreements`.`user_id` = `buildings`.`id` 
LEFT JOIN `clients` ON `clients`.`building_id` = `buildings`.`id` 
WHERE `date_start` = (CURRENT_DATE)

I expect one output but the actual output is this same rows multiple time.

Upvotes: 1

Views: 31

Answers (3)

Strawberry
Strawberry

Reputation: 33935

FWIW, I find this easier to read:

SELECT a.date_start
     , b.id
     , b.street
     , b.street_nr
     , c.building_id
     , a.user_id 
  FROM clients_agreements a
  LEFT 
  JOIN buildings b
    On b.id = a.user_id 
  LEFT 
  JOIN clients c
    ON c.building_id = b.id 
 WHERE a.date_start = CURRENT_DATE()

Upvotes: 0

SAVe
SAVe

Reputation: 812

Try This 

SELECT DISTINCT `clients_agreements`.`date_start`,
       `buildings`.`id`, 
       `buildings`.`street`, 
       `buildings`.`street_nr`,
       `clients`.`building_id`,
       `clients_agreements`.`user_id` 
FROM `clients_agreements` 
LEFT JOIN `buildings` On `clients_agreements`.`user_id` = `buildings`.`id` 
LEFT JOIN `clients` ON `clients`.`building_id` = `buildings`.`id` 
WHERE `date_start` = (CURRENT_DATE)

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

The join produce a cartesian product so if you need distinct result only, use DISTINCT 

SELECT DISTINCT  `clients_agreements`.`date_start`
  , `buildings`.`id`
  , `buildings`.`street`
  , `buildings`.`street_nr`
  , `clients`.`building_id`
  , `clients_agreements`.`user_id` 
FROM `clients_agreements` 
LEFT JOIN `buildings` On `clients_agreements`.`user_id` = `buildings`.`id` 
LEFT JOIN `clients` ON `clients`.`building_id` = `buildings`.`id`
WHERE `date_start` = (CURRENT_DATE)

Upvotes: 1

Related Questions