how to shorten this MySQL query?

Question

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?

nazirula · Accepted 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

Trending News

how to shorten this MySQL query?

1 Answer