SQL Query Parent-Child
I have a couple of SQL server tables.
P contains id and name. PR contains ID, Percentage, Quantity, Date, Date and Time. A PR can contain many lines as listed at p.id/level. (Levels are a list of the rates a product can have in any given time frame.)
for example: Product 1 level 1 starts from 1/1/2008 to 1/1/2009 and has 6 rates shown as 1 row per course. Product 1 level 2 starts on 1/2/2009, etc. Etc.
I need an idea of this that shows the name and name of the PR.tiernumber and dates ... BUT I only want one line to represent the level.
It's easy:
SELECT DISTINCT P.ID, P.PRODUCTCODE, P.PRODUCTNAME, PR.TIERNO,
PR.FROMDATE, PR.TODATE, PR.PRODUCTID
FROM dbo.PRODUCTRATE AS PR INNER JOIN dbo.PRODUCT AS P
ON P.ID = PR.PRODUCTID
ORDER BY P.ID DESC
This gives me the exact correct data ... However: it prevents me from seeing the PR.ID as it negates the distinctive.
I need to constrain the result set because the user just needs to see only the level list, I need to see the PR.ID showing all the data.
Any ideas?
SELECT P.ID, P.ACUPRODUCTCODE, P.PRODUCTNAME, PR.TIERNO,
PR.FROMDATE, PR.TODATE, PR.PRODUCTID, MIN(PR.ID)
FROM dbo.PRODUCTRATE AS PR INNER JOIN dbo.PRODUCT AS P
ON P.ID = PR.PRODUCTID
GROUP BY P.ID, P.ACUPRODUCTCODE, P.PRODUCTNAME, PR.TIERNO,
PR.FROMDATE, PR.TODATE, PR.PRODUCTID
ORDER BY P.ID DESC
Got to do this job. GROUP BY instead of DISTINCT, with a summary function (MIN) to get a specific value for PR.ID.
a source to share
It sounds like you want to accomplish two different things with the same query, which doesn't make sense. Either you want a list of item / level / date information, or you want a list of interest rates.
If you want to choose a specific PR.ID to work with your data, you need to decide what the rule is for - what determines which ID you want to return?
a source to share