Filtering shaped recordsets
-
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
-
Can you post the query and the ddl so we can show you how to do it?
-
also post the structure of your tables ( the parent, child) ones..
******************
Dinakar Nethi
Life is short. Enjoy it.
****************** -
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 (....) )
-
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
-
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
-
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
*/
-
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
-
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
-
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 -
Thanks noeld, that works - in theory!
Unfortunately, the design of the system makes implementing this solution complicated.
I allow the user to set a variety of filters on each element in the query therefore these filters would also need be applied on this sub-query. For example, the user may have filtered the Qualifications to only be current and provided by a certain company.
I would have to write a generic function to return just the filter part of the SQL statement for each element and then duplicate this in the sub-query. Possible - but a large shift to the core query engine at a late stage of the project.
Unless another way can be found...
cheers, dave
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply