April 4, 2007 at 9:40 am
I can't get this final part of the SQL statement, I need to get the MAX date from LotStat.InventoryDate out of this query but I don't want to return the date.
Any one have any ideas??
SELECT
B.BlockName AS Block,
I.Lot AS Lot,
R.RefCodeName AS LotType,
I.SaleableFrontFootage AS Frontage,
ISNULL(IA.Address + ', ','') + IA.CityName + ' ' + IA.ProvinceCode + ' ' + ISNULL(IA.PostalCode,'') AS Address,
AI.HouseStyle AS Style,
RC.RefCodeName AS Status,
Purch.PurchaserName AS Builder
FROM Inventory AS I
JOIN Block AS B
JOIN Phase AS P
JOIN ProjectSub AS PS
JOIN Project AS PJ
ON PS.ProjectID = PJ.ProjectID
ON P.ProjectSubID = PS.ProjectSubID
ON B.PhaseID = P.PhaseID
ON I.BlockID = B.BlockID
JOIN dbo.InventoryAddressByInventoryID(NULL) IA
ON I.InventoryID = IA.InventoryID
JOIN "User" AS U
ON I.UserID = U.UserID
LEFT JOIN RefCode AS R
ON I.LotTypeRefCodeID = R.RefCodeID
LEFT JOIN Inventory_mm_Purchaser as Builder
ON I.InventoryID = Builder.InventoryID
LEFT JOIN Purchaser as Purch
ON Builder.PurchaserID = Purch.PurchaserID
LEFT JOIN ArchitectureInformation as AI
ON I.InventoryID = AI.InventoryID
LEFT JOIN InventoryDate AS LotStat
ON I.InventoryID = LotStat.InventoryID
LEFT JOIN RefCode AS RC
ON LotStat.RefCodeID = RC.RefCodeID
WHERE PJ.ProjectName = 'Something'
AND P.PhaseID IN (114, 119, 120)
AND Purch.PurchaserName NOT LIKE '%SOMETHING%'
AND RC.RefCodeID IN (67, 71, 73)
Thanks in advance for any help.
Wade
April 4, 2007 at 10:00 am
First, put your ON clause after the join.
from a
join b
on a.x = b.y
join c
on a.x = c.y
etc.
Next, do you want the max() for each joined row? Use max(column) in your SELECT list, but then you need a GROUP BY after the WHERE with all your other columns listed.
April 4, 2007 at 10:06 am
Thank you for the quick reply
The on's should be after the joins, I think it's just how the board is formatting the code. I typically have it indented etc so it's easier to follow.
I do want the max for each row yes, when I use a max in the select and then a group by I get errors about the other items.
But also I don't want to return the date, just use it to filter on, so can I do it without using MAX in the select?
April 4, 2007 at 11:11 am
So when I change the query to this:
SELECT
B.BlockName AS Block,
I.Lot AS Lot,
R.RefCodeName AS LotType,
I.SaleableFrontFootage As Frontage,
ISNULL(IA.Address + ', ','') + IA.CityName + ' ' + IA.ProvinceCode + ' ' +
ISNULL(IA.PostalCode,'') AS Address,
AI.HouseStyle AS Style,
RC.RefCodeName AS Status,
Purch.PurchaserName AS Builder,
Max(LotStat.InventoryDate)
FROM Inventory AS I
JOIN Block AS B
JOIN Phase AS P
JOIN ProjectSub AS PS
JOIN Project AS PJ
ON PS.ProjectID = PJ.ProjectID
ON P.ProjectSubID = PS.ProjectSubID
ON B.PhaseID = P.PhaseID
ON I.BlockID = B.BlockID
JOIN dbo.InventoryAddressByInventoryID(NULL) IA
ON I.InventoryID = IA.InventoryID
JOIN "User" AS U
ON I.UserID = U.UserID
LEFT JOIN RefCode AS R
ON I.LotTypeRefCodeID = R.RefCodeID
LEFT JOIN Inventory_mm_Purchaser as Builder
ON I.InventoryID = Builder.InventoryID
LEFT JOIN Purchaser as Purch
ON Builder.PurchaserID = Purch.PurchaserID
LEFT JOIN ArchitectureInformation as AI
ON I.InventoryID = AI.InventoryID
LEFT JOIN InventoryDate AS LotStat
ON I.InventoryID = LotStat.InventoryID
LEFT JOIN RefCode AS RC
ON LotStat.RefCodeID = RC.RefCodeID
WHERE PJ.ProjectName = 'SOMETHING'
AND P.PhaseID IN (114, 119, 120)
AND Purch.PurchaserName NOT LIKE '%SOMETHING%'
AND RC.RefCodeID IN (67, 71, 73)
Group BY LotStat.InventoryDate
I get the following errors:
Msg 8120, Level 16, State 1, Line 1
Column 'B.BlockName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'I.Lot' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'R.RefCodeName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'I.SaleableFrontFootage' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'IA.Address' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'IA.CityName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'IA.ProvinceCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'IA.PostalCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'AI.HouseStyle' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'RC.RefCodeName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'Purch.PurchaserName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
My head hurts... =(
April 4, 2007 at 3:14 pm
Steve told you about the JOINs and ONs, but you still do NOT have the ONs after their JOINs.
FROM Inventory AS I
JOIN Block AS B
JOIN Phase AS P
JOIN ProjectSub AS PS
JOIN Project AS PJ
ON PS.ProjectID = PJ.ProjectID
ON P.ProjectSubID = PS.ProjectSubID
ON B.PhaseID = P.PhaseID
ON I.BlockID = B.BlockID
You CANNOT group the JOINs and the ONs separately. Well you can, but it won't work.
-SQLBill
April 5, 2007 at 7:46 am
So you can't nest joins?
April 5, 2007 at 8:26 am
What is "nesting"?
It's
FROM Inventory AS I
INNER JOIN Block AS B
ON I.BlockID = B.BlockID
INNER JOIN Phase AS P
ON B.PhaseID = P.PhaseID
INNER JOIN ProjectSub AS PS
ON P.ProjectSubID = PS.ProjectSubID
etc. Put them together and specify the join type.
Second, you need to group by all non-aggregated columns.
select id, name, max( score)
from table
group by id, name
So you need a group by that's
GROUP BY B.BlockName,
I.Lot,
R.RefCodeName,
I.SaleableFrontFootage,
Address,
AI.HouseStyle,
RC.RefCodeName,
Purch.PurchaserName
April 5, 2007 at 10:41 am
Steve thank you for your responses, sorry if I appear thick, I'm learning this by trial and error, against a system someone else built.
I think maybe I need to approach this a different way.
What I need to do is get a single result which will pull data from multiple tables.
I think I have what I need, but how can I restrict the results from the query below using a MAX(IStatus.LastModDate)? in the where clause?
SELECT B.BlockName AS Block,
I.Lot,
R.RefCodeName AS LotType,
I.SaleableFrontFootage As Frontage,
ISNULL(IA.Address + ', ','') + IA.CityName + ' ' + IA.ProvinceCode + ' ' + ISNULL(IA.PostalCode,'') AS Address,
AI.HouseStyle AS Style,
RC.RefCodeName AS Status,
PUR.PurchaserName as Builder
FROM Inventory AS I
JOIN Block AS B
INNER JOIN Phase AS P
INNER JOIN ProjectSub AS PS
INNER JOIN Project AS PJ
ON PS.ProjectID = PJ.ProjectID
ON P.ProjectSubID = PS.ProjectSubID
INNER JOIN PhaseSetup AS PSetup
ON P.PhaseID = PSetup.PhaseID
ON B.PhaseID = P.PhaseID
ON I.BlockID = B.BlockID
LEFT JOIN InventoryDate AS IStatus
ON I.InventoryID = IStatus.InventoryID
AND IStatus.RefCodeID IN (67, 71, 73) -- Open, Spec, Sale
LEFT JOIN RefCode AS R
ON I.LotTypeRefCodeID = R.RefCodeID
LEFT JOIN dbo.BuilderSaleByInventory(NULL) AS BSale
ON I.InventoryID = BSale.InventoryID
JOIN dbo.InventoryAddressByInventoryID(NULL) IA
ON I.InventoryID = IA.InventoryID
JOIN dbo.PurchaserByInventory(NULL) AS PUR
ON I.InventoryID = PUR.InventoryID
LEFT JOIN ArchitectureInformation as AI
ON I.InventoryID = AI.InventoryID
LEFT JOIN RefCode AS RC
ON IStatus.RefCodeID = RC.RefCodeID
WHERE PJ.ProjectName = 'Copperfield'
AND P.PhaseID IN (114, 119, 120)
AND Pur.PurchaserName NOT LIKE '%HRC%'
April 5, 2007 at 6:19 pm
Nope... I've seen this before... I call it "chain" join. If we look at Wade's join "chain"...
FROM Inventory AS I
JOIN Block AS B
JOIN Phase AS P
JOIN ProjectSub AS PS
JOIN Project AS PJ
ON PS.ProjectID = PJ.ProjectID
ON P.ProjectSubID = PS.ProjectSubID
ON B.PhaseID = P.PhaseID
ON I.BlockID = B.BlockID --serial "CHAIN" ends here
JOIN dbo.InventoryAddressByInventoryID(NULL) IA
ON I.InventoryID = IA.InventoryID
JOIN "User" AS U
ON I.UserID = U.UserID
LEFT JOIN RefCode AS R
ON I.LotTypeRefCodeID = R.RefCodeID
LEFT JOIN Inventory_mm_Purchaser as Builder
ON I.InventoryID = Builder.InventoryID
LEFT JOIN Purchaser as Purch
ON Builder.PurchaserID = Purch.PurchaserID
LEFT JOIN ArchitectureInformation as AI
ON I.InventoryID = AI.InventoryID
LEFT JOIN InventoryDate AS LotStat
ON I.InventoryID = LotStat.InventoryID
LEFT JOIN RefCode AS RC
ON LotStat.RefCodeID = RC.RefCodeID
... the diagram ends up looking like this (which is why I call it a "chain" join)...
I---B---P---PS---PJ
|
|---IA
|
|---U
|
|---<<R
|
|---<<Builder
|
|---<<Purch
|
|---<<AI
|
|---<<LotStat
|
|---<<RC
The optimizer in EM will sometimes try to resolve the long chain of I,B,P,PS,PJ (especially in the presence of the other joins) as a whole (similar to a derived table). As a result, it first identifies all of the entities in the chain all at once, and then it figures out the relationships for the ON's all at once.
Since I don't design databases with such long chains, I haven't tested this for perfomance v.s. doing it the normal way, but I could see it being a bit more efficient... maybe not.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 9, 2007 at 5:00 pm
Hi everyone, I'm really struggling with this and hoping someone can help me
AND I need to restate that I am a SQL noob!!
First attempt:
This query is returning all the data I need and then some! There are two fields I can use to try to filter on InventoryDate and RefCodeID. As you will see in the next example I've tried without success to get the info I need.
SELECT B.BlockName AS Block,
I.Lot,
R.RefCodeName AS LotType,
I.SaleableFrontFootage As Frontage,
ISNULL(IA.Address + ', ','') + IA.CityName + ' ' + IA.ProvinceCode + ' ' + ISNULL(IA.PostalCode,'') AS Address,
AI.HouseStyle AS Style,
RC.RefCodeName AS Status,
PUR.PurchaserName as Builder
FROM Inventory AS I
JOIN Block AS B
INNER JOIN Phase AS P
INNER JOIN ProjectSub AS PS
INNER JOIN Project AS PJ
ON PS.ProjectID = PJ.ProjectID
ON P.ProjectSubID = PS.ProjectSubID
INNER JOIN PhaseSetup AS PSetup
ON P.PhaseID = PSetup.PhaseID
ON B.PhaseID = P.PhaseID
ON I.BlockID = B.BlockID
LEFT JOIN InventoryDate AS IStatus
ON I.InventoryID = IStatus.InventoryID
AND IStatus.RefCodeID IN (67, 71, 73) -- Open, Spec, Sale
LEFT JOIN RefCode AS R
ON I.LotTypeRefCodeID = R.RefCodeID
LEFT JOIN dbo.BuilderSaleByInventory(NULL) AS BSale
ON I.InventoryID = BSale.InventoryID
LEFT JOIN dbo.InventoryAddressByInventoryID(NULL) IA
ON I.InventoryID = IA.InventoryID
LEFT JOIN dbo.PurchaserByInventory(NULL) AS PUR
ON I.InventoryID = PUR.InventoryID
LEFT JOIN ArchitectureInformation as AI
ON I.InventoryID = AI.InventoryID
LEFT JOIN RefCode AS RC
ON IStatus.RefCodeID = RC.RefCodeID
WHERE PJ.ProjectName = 'Copperfield'
AND P.PhaseID IN (114, 119, 120)
AND Pur.PurchaserName NOT LIKE '%HRC%'
Second attempt:
I've tried adding a select statement in the select statement but it's not working for me, if you have any questions please ask, I'm not sure what Info you will need to help me solve this puzzle.
SELECT P.PhaseName,
B.BlockName AS Block,
I.Lot,
R.RefCodeName AS LotType,
I.SaleableFrontFootage As Frontage,
ISNULL(IA.Address + ', ','') + IA.CityName + ' ' + IA.ProvinceCode + ' ' + ISNULL(IA.PostalCode,'') AS Address,
AI.HouseStyle AS Style,
-- RC.RefCodeName AS Status,
(SELECT RC.RefCodeName AS Status
FROM InventoryDate AS IDate
JOIN RefCode AS RC
ON IDate.RefCodeID = RC.RefCodeID
WHERE IDate.InventoryID >= BSale.InventoryDate
AND IDate.InventoryDate = (SELECT MAX(InventoryDate)
FROM InventoryDate AS IDate2
WHERE IDate.InventoryID = IDate2.InventoryID)) AS 'Status',
PUR.PurchaserName as Builder
FROM Inventory AS I
JOIN Block AS B
INNER JOIN Phase AS P
INNER JOIN ProjectSub AS PS
INNER JOIN Project AS PJ
ON PS.ProjectID = PJ.ProjectID
ON P.ProjectSubID = PS.ProjectSubID
INNER JOIN PhaseSetup AS PSetup
ON P.PhaseID = PSetup.PhaseID
ON B.PhaseID = P.PhaseID
ON I.BlockID = B.BlockID
LEFT JOIN RefCode AS R
ON I.LotTypeRefCodeID = R.RefCodeID
LEFT JOIN dbo.InventoryAddressByInventoryID(NULL) IA
ON I.InventoryID = IA.InventoryID
LEFT JOIN dbo.PurchaserByInventory(NULL) AS PUR
ON I.InventoryID = PUR.InventoryID
LEFT JOIN ArchitectureInformation as AI
ON I.InventoryID = AI.InventoryID
LEFT JOIN InventoryDate AS BSale
ON I.InventoryID = BSale.InventoryID
AND BSale.RefCodeID IN (70, 71, 73)
WHERE PJ.ProjectName = 'Copperfield'
AND P.PhaseID IN (114, 119, 120)
AND Pur.PurchaserName NOT LIKE '%HRC%'
Thanks in advance to anyone that can help.
Wade
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply