I have a database with two tables, one of which depends on the other:
CREATE TABLE a(
a_id INTEGER PRIMARY KEY
b_id INTEGER);
CREATE TABLE c(
b_id INTEGER,
num INTEGER,
flag INTEGER,
PRIMARY KEY (b_id,num));
In words, each "a"
> SELECT a_id, count(*)
> FROM a LEFT OUTER JOIN c USING (b_id)
> WHERE c.flag = 1
> GROUP BY a_id
>
> I get a positive count if there are there are matching rows with flag = 1;
> but if an a_id has no matching rows in c with flag=1, I get nothing at all.
> What I want is a row with the a_id and
On 6/14/12 11:06 AM, "Pavel Ivanov" wrote:
>> SELECT a_id, count(*)
>> FROM a LEFT OUTER JOIN c USING (b_id)
>> WHERE c.flag = 1
>> GROUP BY a_id
>>
>> I get a positive count if there are there are matching rows with flag =
>>1; but if an a_id has no matching rows in c with flag=1, I get nothing
On 6/14/2012 2:00 PM, Duquette, William H (318K) wrote:
What I want to do is find a_id's for which c contains no rows with the matching
b_id in which the flag column is 1.
Why don't you just say that?
select a_id from a
where b_id not in (select b_id from c where flag = 1);
--
Igor Tandetnik
On 6/14/12 1:00 PM, "Igor Tandetnik" wrote:
>On 6/14/2012 2:00 PM, Duquette, William H (318K) wrote:
>> What I want to do is find a_id's for which c contains no rows with the
>>matching b_id in which the flag column is 1.
>
>Why don't you just say that?
>
>select a_id from a
>where b_id not in (
5 matches
Mail list logo
