CODEWITHSUNDEEP
mysqlsql
user3782464
user3782464

Reputation: 151

Mysql union Priority

I have 3 tables and my query is :

SELECT BRAND, AMOUNT FROM
(
SELECT BRAND, AMOUNT FROM SALES1 
UNION
SELECT BRAND, AMOUNT FROM SALES2
UNION
SELECT BRAND, AMOUNT FROM SALES3
)

SALES 1 TABLE HAS BRAND: A AND AMOUNT: 50

SALES 3 TABLE HAS BRAND: A AND AMOUNT: 100

I want to get the amount 50 and disregard 100. I want to ask if is there any priority when using union?

Upvotes: 1

Views: 1171

Answers (3)

Rick James
Rick James

Reputation: 142278

SELECT  BRAND, AMOUNT
    FROM  
      ( SELECT  BRAND, AMOUNT, 1 AS priority
            FROM  SALES1
            UNION  ALL 
        SELECT  BRAND, AMOUNT, 2 AS priority
            FROM  SALES2
            UNION  ALL 
        SELECT  BRAND, AMOUNT, 3 AS priority
            FROM  SALES3 
      )
    ORDER BY  priority
    LIMIT  1;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269653

If you want the first occurrence, you should use union all rather than union. This is important for performance reasons, because union does unnecessary duplicate elimination.

Then use the not exists clauses for each subquery:

(SELECT BRAND, AMOUNT
 FROM SALES1 
) UNION ALL
(SELECT BRAND, AMOUNT
 FROM SALES2 s2
 WHERE NOT EXISTS (SELECT 1 FROM SALES1 s1 WHERE s1.BRAND = s2.BRAND)
) UNION ALL
(SELECT BRAND, AMOUNT
 FROM SALES3 s3
 WHERE NOT EXISTS (SELECT 1 FROM SALES1 s1 WHERE s1.BRAND = s3.BRAND) AND
       NOT EXISTS (SELECT 1 FROM SALES2 s2 WHERE s2.BRAND = s3.BRAND)
)

Upvotes: 3

void
void

Reputation: 7880

you need a "where not exists"(or "not in" or a "left join") within your unions:

(SELECT BRAND, AMOUNT FROM SALES1 )
UNION 
(SELECT BRAND, AMOUNT FROM SALES2) 
UNION 
(SELECT BRAND, AMOUNT FROM SALES3 where brand not in (SELECT BRAND FROM SALES1))

Upvotes: 0

Related Questions