Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Filtering shaped recordsets Expand / Collapse
Author
Message
Posted Wednesday, May 11, 2005 11:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 29, 2009 9:18 AM
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
Post #181557
Posted Wednesday, May 11, 2005 11:31 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Monday, September 22, 2014 6:13 AM
Points: 20,578, Visits: 9,618
Can you post the query and the ddl so we can show you how to do it?
Post #181563
Posted Wednesday, May 11, 2005 11:55 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 5, 2013 12:21 PM
Points: 265, Visits: 164
also post the structure of your tables ( the parent, child) ones..

******************
Dinakar Nethi
Life is short. Enjoy it.
******************
Post #181575
Posted Thursday, May 12, 2005 12:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 2, 2007 7:35 AM
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 (....) )

 

 

Post #181702
Posted Thursday, May 12, 2005 3:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 29, 2009 9:18 AM
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
Post #181738
Posted Thursday, May 12, 2005 6:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 9, 2007 1:33 PM
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

Post #181785
Posted Thursday, May 12, 2005 6:36 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, May 20, 2006 9:25 PM
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 

*/

Post #181800
Posted Thursday, May 12, 2005 7:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, May 20, 2006 9:25 PM
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

Post #181810
Posted Thursday, May 12, 2005 7:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 29, 2009 9:18 AM
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

Post #181817
Posted Thursday, May 12, 2005 8:29 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:47 AM
Points: 6,259, Visits: 2,029


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
Post #181876
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse