CODEWITHSUNDEEP
mysqlduplicatesleft-join
cooper
cooper

Reputation: 674

mysql left join without duplicate rows

Maybe the title is not the best but I didn't know how to explain it in just a few words.

I've two tables like this:

Table "users"

id|name
--+--------+
1 |Alice   |
2 |Bob     |
3 |Charlie |

Table "article_users"

user_id|article_id|expired_date
-------+----------+------------+
   1   |    1     |01-01-2016
   1   |    5     |01-01-2018
   3   |    1     |01-01-2016

I need to obtain all the users and know whether they have an article relation, obtaining if it's expired in case they have that relation.

I don't need to know which article is expired or not. I mean, in case a user has a relation not expired with (at least) one article, the answer should be: "yeah, he/she has an active relation".

The query I was working on is:

SELECT u.*, IF(TIMEDIFF(expired_date,CURDATE())<0,'YES',IF(TIMEDIFF(expired_date,CURDATE())>0,'EXPIRED','NO')) relation
FROM users u 
LEFT JOIN article_users au ON au.user_id = u.id

The problem I have is that I'm getting "duplicate" rows because of the LEFT JOIN, and I just want to know if a user has an active relation. I mean, no matter if the user has one or more active relations. In case he/she has at least one (active relation) then the result should be "yes". In case he has no active relations (but he/she has relations) the result should be "expired", and finally in case he/she has no relations, the result should be "no".

So, the desired result should be:

id|name    |relation
--+--------+--------
 1|Alice   |   YES    
 2|Bob     |    NO   
 3|Charlie |  EXPIRED

I'm trying to get it with a GROUP_CONCAT function but I'm unabled to get it. I don't know if it's the right way or not.

Any help would be really appreciated.

Thanks in advance.

Upvotes: 0

Views: 975

Answers (1)

Jorge Campos
Jorge Campos

Reputation: 23361

Try this way:

Setup:

create table users(
  id integer,
  name varchar(15)
);

create table article_users (
  user_id integer,
  article_id integer,
expired_date date
);

insert into users values (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
insert into article_users values (1, 1, '2016-01-01'), 
                 (1, 2, '2018-01-01'), (3, 1, '2016-01-01');

Query:

select u.name, 
       case when a.qtd>0 then 'Yes'
            when a.qtd=0 then 'Expired'
            else 'No' end as relation
  from users u
         left join 
         (select user_id, 
                 sum(if(timediff(expired_date, curdate())>0, 1, 0)) qtd
            from article_users 
           group by user_id) a on u.id = a.user_id
order by u.name;

Result:

name     relation
------------------
Alice    Yes
Bob      No
Charlie  Expired

Since SQLFiddle isn't currently working I added it on SQLize. See it working here: http://www.sqlize.com/8cBsHBWCbN

Upvotes: 1

Related Questions