Yahoo Answers is shutting down on 4 May 2021 (Eastern Time) and the Yahoo Answers website is now in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.

Kidd J
Lv 6
Kidd J asked in Computers & InternetProgramming & Design · 9 years ago

how to shorten this MySQL query?

SELECT v1.* FROM (

SELECT c1,c2,c3,c4

FROM t1 WHERE left(c1,3)='200'

and c2<>'20' and c3='005' group by c1

) v1 union all

SELECT v1.* FROM (

SELECT c1,c2,c3,c4

FROM t1 WHERE left(c1,3)='200'

and c2<>'20' and c3='006' group by c1

) v1 union all

SELECT v1.* FROM (

SELECT c1,c2,c3,c4

FROM t1 WHERE left(c1,3)='200'

and c2<>'20' and c3='007' group by c1

) v1 union all

SELECT v1.* FROM (

SELECT c1,c2,c3,c4

FROM t1 WHERE left(c1,3)='200'

and c2<>'20' and c3='008' group by c1

) v1;

Update:

Sorry, forgot to add. The queries are all the same except for "c3" value. Let's assume that c3 = room number. My objective is to create a single query with all different c3 value inside it. How ah?

1 Answer

Relevance
  • nazirula
    9 years ago
    Favourite answer

    try it. what say you ?

    SELECT * FROM t1

    WHERE LEFT(c1,3)='200'

    AND c2<>'20'

    AND c3 IN (SELECT roomcode FROM roomlist)

    GROUP BY c1;

    EDIT:

    do not use roomcode so that it will list all room. do not bother to use other table, hiiiyaaaa

    SELECT COUNT(*),outletcode FROM t1 WHERE LEFT(c1,3)='200'

    AND c2<>'20' GROUP BY outletcode

Still have questions? Get answers by asking now.