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 ?

"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

"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)

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

"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