is there a duplicate rows in UNION

[scorpioy]

I have 2 joined query:

(select from TABLE1 where data like '%A%' and data like '%B%')
UNION
(select from TABLE1 where data like '%A%' OR data like '%B%')

I want to know if result would return duplicate rows in the second half of query, since the result sets of two query would have intersection

Thanks

[Sxooter]

Union automagically removes duplicates from the return set. If you want the duplicates to show up, you can use union all. If you want the difference of the sets, use except.

[_RL_Ian]

scorpioy wrote:

I have 2 joined query:

(select from TABLE1 where data like '%A%' and data like '%B%')
UNION
(select from TABLE1 where data like '%A%' OR data like '%B%')

I want to know if result would return duplicate rows in the second half of query, since the result sets of two query would have intersection

Thanks

Your query really only needs the second select statement, because if you select everything that has A OR B, you automatically got all the records that have A & B. Make sense? What are you trying to accomplish, maybe I can help.

[scorpioy]

Thanks, Sxooter. Do you mean just replace 'UNION' by 'union all' or 'except' would achieve the corresponding specifications?

I know using OR is a superset of AND. what Im really trying to accomplish is to build a search engineer.

I get search keywords from input, then do the search. I want to show results that match all keywords first, then display results that match some of keywords. Do you have a better idea fot doing this? Thanks.

[Sxooter]

If you're wanting a ranking system, i.e. ANDed searches come out higher than ORed searches, you could do something like this:

select from (
(select , 1 as rank from TABLE1 where data like '%A%' and data like '%B%')
UNION
(select *, 2 as rank from TABLE1 where data like '%A%' OR data like '%B%')
) as a
order by a.rank, a.otherfields;

(Not sure of the exact syntax, but you get the idea...

[scorpioy]

I don't need make some particular order within one set of result, as long as result of 'AND' come before the result of 'OR' is good.

How could I do this ?
Thanks

[Sxooter]

My method does just what you're asking for. It ensures that the results of the AND come first.

Please note that while SOME databases may deliver the output of the AND query before the output of your OR query, other databases (PostgreSQL notably) may not, so relying on ordering without an order by clause is relying on an artifact of the system, not a reliable thing to do. MySQL may change some day to a more efficient method of joining two data sets from a union that results in the order being backwards or all mixed up, and it's legal to do so, unless you have an order by.

[scorpioy]

as the example query you gave: order by a.rank, a.otherfields -- there are problems here, the returned table would only return the column name of the first half of the query, therefore, when I specify the name in the order clause for the column in the second query, it will not recognize it and output error.

another problem: the alias table name inside brackets of union cannot be used outside of union

so... Im still trying

[Sxooter]

That query runs perfectly on my postgresql database. The alias is NOT INSIDE the parenthesis, it is OUTSIDE the parenthetical level of the union. Look a little closer this time.

Also, in a union, whatever the names of the first table are they get inherited by the second and subsequent tables.

Are you getting these responses as errors from your database after copying my query, or is this what you're expecting to get?

[scorpioy]

yeh, sorry. your table alias is outside of union
but when i tried exactly the query syntax as you wrote, i got error on MySQL control center: 'Every derived table must have its own alias' -- I don't know what this is

[Sxooter]

Try it without the inner ( ) pairs. Like this:

select from (
select , 1 as rank from acl where rightname like '%A%' or rightname like '%S%'
UNION
select *, 2 as rank from acl where rightname like '%A%' AND rightname like '%S%'
) as a
order by a.rank;

Note that in this example I'm using a query I used on a test database, so the names are different here.. Just sayin.

[scorpioy]

it works now, but there are duplicates if I use that query.
Actually all results of 'AND' are included in results of 'OR'. and they are appended to the end of result of 'OR'

I also noticed:
when I use use my very first query, it is ordered have no duplicates if 'AND' is before 'OR', but if 'OR' is before 'AND' then order becomes mixed. this is sort of strange

[Sxooter]

Well, by including the ranking, we have ensured that each list is unique, so it sounds like MySQL is making a mistake here, or you're not running my query with the "as rank" number thing in it.

If the rows are not coming out the same from reversing the queries, then there is something wrong with MySQL and you have a bug to report / look up.

Note that duplicates are normal at this stage, we'll come up with a simple self join or something to toss them out later, and keep the ranking... Just get the thing working and making two sets, with the ORs having all the ANDed sets, and the ANDs being a subset of that.

[Sxooter]

I think I just thought of a new way of approaching this.

Leave out the ranking, but put in an order by at the end, and use a union all.

That means that a search that matches both the AND and the OR will show up twice.

Now, since they're ordered, you just use a simple loop thing in your display:

$oldrow = array("");
while ($row = fetch_row($res)){
    if (implode(":::",$oldrow)==implode(":::","$row)) { // gotta dupe
        continue ();  // or is that break?  I think continue
    }
}

Since all your dups will come out on top, you can just set some other switch that detects when the dups stop if you want to show the ORs in a different list from the ANDs...

[Sxooter]

Well, it looks like you HAVE found a MySQL bug.

Never mind, my test case had an incorrect field name in it. never mind. I'm running a fairly late model of MySQL though, so yours might have some problems.

Here's my test case:

create table test1 (i1 text, i2 text);
insert into test1 values ('a','a');
insert into test1 values ('b','a');
select from test1 where i1='a' OR i2='a' union all select from test1 where i1='a' AND i2='a';
select from test1 where i1='a' AND i2='a' union all select from test1 where i1='a' OR i2='a';

[Sxooter]

Thought of a sql solution:

select field1, field2, field3, count(),
<some case statement I haven't worked out goes here, might need another level of subselect to work>
from (
select field1, field2, field3 as rank from TABLE1 where data like '%A%' and data like '%B%'
UNION
select field1, field, field3 as rank from TABLE1 where data like '%A%' OR data like '%B%'
) as a
group by a.field1, a.field2, a.field3
order by count(), a.otherfields;

[scorpioy]

I'm using MySQL 4.1.7

thanks for your tremendous help, Sxooter.