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;

2012-03-11T18:29:16Z

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?

nazirula2012-03-11T18:57:49Z

Favorite 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