[SOLVED] Query Help Needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

[SOLVED] Query Help Needed

Author

Topic

timbyng
Starting Member

6 Posts

Posted - 2008-04-11 : 12:27:08

Hello,

First of all, sorry about not having a more descriptive title. I'm not sure how to describe this in one line. I think I can best explain it with an example. Here's some script that will produce some sample data that I would like to work with:

DECLARE @Parent TABLE (
	ParentID INT,
	Name VARCHAR(50)
)

DECLARE @ChildA TABLE (
	ParentID INT,
	Name VARCHAR(50)
)

DECLARE @ChildB TABLE (
	ParentID INT,
	Name VARCHAR(50)
)

DECLARE @ChildC TABLE (
	ParentID INT,
	Name VARCHAR(50)
)

INSERT @Parent SELECT 1, 'Parent1'
INSERT @ChildA SELECT 1, 'ChildA1'
INSERT @ChildB SELECT 1, 'ChildB1'
INSERT @ChildB SELECT 1, 'ChildB2'
INSERT @ChildB SELECT 1, 'ChildB3'
INSERT @ChildC SELECT 1, 'ChildC1'
INSERT @ChildC SELECT 1, 'ChildC2'

Here's what how the client would like the data to look in a report:


Parent1	ChildA1	ChildB1	ChildC1
       	        ChildB2	ChildC2
       	        ChildB3

Basically, I would like to use as few lines of data to show the results. I'm at a loss on how to do this.

Of course the following won't work:


SELECT
	P.Name ParentName,
	CA.Name ChildAName,
	CB.Name ChildBName,
	CC.Name ChildCName
FROM
	@Parent P
	LEFT JOIN @ChildA CA ON P.ParentID = CA.ParentID
	LEFT JOIN @ChildB CB ON P.ParentID = CB.ParentID
	LEFT JOIN @ChildC CC ON P.ParentID = CC.ParentID

This will produce the following results:


Parent1	ChildA1	ChildB1	ChildC1
Parent1	ChildA1	ChildB1	ChildC2
Parent1	ChildA1	ChildB2	ChildC1
Parent1	ChildA1	ChildB2	ChildC2
Parent1	ChildA1	ChildB3	ChildC1
Parent1	ChildA1	ChildB3	ChildC2

Any ideas on how to achieve this? There might be examples out there already, but I don't know what to search for.

Thanks,
Tim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-11 : 13:08:58

Are you trying to show it in some report. Then i guess it would be more easier to do this at report side.

timbyng
Starting Member

6 Posts

Posted - 2008-04-11 : 13:17:52

quote:
Originally posted by visakh16

Are you trying to show it in some report. Then i guess it would be more easier to do this at report side.

How so?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-11 : 13:19:08

Most reporting applications have options to balnk repetitive values in successive rows.

timbyng
Starting Member

6 Posts

Posted - 2008-04-11 : 13:37:34

quote:
Originally posted by visakh16

Most reporting applications have options to balnk repetitive values in successive rows.

That's true. I am using Crystal Reports and I have the option to suppress if duplicated for the column and/or row. However, suppressing on a duplicate row won't help, as there are no duplicates. Suppressing on a duplicate column will still leave me with 6 records from the example above. For example, I'd have this:


Parent1	ChildA1	ChildB1	ChildC1
               	       	ChildC2
               	ChildB2	ChildC1
               	       	ChildC2
               	ChildB3	ChildC1
               	       	ChildC2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-11 : 13:47:53

Do you have any other fields to designate that ChildB1 should link to only ChildC1 and so on?

timbyng
Starting Member

6 Posts

Posted - 2008-04-11 : 13:59:51

No, I don't have anything like that.

willpost
Starting Member

4 Posts

Posted - 2008-04-11 : 23:57:18

Add an ordinal column (integer) for each Child table
When each record is created find the highest number for that ParentID, increment by one and store that value

ChildA
ParentID:1 Ordinal:1 Name:ChildA1

ChildB
ParentID:1 Ordinal:1 Name:ChildB1
ParentID:1 Ordinal:2 Name:ChildB2
ParentID:1 Ordinal:3 Name:ChildB3

ChildC
ParentID:1 Ordinal:1 Name:ChildC1
ParentID:1 Ordinal:2 Name:ChildC2

Then you need a union of all possible Parents and Ordinals:

qryParentOrdinalUnion
---------------------
SELECT ParentID, Ordinal
FROM ChildA
UNION
SELECT ParentID, Ordinal
FROM ChildB
UNION SELECT ParentID,Ordinal
FROM ChildC;

Then join the result to the parent and child tables

qryParentChild
--------------
SELECT Parent.Name AS Parent_Name, qryParentOrdinalUnion.Ordinal, ChildA.Name AS ChildA_Name, ChildB.Name AS ChildB_Name, ChildC.Name AS ChildC_Name
FROM (((qryParentOrdinalUnion LEFT JOIN ChildA ON (qryParentOrdinalUnion.Ordinal = ChildA.Ordinal) AND (qryParentOrdinalUnion.ParentID = ChildA.ParentID)) LEFT JOIN Parent ON qryParentOrdinalUnion.ParentID = Parent.ParentID) LEFT JOIN ChildB ON (qryParentOrdinalUnion.Ordinal = ChildB.Ordinal) AND (qryParentOrdinalUnion.ParentID = ChildB.ParentID)) LEFT JOIN ChildC ON (qryParentOrdinalUnion.Ordinal = ChildC.Ordinal) AND (qryParentOrdinalUnion.ParentID = ChildC.ParentID);


