Hierarchy, Max(Date) and Inner Joins

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

General SQL Server Forums

New to SQL Server Programming

Hierarchy, Max(Date) and Inner Joins

Author

Topic

ssimon
Starting Member

16 Posts

Posted - 2008-06-13 : 14:31:19

Ok first I am still very new to SQL so I really do not understand quite a bit (SP's, Sub Query's,CTE, etc..). I also went through the SQLTeam article on trees and hierachies but only confused myself even more.

I will try my best to put all the information that I know of (in the shortest way that I know).....sorry I know this is long :(

SQLExpress2005
SQL Server Management Studio Express

Two Tables

PartMaster
PRTNUM_01.......varchar(30) ////Part Number
PMDES1_01.........varchar(50) ////Primary Description
PMDES2_01.........varchar(50) ////Secondary Description

PRTNUM_01........PMDES1_01............PMDES2_01
100700..............Slide. .................6-inch
102121..............Video Card...........128MB
IPC4235-01........Chassis..............Parts
IPC4235-03........Chassis..............Parts
102128..............Riser................Card
102385..............Mother...............Board
801549..............Bracket,.............Power
801709..............Bracket..............Fan
100773..............Hardware.............Hinge

ProductStructure
PARPRT_02........varchar(30) ////Top Level Part Number
COMPRT_02......varchar(50) ////Component Part Number
EFFDTE_02.........smalldatetime //// Effective Date
QTYPER_02.........float ////// Quantity

PARPRT_02.............COMPRT_02..........EFFDTE_02....QTYPER_02
4235........................100700............2008-08-30.......1
4235........................102121............2007-08-30.......1
4235........................IPC4235-01......2008-05-20.......1
4235........................IPC4235-03......2007-08-30.......1
4235........................IPC4235-03......2008-05-20.......0
IPC4235-01...............102128............2007-11-19.......1
IPC4235-01...............102385............2007-11-19.......1
IPC4235-01...............801549............2007-11-19.......1
IPC4235-01...............801709............2008-04-01........1
IPC4235-01...............801709............2007-05-20........0
IPC4235-03...............100773............2007-09-20........1

--------What I am trying to accomplish is the retrieve the following:

PARPRT_02|COMPRT_02|Des|SubComp|Sub#|SubDes|EFFDTE_02|QTYPER_02
4235|100700|Slide 6-inch|n.a|n.a|n.a|2008-08-30|1
4235|102121|Video Card 128MB|n.a|n.a|n.a|2007-08-30|1
4235|IPC4235-01|Chassis Parts|IPC4235-01|102128|Riser Card|2007-11-19|1
4235|IPC4235-01|Chassis Parts|IPC4235-01|102385|Mother Board|2007-11-19|1
4235|IPC4235-01|ChassisParts|IPC4235-01|801549|Bracket,Power|2007-11-19|1

I have gotten Close but no cigar with the following:


SELECT ps.PARPRT_02, 
ps.COMPRT_02, 
pm.PMDES1_01 + ' ' + pm.PMDES2_01 as Description, 
ISNULL(ps2.PARPRT_02, 'n.a.') AS [Sub Component], 
ISNULL(ps2.COMPRT_02, 'n.a.') AS [Part Number of Sub],
ps.EFFDTE_02, ps.QTYPER_02
FROM ProductStructure ps 
INNER JOIN
 (SELECT     COMPRT_02, MAX(EFFDTE_02) AS EFFDTE_02
  FROM       ProductStructure
  WHERE      (PARPRT_02 = '4235')
  GROUP BY PARPRT_02, COMPRT_02) AS p3 
ON ps.COMPRT_02 = p3.COMPRT_02 AND ps.EFFDTE_02 = p3.EFFDTE_02
Inner Join PartMaster AS pm On pm.PRTNUM_01 = ps.COMPRT_02
LEFT JOIN ProductStructure ps2
ON ps2.PARPRT_02 = ps.COMPRT_02
WHERE ps.PARPRT_02 = '4235' 
ORDER BY 
ps.PARPRT_02, ps.COMPRT_02

Which is returning:

PARPRT_02|COMPRT_02|Description|Sub Component|Part Number of Sub|EFFDTE_02|QTYPER_02
4235|100700|Slide 6-inch|n.a|n.a|2008-08-30|1
4235|102121|Video Card 128MB|n.a|n.a|2007-08-30|1
4235|IPC4235-01|Chassis Parts|IPC4235-01|102128|2008-05-20|1
4235|IPC4235-01|Chassis Parts|IPC4235-01|102385|2008-05-20|1
4235|IPC4235-01|Chassis Parts|IPC4235-01|801549|2008-05-20|1
4235|IPC4235-01|Chassis Parts|IPC4235-01|801709|2008-05-20|1
4235|IPC4235-01|Chassis Parts|IPC4235-01|801709|2008-05-20|1
4235|IPC4235-03|Chassis Parts|IPC4235-03|100773|2008-05-20|0

Line 1 - fine / Line 2 - fine / Line three (need to add description Part number of Sub)(need to keep original) / Line 4 and 5 same as line 3 / Line 6 and Line 7 should not show because the later date has a QTY of zero / Line 9 should not show because date is zero.

I once again appologize for the length and details on this , but this is has been driving me nutts. ANY help will be appreciated.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-14 : 01:21:13

[code]SELECT ps.PARPRT_02,ps.COMPRT_02,
COALESCE(pm.PMDES1_01,'') + ' ' + COALESCE(pm.PMDES2_01,'') AS Des,
COALESCE(tmp.PARPRT_02,'n.a') AS SubComp,
COALESCE(tmp.COMPRT_02,'n.a') AS [Sub#],
COALESCE(tmp.SubDes,'n.a') AS SubDes,
COALESCE(tmp.EFFDTE_02,ps.EFFDTE_02) AS EFFDTE_02,
COALESCE(tmp.QTYPER_02,ps.QTYPER_02) AS QTYPER_02
FROM ProductStructure ps
INNER JOIN PartMaster pm
ON pm.PRTNUM_01=ps.COMPRT_02
LEFT JOIN (SELECT ps1.PARPRT_02,ps1.COMPRT_02,
COALESCE(pm1.PMDES1_01,'') + ' ' + COALESCE(pm1.PMDES2_01,'') AS SubDes,
ps1.EFFDTE_02
FROM ProductStructure ps1
INNER JOIN PartMaster pm1
ON pm1.PRTNUM_01=ps1.COMPRT_02
INNER JOIN (SELECT PARPRT_02,COMPRT_02
FROM ProductStructure
GROUP BY PARPRT_02,COMPRT_02
HAVING SUM(CASE WHEN QTYPER_02=0 THEN 1 ELSE 0 END) =0)ps2
ON ps2.PARPRT_02=ps1.PARPRT_02
AND ps2.COMPRT_02=ps1.COMPRT_02
WHERE ps1.PARPRT_02<> '4235' )tmp
ON tmp.PARPRT_02=ps.COMPRT_02
WHERE ps.PARPRT_02='4235'
AND ps.QTYPER_02<>0[/code]

ssimon
Starting Member

16 Posts

Posted - 2008-06-16 : 14:43:35

Thank you for your assistance , however I get the following:
Msg 207, Level 16, State 1, Line 7
Invalid column name 'QTYPER_02'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'QTYPER_02'.

---Line 7----
COALESCE(tmp.QTYPER_02,ps.QTYPER_02) AS QTYPER_02

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-17 : 00:04:58

[code]SELECT ps.PARPRT_02,ps.COMPRT_02,
COALESCE(pm.PMDES1_01,'') + ' ' + COALESCE(pm.PMDES2_01,'') AS Des,
COALESCE(tmp.PARPRT_02,'n.a') AS SubComp,
COALESCE(tmp.COMPRT_02,'n.a') AS [Sub#],
COALESCE(tmp.SubDes,'n.a') AS SubDes,
COALESCE(tmp.EFFDTE_02,ps.EFFDTE_02) AS EFFDTE_02,
COALESCE(tmp.QTYPER_02,ps.QTYPER_02) AS QTYPER_02
FROM ProductStructure ps
INNER JOIN PartMaster pm
ON pm.PRTNUM_01=ps.COMPRT_02
LEFT JOIN (SELECT ps1.PARPRT_02,ps1.COMPRT_02,ps1.QTYPER_02,
COALESCE(pm1.PMDES1_01,'') + ' ' + COALESCE(pm1.PMDES2_01,'') AS SubDes,
ps1.EFFDTE_02
FROM ProductStructure ps1
INNER JOIN PartMaster pm1
ON pm1.PRTNUM_01=ps1.COMPRT_02
INNER JOIN (SELECT PARPRT_02,COMPRT_02
FROM ProductStructure
GROUP BY PARPRT_02,COMPRT_02
HAVING SUM(CASE WHEN QTYPER_02=0 THEN 1 ELSE 0 END) =0)ps2
ON ps2.PARPRT_02=ps1.PARPRT_02
AND ps2.COMPRT_02=ps1.COMPRT_02
WHERE ps1.PARPRT_02<> '4235' )tmp
ON tmp.PARPRT_02=ps.COMPRT_02
WHERE ps.PARPRT_02='4235'
AND ps.QTYPER_02<>0[/code]

ssimon
Starting Member

16 Posts

Posted - 2008-06-17 : 08:30:09

The query is returning without errors now but still one small problem.

If the Parent Componet (COMPRT_02) has the greater date and has Zero as Quantity (QTYPER_02) then any Childern Components (tmp.COMPRT_02) should not show.

When I run the query it still returns
PARPRT_02|COMPRT_02 |Des...........|SubComp.....|Sub#..|SubDes..........|EFFDTE_02.|QTYPER_02
4235.........|IPC4235-03|Chassis Parts|IPC4235-03|100773|Hardware Hinge|2007-09-20|1

In ProductStructure.......
4235........................IPC4235-03......2007-08-30.......1
4235........................IPC4235-03......2008-05-20.......0
IPC4235-03...............100773............2007-09-20........1

How do we get rid of Sub# 100773 since it's parent IPC4235-03 is Zero?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-17 : 08:39:31

quote:
Originally posted by ssimon

The query is returning without errors now but still one small problem.

If the Parent Componet (COMPRT_02) has the greater date and has Zero as Quantity (QTYPER_02) then any Childern Components (tmp.COMPRT_02) should not show.

When I run the query it still returns
PARPRT_02|COMPRT_02 |Des...........|SubComp.....|Sub#..|SubDes..........|EFFDTE_02.|QTYPER_02
4235.........|IPC4235-03|Chassis Parts|IPC4235-03|100773|Hardware Hinge|2007-09-20|1

In ProductStructure.......
4235........................IPC4235-03......2007-08-30.......1
4235........................IPC4235-03......2008-05-20.......0
IPC4235-03...............100773............2007-09-20........1

How do we get rid of Sub# 100773 since it's parent IPC4235-03 is Zero?

do you mean this should not be returned at all? or is it enough just to make sub# n.a

ssimon
Starting Member

16 Posts

Posted - 2008-06-17 : 08:51:38

I would actually not like it to show, I might still be able to work with the returned data if it did return n.a. within the Sub# column but I would have to work that out in other code. (History - this is a returned query for a parts list within a ASP.NET page - A customer logs in , enters the Top Level Model of their computer and the return query list all active parts as of the last updated database.......not that this would matter - just thought I would throw it in :) )...........Thanks again ... you have no idea how much this has caused my hair to fall out

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-17 : 09:40:59

[code]SELECT ps.PARPRT_02,ps.COMPRT_02,
COALESCE(pm.PMDES1_01,'') + ' ' + COALESCE(pm.PMDES2_01,'') AS Des,
COALESCE(tmp.PARPRT_02,'n.a') AS SubComp,
CASE WHEN tmp1.PARPRT_02 IS NULL THEN 'n.a'
ELSE COALESCE(tmp.COMPRT_02,'n.a') END AS [Sub#],
COALESCE(tmp.SubDes,'n.a') AS SubDes,
COALESCE(tmp.EFFDTE_02,ps.EFFDTE_02) AS EFFDTE_02,
COALESCE(tmp.QTYPER_02,ps.QTYPER_02) AS QTYPER_02
FROM ProductStructure ps
INNER JOIN PartMaster pm
ON pm.PRTNUM_01=ps.COMPRT_02
LEFT JOIN (SELECT ps1.PARPRT_02,ps1.COMPRT_02,ps1.QTYPER_02,
COALESCE(pm1.PMDES1_01,'') + ' ' + COALESCE(pm1.PMDES2_01,'') AS SubDes,
ps1.EFFDTE_02
FROM ProductStructure ps1
INNER JOIN PartMaster pm1
ON pm1.PRTNUM_01=ps1.COMPRT_02
INNER JOIN (SELECT PARPRT_02,COMPRT_02
FROM ProductStructure
GROUP BY PARPRT_02,COMPRT_02
HAVING SUM(CASE WHEN QTYPER_02=0 THEN 1 ELSE 0 END) =0)ps2
ON ps2.PARPRT_02=ps1.PARPRT_02
AND ps2.COMPRT_02=ps1.COMPRT_02
WHERE ps1.PARPRT_02<> '4235' )tmp
ON tmp.PARPRT_02=ps.COMPRT_02
LEFT JOIN (SELECT PARPRT_02,COMPRT_02
FROM ProductStructure
GROUP BY PARPRT_02,COMPRT_02
HAVING SUM(CASE WHEN QTYPER_02=0 THEN 1 ELSE 0 END)=0

)tmp1
ON tmp1.PARPRT_02=ps.PARPRT_02
AND tmp1.COMPRT_02=ps.COMPRT_02
WHERE ps.PARPRT_02='4235'
AND ps.QTYPER_02<>0[/code]

ssimon
Starting Member

16 Posts

Posted - 2008-06-17 : 10:43:17

I added

WHERE ps.PARPRT_02='4235ARG-03' AND tmp1.PARPRT_02 IS NOT NULL

I am going to test this out with my other code but I think you just solved something I have been working on for a very long time!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources