Union distinct
This is a discussion on Union distinct within the MySQL forums, part of the Database Server Software category; --> Hi all, I've a query wich results is returned via a "UNION". Problem is that I'd like to return ...
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 10:32 AM
| ||||
| ||||
 Union distinct Hi all, I've a query wich results is returned via a "UNION". Problem is that I'd like to return a distinct row so if the first select returns a record based on a field, then the second must not. like (select idx, ....from ...)union(select idx,....from ......) so if idx is returned in the first select it must not in the second. It is possible ?      |
|
02-28-2008, 10:32 AM
| |||
| |||
| Re: Union distinct "Bob Bedford" <bob@bedford.com> schreef in bericht news:4794987d$0$3817$5402220f@news.sunrise.ch... > Hi all, > > I've a query wich results is returned via a "UNION". Problem is that I'd > like to return a distinct row so if the first select returns a record > based on a field, then the second must not. > > like (select idx, ....from ...)union(select idx,....from ......) > > so if idx is returned in the first select it must not in the second. > > It is possible ? > select id from nrs; 1 2 3 4 4 rows in set (0.0 sec) select id from nrs where id<4 union select id from nrs where id>1; 1 2 3 4 4 rows in set (0.0 sec) what do you mean? i dont see your problem |
|
02-28-2008, 10:32 AM
| |||
| |||
| Re: Union distinct "Luuk" <luuk@invalid.lan> a écrit dans le message de news: 2tie65-pgm.ln1@a62-251-88-195.adsl.xs4all.nl... > > "Bob Bedford" <bob@bedford.com> schreef in bericht > news:4794987d$0$3817$5402220f@news.sunrise.ch... >> Hi all, >> >> I've a query wich results is returned via a "UNION". Problem is that I'd >> like to return a distinct row so if the first select returns a record >> based on a field, then the second must not. >> >> like (select idx, ....from ...)union(select idx,....from ......) >> >> so if idx is returned in the first select it must not in the second. >> >> It is possible ? >> > > select id from nrs; > 1 > 2 > 3 > 4 > 4 rows in set (0.0 sec) > > select id from nrs where id<4 > union > select id from nrs where id>1; > 1 > 2 > 3 > 4 > 4 rows in set (0.0 sec) > > what do you mean? i dont see your problem Not so simple. I've a complex query: (select idx, firstname,lastname,address,....from person inner join status on person.idperson = status.idperson where status.statustype = 12 and status.statusdate < '2007-01-01') union (select idx, firstname,lastname,address,....from person inner join group on person.idperson = group.idperson where group.grouptype = 27 and group.groupdate < '2007-01-01') as you can see I have 2 different ways to get the informations and I can't mix the 2 in the same query (as The way I link tables is quite different) |
|
02-28-2008, 10:32 AM
| |||
| |||
| Re: Union distinct On Mon, 21 Jan 2008 14:41:14 +0100, Bob Bedford <bob@bedford.com> wrote: > > "Luuk" <luuk@invalid.lan> a écrit dans le message de news: > 2tie65-pgm.ln1@a62-251-88-195.adsl.xs4all.nl... >> >> "Bob Bedford" <bob@bedford.com> schreef in bericht >> news:4794987d$0$3817$5402220f@news.sunrise.ch... >>> Hi all, >>> >>> I've a query wich results is returned via a "UNION". Problem is that >>> I'd >>> like to return a distinct row so if the first select returns a record >>> based on a field, then the second must not. >>> >>> like (select idx, ....from ...)union(select idx,....from ......) >>> >>> so if idx is returned in the first select it must not in the second. >>> >>> It is possible ? >>> >> >> select id from nrs; >> 1 >> 2 >> 3 >> 4 >> 4 rows in set (0.0 sec) >> >> select id from nrs where id<4 >> union >> select id from nrs where id>1; >> 1 >> 2 >> 3 >> 4 >> 4 rows in set (0.0 sec) >> >> what do you mean? i dont see your problem > Not so simple. I've a complex query: > (select idx, firstname,lastname,address,....from person inner join > status on > person.idperson = status.idperson where status.statustype = 12 and > status.statusdate < '2007-01-01') > union > (select idx, firstname,lastname,address,....from person inner join group > on > person.idperson = group.idperson where group.grouptype = 27 and > group.groupdate < '2007-01-01') > > as you can see I have 2 different ways to get the informations and I > can't > mix the 2 in the same query (as The way I link tables is quite different) Haven't had any coffee yet, but: SELECT idx, firstname,lastname,address,.... FROM person LEFT JOIN status ON person.idperson = status.idperson AND status.statustype = 12 AND status.statusdate < '2007-01-01' LEFT JOIN group ON person.idperson = group.idperson AND group.grouptype = 27 AND group.groupdate < '2007-01-01' WHERE group.idperson IS NOT NULL OR status.idperson IS NOT NULL Haven't really thought the effeciency of the query through yet though. -- Rik Wasmus |
|
02-28-2008, 10:32 AM
| ||||
| ||||
| Re: Union distinct "Bob Bedford" <bob@bedford.com> schreef in bericht news:4794a104$0$3813$5402220f@news.sunrise.ch... > > "Luuk" <luuk@invalid.lan> a écrit dans le message de news: > 2tie65-pgm.ln1@a62-251-88-195.adsl.xs4all.nl... >> >> "Bob Bedford" <bob@bedford.com> schreef in bericht >> news:4794987d$0$3817$5402220f@news.sunrise.ch... >>> Hi all, >>> >>> I've a query wich results is returned via a "UNION". Problem is that I'd >>> like to return a distinct row so if the first select returns a record >>> based on a field, then the second must not. >>> >>> like (select idx, ....from ...)union(select idx,....from ......) >>> >>> so if idx is returned in the first select it must not in the second. >>> >>> It is possible ? >>> >> >> select id from nrs; >> 1 >> 2 >> 3 >> 4 >> 4 rows in set (0.0 sec) >> >> select id from nrs where id<4 >> union >> select id from nrs where id>1; >> 1 >> 2 >> 3 >> 4 >> 4 rows in set (0.0 sec) >> >> what do you mean? i dont see your problem > Not so simple. I've a complex query: > (select idx, firstname,lastname,address,....from person inner join status > on person.idperson = status.idperson where status.statustype = 12 and > status.statusdate < '2007-01-01') > union > (select idx, firstname,lastname,address,....from person inner join group > on person.idperson = group.idperson where group.grouptype = 27 and > group.groupdate < '2007-01-01') > > as you can see I have 2 different ways to get the informations and I can't > mix the 2 in the same query (as The way I link tables is quite different) > if you want the distinct results you can do SELECT distinct * FROM ( query1 UNION query2 } it will still give duplicate values of your `idx` because MySQL cannot decide for you wat to do if you have a duplicate idx. you can do that for MySQL by adding a GROUP BY SELECT distinct * FROM ( query1 UNION query2 } GROUP BY idx |
Linear Mode
