Monday, May 14, 2012

select from one table where count=1 in second table and two more conditions

I need to select transactions with cash payment and same date but only for those transactions
which have only one payment ( so num 14 should be omitted from resultset )
So correct result is 12 and 13 only.



    Table2                          Table1
num | date | data | total num | payment | date
12 xy abc 2.5 12 cash xy
13 xy cbc 2.1 13 cash xy
14 xy acc 2.3 14 visa xy
19 xy def 2.0 14 cash xy
27 xy fgh 1.3 19 visa xy
27 mc xy


Something like this gives num 14 in result-set but 14 should be omitted.



SELECT num, data 
FROM Table2
WHERE num IN
(
SELECT num FROM `Table1`
WHERE payment = 'cash'
GROUP BY `num`
HAVING ( COUNT(`num`) = 1 )
)


To sumarize correct answer (by tombom ):



 SELECT t2.num, t2.data 
FROM Table1 as t1
INNER JOIN Table2 as t2 ON t1.num = t2.num
AND t1.date = 'xy'
GROUP BY t1.num
HAVING GROUP_CONCAT(t1.payment) = 'cash'


Thanks!





No comments:

Post a Comment