[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 373
  • Last Modified:

Subqueries

Good Morning,

Im getting stumped on a Select statement utilizing a subquery and was hoping to get some assistance?


Simple table with 3 fields --->

PK, Parent_ID,  Status_ID

1    15              1
2    15              0
3    15              0
4    17              1
5    17              1
6    18              0
7    18              0
8    21              1
9    21              0
10   21              0


I'm trying to come up with SQL Statement way to get the unique Parent_ID (listed once) if ALL the Status_ID's are 0.  There maybe times with the unique parent_ID is in the table 2 times or 100 times.  In the example above, 18 would be the only item returned.



1    15              1
2    15              0
3    15              0
4    17              1
5    17              1
6    18              0
7    18              0
8    21              0
9    21              0
10   21              0


18 & 21 would be returned here.
15 would not be returned because it has 1 entries where the status_id = 1
17 would not be returned because it has 2 entries where the status_id = 1



basically I'm looking how to make this work ...

SELECT Parent_ID
 FROM MyTableName
 WHERE (COUNT(Status_ID = '1') < 1 for the Parent_ID)
 Group By Parent_ID;

thanks is advance
0
nmachin
Asked:
nmachin
  • 5
  • 4
  • 3
  • +2
2 Solutions
 
Patrick MatthewsCommented:
nmachin said:
>>18 & 21 would be returned here.

Are you sure about that?  21 seems to have a row with status 1...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do it:
select parent_id
  from yourtable
group by parent_id
 where max(status_id) <> 1

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
matthespatrick: there are 2 data samples, and for the first, only 18 should be returned :)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
RiteshShahCommented:
do you want to get the set which has zero more than time but don't have any 1?
0
 
Patrick MatthewsCommented:
SELECT q1.Parent_ID
FROM
      (SELECT t1.Parent_ID, COUNT(t1.Parent_ID) AS Qty
      FROM SomeTable t1
      GROUP BY t1.Parent_ID) q1 INNER JOIN
      (SELECT t2.Parent_ID, COUNT(t2.Parent_ID) AS Qty
      FROM SomeTable t2
      WHERE t2.Status_ID = 0
      GROUP BY t2.Parent_ID) q2 ON q1.Parent_ID = q2.Parent_ID AND t1.Status_ID = t2.Status_ID
ORDER BY q1.Parent_ID
0
 
RiteshShahCommented:
AngelIII, if author needs the result you assumed than you query need bit twicking.

select parent_id
  from yourtable
 where max(status_id) <> 1
group by parent_id
0
 
Patrick MatthewsCommented:
angelIII said:
>>matthespatrick: there are 2 data samples, and for the first, only 18 should be returned :)

Yeah, I need to read a little more closely :)
0
 
nmachinAuthor Commented:
angelIII,
when I try to run that example, I'm getting 'An aggregate may not appear in the Where clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated in an outer reference'
0
 
Patrick MatthewsCommented:
nmachin,

In retrospect, while my query should work, it is more complex than it needs to be.  I would adopt angelIII's/
RiteshShah's approach, with a twist: test to see that the max *and the min* of the STatus_ID column equals
zero.

Regards,

Patrick
0
 
Philip PinnellCommented:
Think it should be

select parent_id
  from yourtable
group by parent_id
 having max(status_id) <> 1
0
 
RiteshShahCommented:
actually, not even where, there should be HAVING.

select parent_id
  from yourtable
group by parent_id
having max(status_id) <> 1
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, for that, indeed HAVING instead of WHERE:
select parent_id
  from yourtable
group by parent_id
having max(status_id) <> 1

Open in new window

0
 
nmachinAuthor Commented:
Hey Patrick,

I'm getting a little lost (sorry, I'm a SQL Novice) on your example since I'm only using 1 table.
0
 
RiteshShahCommented:
"sorry, for that, indeed HAVING instead of WHERE:"

AngelIII, don't need to, it happens sometime otherwise, you are a legend.
0
 
Patrick MatthewsCommented:
nmachin said:
>>I'm getting a little lost (sorry, I'm a SQL Novice) on your example since I'm only using 1 table.

I am only using a single table in my example; you would just replace SomeTable with your actual table
name.

That said, as I indicated above, my approach is probably more complicated than it needs to be; in retrospect
I should have used something similar to angelIII and RiteshShah, such as:

select parent_id
  from yourtable
group by parent_id
having max(status_id) = 0 AND min(status_id) = 0

Points should go to them if you use that approach.
0
 
nmachinAuthor Commented:
Thanks again for all your help!!!  You definitely made this look easy!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now