Result:
Parent_Name Ordinal ChildA_Name ChildB_Name ChildC_Name
Parent1           1     ChildA1     ChildB1     ChildC1
Parent1           2                 ChildB2     ChildC2
Parent1           3                 ChildB3

If you want to hide the Parent Name on the second and third rows, you need to put an if statement in the parent_name field. This is possible in Access with IIF or a crystal reports function. Doing it in SQL will be tricky, it may be possible with combinations of isnull and nullif.

timbyng
Starting Member

6 Posts

Posted - 2008-04-14 : 07:43:37

Thanks willpost! I'll have to see if it's possible to add the ordinal to the existing tables, which it should be. Even if I can't, I think I can still get it to work (I'll try to post an example). It might be a couple of days before I get back to you, but this looks perfect.

I can definitely suppress the duplicate parent name in Crystal Reports, so having the name repeated isn't an issue.

I'll post my results as soon as possible.

Thanks again!
Tim

timbyng
Starting Member

6 Posts

Posted - 2008-04-14 : 08:33:05

I'm still not sure if I'll be able to add the Ordinal field to the child tables. In the meantime, I have come up with a solution that will work that generates the Ordinal on the fly. This is possible by massaging the data, and then running this data through willpost's code. Here is the complete example of how to do this:

DECLARE @Parent TABLE (
	ParentID INT,
	Name VARCHAR(50)
)

DECLARE @ChildA TABLE (
	ParentID INT,
	Name VARCHAR(50)
)

DECLARE @ChildB TABLE (
	ParentID INT,
	Name VARCHAR(50)
)

DECLARE @ChildC TABLE (
	ParentID INT,
	Name VARCHAR(50)
)

DECLARE @ChildD TABLE (
	ParentID INT,
	Name VARCHAR(50)
)

INSERT @Parent SELECT 1, 'Parent1'
INSERT @ChildA SELECT 1, 'ChildA1'
INSERT @ChildA SELECT 1, 'ChildA2'
INSERT @ChildB SELECT 1, 'ChildB1'
INSERT @ChildB SELECT 1, 'ChildB2'
INSERT @ChildB SELECT 1, 'ChildB3'
INSERT @ChildB SELECT 1, 'ChildB4'
INSERT @ChildC SELECT 1, 'ChildC1'
INSERT @ChildC SELECT 1, 'ChildC2'
INSERT @ChildD SELECT 1, 'ChildD1'

DECLARE @OrdinalUnion TABLE(
	ParentID INT,
	Ordinal INT
)

DECLARE @TempChildA TABLE (
	ParentID INT,
	Ordinal INT,
	Name VARCHAR(50)
)

DECLARE @TempChildB TABLE (
	ParentID INT,
	Ordinal INT,
	Name VARCHAR(50)
)

DECLARE @TempChildC TABLE (
	ParentID INT,
	Ordinal INT,
	Name VARCHAR(50)
)

INSERT
	@TempChildA
SELECT
	ParentID,
	(SELECT COUNT(*) FROM @ChildA WHERE Name <= CA.Name) AS Ordinal,
	Name
FROM
	@ChildA AS CA

INSERT
	@TempChildB
SELECT
	ParentID,
	(SELECT COUNT(*) FROM @ChildB WHERE Name <= CB.Name) AS Ordinal,
	Name
FROM
	@ChildB AS CB

INSERT
	@TempChildC
SELECT
	ParentID,
	(SELECT COUNT(*) FROM @ChildC WHERE Name <= CC.Name) AS Ordinal,
	Name
FROM
	@ChildC AS CC

INSERT
	@OrdinalUnion
SELECT
	ParentID,
	Ordinal
FROM
	@TempChildA AS CA
UNION
SELECT
	ParentID,
	Ordinal
FROM
	@TempChildB AS CB
UNION
SELECT
	ParentID,
	Ordinal
FROM
	@TempChildC AS CC

SELECT
	P.Name AS Parent_Name,
	U.Ordinal,
	CA.Name AS ChildA_Name,
	CB.Name AS ChildB_Name,
	CC.Name AS ChildC_Name
FROM
	@OrdinalUnion AS U
	LEFT JOIN @Parent AS P ON U.ParentID = P.ParentID
	LEFT JOIN @TempChildA AS CA ON U.ParentID = CA.ParentID AND U.Ordinal = CA.Ordinal
	LEFT JOIN @TempChildB AS CB ON U.ParentID = CB.ParentID AND U.Ordinal = CB.Ordinal
	LEFT JOIN @TempChildC AS CC ON U.ParentID = CC.ParentID AND U.Ordinal = CC.Ordinal

I am going to push to have the Ordinal added to the child tables, though. Having the Ordinal on the child table is definitely the way to go, as I'm sure it would perform much better.

Thanks to both willpost and visakh16! I really appreciate all of the help you've given me.

Regards,
Tim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources