4
SELECT * from campaigns WHERE id not in
(SELECT 
    e.id_campaign, d.name, d.frequency, d.country, d.referral, d.bid, d.status, COUNT(e.id) AS countcap  
    FROM campaigns d
    LEFT JOIN served e
    ON d.id = e.id_campaign 
    WHERE 
        d.status = 'Active'
    GROUP BY e.id_campaign
    HAVING
        countcap < d.frequency)

I get the error "Operand Should Contain 1 Column" - but I need the COUNT(e.id)

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
reefine
  • 873
  • 2
  • 13
  • 25
  • I am trying to select all records from 'campaigns' that don't match the ids matching the SELECT inside parentheses – reefine Jun 11 '11 at 22:38

3 Answers3

5

There's always this:

select *
from campaigns 
where id not in (
    select id_campaign from (
        select e.id_campaign as id_campaign, d.frequency, e.id
        from campaigns d left join served e on d.id = e.id_campaign
        where d.status = 'Active'
        group by e.id_campaign 
        having count(e.id) < d.frequency 
    )
)
mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • Doesnt the second subquery makes a second temporary table? Isnt there a better way? :( – Boris D. Teoharov Apr 03 '13 at 19:57
  • 1
    @dudelgrincen: That's up to the database. You can often use a LEFT JOIN instead of another subquery but the HAVING requirement complicates things. I generally just try to express the logic clearly and let the database figure it out, if it is too slow then you look at the data, indexes, and query plan and start tuning. – mu is too short Apr 03 '13 at 20:34
0
SELECT * from campaigns WHERE id not in
(SELECT 
e.id_campaign, d.name, d.frequency, d.country, d.referral, d.bid, d.status, 
COUNT(e.id) AS countcap  
FROM campaigns d....
)

You are checking id not in so ,you should check only one column for check id.

you can write this (It may not fulfill your purpose): 

SELECT * from campaigns WHERE id not in
(SELECT 
e.id_campaign from campaigns d
LEFT JOIN served e
ON d.id = e.id_campaign 
WHERE 
    d.status = 'Active'
GROUP BY e.id_campaign
HAVING
    countcap < d.frequency
)
Moshiur Rahman
  • 1,534
  • 12
  • 26
0

The 'not in' clause expects a list of values. So, a query such as the following will work:

SELECT * from campaigns WHERE id not in
(SELECT 
    e.id_campaign
    FROM campaigns d
    LEFT JOIN served e
    ON d.id = e.id_campaign 
    WHERE 
        d.status = 'Active'
    GROUP BY e.id_campaign
    HAVING
        COUNT(e.id) < d.frequency)

If you can tell us the structure of tables and what you want to select, we'll be able to figure out the right query for you.

Chandranshu
  • 3,669
  • 3
  • 20
  • 37