ReneS
Posts: 225
|
| Posted: 06/27/2006, 10:35 AM |
|
Hi,
As the title says:
I have a table with:
ID, parentID, childID, childType
There are multiple records with the same parentID that have different childID's and sometimes different childType.
So:
parentID: 123, childID: 454 childType 45
parentID: 123, childID: 455 childType 45
parentID: 123, childID: 456 childType 96
parentID: 789, childID: 457 childType 45
etc.
Now what I am trying to do is to get only those parentID's with 1 particular childType.
So in this case only parentID: 789
Any help is appreciated
Thanks,
Rene
|
 |
 |
E43509
Posts: 283
|
| Posted: 06/27/2006, 10:49 AM |
|
basically using aggregate functions in sql
select count(*), parentid
from mytable
group by parentid
having count(*) = 1
Then you can put in a where clause to look for a particular childtype
select parentid
from mytable
where childtype = 45
group by parentid
having count(*) = 1
|
|
|
|
DonB
|
| Posted: 06/28/2006, 11:14 AM |
|
SELECT DISTINCT(parentID) FROM myTable WHERE childType=457
or amy I missing something here?
--
DonB
http://www.gotodon.com/ccbth
|
|
|
|
Rene S
|
| Posted: 06/28/2006, 1:53 PM |
|
Hi Don,
I think my explanation was to short, or I'm missing something, or you are (I'm being carefull)
I only want the parentID with that one particular childID. So I don't want a "parent" to have other "children" than a particular childType.
so:
ID: 1, parentID: 123, childID: 454, childType 45
ID: 2, parentID: 123, childID: 455, childType 45
ID: 3, parentID: 123, childID: 456, childType 96
ID: 4, parentID: 789, childID: 457 childType 45
ID: 5, parentID: 789, childID: 458 childType 45
So if looking for childType 45, I would like to have only ID 4 and 5 and not 1 and 2.
Have not been able to try suggested solutions, and I'm not very good at it, but I'll let you know.
Thanks,
Rene
|
|
|
|
peterr
Posts: 5971
|
| Posted: 06/28/2006, 2:06 PM |
|
Not sure, but I suspect that the syntax may depend on your database type and version.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Rene S
|
| Posted: 06/28/2006, 2:13 PM |
|
Hi,
That was quick!
Database is MS SQL-server 2000
Thanks,
Rene
|
|
|
|
peterr
Posts: 5971
|
| Posted: 06/28/2006, 2:24 PM |
|
OK. I don't have access to that database but it supports sub-queries, thus my approach would be: create a query that selects all ParentID's that contain other ChildTypes than the one you want, like:
SELECT parentID WHERE childType<>45
Then create a 2nd query that selects parentID with the childType you want, but NOT IN the previous query. Thus maybe something like this will work:
SELECT parentID WHERE childType=45 AND parentID NOT IN (SELECT parentID WHERE childType<>45)
I don't know if my syntax is correct or if this is best approach. Just an idea.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Rene S
|
| Posted: 06/28/2006, 2:44 PM |
|
Thanks all,
will try on friday, will let you know what works.
Good night,
Rene
|
|
|
|
DonB
|
| Posted: 06/28/2006, 7:35 PM |
|
OK, so you need only the ones where parentID does not overlap another
childType.
Peter expressed it in one way, but omitted the 'FROM' clauses
SELECT ID FROM pt
WHERE childType=45
AND parentID NOT IN (
SELECT parentID
FROM pt
WHERE childType<>45)
Here is another equivalent way to express it.
SELECT ID
FROM myTable AS A
WHERE NOT EXISTS (
SELECT * FROM myTable AS b
WHERE a.parentID = b.ParentID
AND a.childType <> b.childType
)
AND a.childType = 45
If the table contains many rows, the second option might be a bit faster, as
it would subquery on parentID and possibly reduce the size of the
intermediate results (the subquery). You probably won't have enought rows
to make the difference apparent.
--
DonB
http://www.gotodon.com/ccbth
<ReneS@forum.codecharge (Rene S)> wrote in message
news:244a2ec661f9a4@news.codecharge.com...
> Hi Don,
>
> I think my explanation was to short, or I'm missing something, or you are
(I'm
> being carefull)
> I only want the parentID with that one particular childID. So I don't want
a
> "parent" to have other "children" than a particular childType.
> so:
> ID: 1, parentID: 123, childID: 454, childType 45
> ID: 2, parentID: 123, childID: 455, childType 45
> ID: 3, parentID: 123, childID: 456, childType 96
> ID: 4, parentID: 789, childID: 457 childType 45
> ID: 5, parentID: 789, childID: 458 childType 45
>
> So if looking for childType 45, I would like to have only ID 4 and 5 and
not 1
> and 2.
>
> Have not been able to try suggested solutions, and I'm not very good at
it, but
> I'll let you know.
>
> Thanks,
>
> Rene
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
|
