help with a select clause

[Lone Wolf]

We have a recordset with the following structure id (int), mls
(varchar)

What we need to do is within a single sql call we need to show all
records where the mls = '' and all unique records where mls!=''

In other words, there will be multiple records with duplicate mls
entries. We do not want to show duplicate enties for where the mls has
been set, only where the mls has not been set.

Any suggestions?

[Chander Ganesan]

I'm not sure I understand the question here. It sounds like you want
to do 2 queries (one where MLS is = something and one where MLS !=
something). Is that correct?

In that case you could just use a union to perform the 2 queries and
return them.

select mls, id from mls_id_table where mls='value' union select
distinct mls, id from mls_id_table where mls != 'novalue';

If you are looking for distinct values across the board you might try:

select distinct A.mls, A.id from (select mls_id_table where mls='value'
union select distinct mls, id from mls_id_table where mls != 'novalue')
as A;

To ensure you get adequate performance you might need to make sure your
sort buffer is of adequate size (EXPLAIN the query to find out)..
Otherwise you might find that the queries that use sort will have to
work with a temporary table..

--
Chander Ganesan
Open Technology Group, Inc.
Phone: [phone]
http://www.otg-nc.com
Expert MySQL training.

[Lone Wolf]

Thanks! I completely forgot about Union and intersect! but that's is
what I was hunting for.

Kudos!

Lone Wolf