Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Filtering shaped recordsets


Filtering shaped recordsets

Author
Message
David Perry-232147
David Perry-232147
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 9
Hi, Can anyone help me with a SQL data shaping problem thats taken away most of my day!

I want to exclude rows from the parent recordset where there are no child records.

For example, Given the following data set

Parent1
--|---Child
--|---Child
Parent2
Parent3
--|---Child

i want to exclude Parent2 from the results (it has no children)

I have managed to get an aggregated field in the parent recordset with a count of the number of records but any attempt to actually limit the results has been unsuccesful.

I currently post process the data and use the recordset.Filter to achieve the result but this is quite ineffecient.

I can post some example SQL if that will help. Any pointers would be much appreciated

Dave
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
Can you post the query and the ddl so we can show you how to do it?
Dinakar Nethi-176633
Dinakar Nethi-176633
SSC Veteran
SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)

Group: General Forum Members
Points: 273 Visits: 188
also post the structure of your tables ( the parent, child) ones..

******************
Dinakar Nethi
Life is short. Enjoy it.
******************
David Mrazek
David Mrazek
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1

I don't have your tables, bat I can try to help you.

I would try to use for exampl> SELECT * FROM table WHERE parents IN(SELECT parents FROM table_parents WHERE (....) )


David Perry-232147
David Perry-232147
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 9
Here is an example of a SHAPE command that returns the qualifications records for all of the people

SHAPE { SELECT * FROM DT_PERSON }
APPEND({SELECT * FROM DT_QUALIFICATION} RELATE Person_ID TO Person_ID) AS ChildRS

I can append a field to the parent set indicating the number of qualifications for each person using:

SHAPE { SELECT * FROM DT_PERSON }
APPEND({SELECT * FROM DT_QUALIFICATION} RELATE Person_ID TO Person_ID) AS ChildRS, COUNT(ChildRS.Ndx) AS ChildRS_Count

However I cannot seem to figure out where to put the WHERE clause to restrict the parent rows returned, for example, to only include people who have at least one qualification.

I would have thought this would have been a fairly common thing to want to do but i have yet to find any examples of whether it is even possible

Cheers, Dave
Mark Resnicoff
Mark Resnicoff
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 1

If I understand your question correctly and you have a parent table and a child table, join the two tables with an INNER JOIN. This will retrieve only the parent records that have at least one associated child record.

Example:

SELECT * FROM DT_PERSON P INNER JOIN DT_QUALIFICATION Q ON P.Person_ID = Q.Person_ID

If you use a LEFT OUTER JOIN, then parent records without any child records would also be retrieved.

I hope this helps.

Mark


Michael Du Bois
Michael Du Bois
Old Hand
Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)

Group: General Forum Members
Points: 383 Visits: 1

David try this

HTH Mike

IF Object_ID('TempDB..#Parent')>0
DROP TABLE #Parent
IF Object_ID('TempDB..#Child') > 0
DROP TABLE #Child

CREATE TABLE #Parent
(
Pk int,
OtherStuff VarChar(20)

)

CREATE TABLE #Child
(

FKParent int

)

DECLARE @Count int
Set @Count = 0
While @Count <10
Begin
Set @Count = @Count + 1
INSERT INTO #Parent(PK)
VALUES(@Count)
IF @Count < 5
INSERT INTO #Child(FKParent) VALUES(@Count)
End
GO
SELECT P1.pK
FROM #Parent p1
WHERE NOT EXISTS
(
SELECT p.pk
FROM
#Child c
JOIN
#Parent p
ON c.FKParent = P1.pk
 

--
/*

Results 5,6,7,8,9,10

Records 1-4 have parents

Edited to change Mark to David. Addressed the wrong person sorry

*/


Michael Du Bois
Michael Du Bois
Old Hand
Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)

Group: General Forum Members
Points: 383 Visits: 1

If you cut and paste my code you will have to insert a ) at the end of the second select statement. Love those expressions

Mike


David Perry-232147
David Perry-232147
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 9
Hi, These are all good solutions but I am trying to solve the problem using hierarchical recordsets! I have built a generic datamodel and the user designs their queries in a GUI. The resulting SQL statement is based on the SHAPE APPEND syntax so using a conventional INNER JOIN is unfortunately not applicable in this case.

Dave
noeld
noeld
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6320 Visits: 2048


SHAPE { SELECT * FROM DT_PERSON where exists (select * from DT_QUALIFICATION q where DT_PERSON.PersonID = q.Person_ID) }
APPEND({SELECT * FROM DT_QUALIFICATION} RELATE Person_ID TO Person_ID) AS ChildRS

Would something like that works?




* Noel
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search