Need syntax help with a JOIN inside of a Subquery
- I am designing a query that outputs JSON using FOR JSON PATH and I'm having difficulty including a JOIN for the Lookup_IndustrySectors table.
My database looks like this:
Here is what I have so far (below). The subqueries work fine. The commented code won't run.Please help!
==== BEGIN ====
USE MyDB;
SELECT
COMPANYNAME,(SELECT Capability
FROM Capabilities AS Capabilities
WHERE ConnectoryStore.ConnectoryID = Capabilities.ConnectoryID
FOR JSON PATH) AS Capabilities,(SELECT [NAME], CITY
FROM Branches AS Branches
WHERE ConnectoryStore.ConnectoryID = Branches.ConnectoryID
FOR JSON PATH) AS Branches,(SELECT MAKE, MODEL, QUANTITY, SPECS
FROM Equipment AS Equipment
WHERE ConnectoryStore.ConnectoryID = Equipment.ConnectoryID
FOR JSON PATH) AS Equipment,(SELECT AREA, DESCRIPTION
FROM Facility AS Facility
WHERE ConnectoryStore.ConnectoryID = Facility.ConnectoryID
FOR JSON PATH) AS Facility,--(SELECT IndustrySectors.ConnectoryID, Lookup_IndustrySectors.IndustrySector
--FROM IndustrySectors AS IndustrySectors
-- INNER JOIN Lookup_IndustrySectors AS Lookup_IndustrySectors
-- ON IndustrySectors.industrysectorid = Lookup_IndustrySectors.IndustrySectorID
--WHERE IndustrySectors.ConnectoryID = 3918) AS 'Industry_Sectors',
--SELECT IndustrySector
-- FROM Lookup_IndustrySectors
-- WHERE industrysectorid IN (
-- SELECT industrysectorid
-- FROM IndustrySectors
-- --WHERE ConnectoryID = 3918
-- ) FOR JSON PATH,FROM ConnectoryStore AS ConnectoryStore
WHERE ConnectoryStore.ConnectoryID = 3918
FOR JSON PATH;Msg 156, Level 15, State 1, Line 44
Incorrect syntax near the keyword 'FROM'
.
==== END ==== -
The Industry_Sectors subquery is just missing the FOR JSON PATH. I'm not sure what you're trying to accomplish with the second but it isn't framed as a subquery.
-
You know there no need to alias an object as itself right? For example: ConnectoryStore AS ConnectoryStore. Normally Aliases are to make your code, and object's name, more succinct. So, for example you might use some of the following:
ConnectoryStore AS CS
Equipment AS EAnd, although it's in the comments:
Lookup_IndustrySectors AS LISThom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
@ Thom A - The alias after FROM (ConnectoryStore AS ConnectoryStore) is an artifact from throwing "hail mary" passes to the end-zone (American Football term) to try and get rid of the
Msg 156, Level 15, State 1, Line 44
Incorrect syntax near the keyword 'FROM'
error... I should have removed it from the code I pasted... -
would be nice to have DDL so we could help
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
You have a comma after Facility, that is what is casing the error.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret. -
@ andycadley - "The Industry_Sectors subquery is just missing the FOR JSON PATH." - .
(SELECT IndustrySectors.ConnectoryID, Lookup_IndustrySectors.IndustrySector
FROM IndustrySectors AS IndustrySectors
INNER JOIN Lookup_IndustrySectors AS Lookup_IndustrySectors
ON IndustrySectors.industrysectorid = Lookup_IndustrySectors.IndustrySectorID
WHERE IndustrySectors.ConnectoryID = 3918) FOR JSON PATHMsg 156, Level 15, State 1, Line 44
Incorrect syntax near the keyword 'FROM'. - rob.vig - Monday, October 1, 2018 7:27 PMI am designing a query that outputs JSON using FOR JSON PATH and I'm having difficulty including a JOIN for the Lookup_IndustrySectors table.
My database looks like this:
Here is what I have so far (below). The subqueries work fine. The commented code won't run.Please help!
==== BEGIN ====
USE MyDB;
SELECT
COMPANYNAME,(SELECT Capability
FROM Capabilities AS Capabilities
WHERE ConnectoryStore.ConnectoryID = Capabilities.ConnectoryID
FOR JSON PATH) AS Capabilities,(SELECT [NAME], CITY
FROM Branches AS Branches
WHERE ConnectoryStore.ConnectoryID = Branches.ConnectoryID
FOR JSON PATH) AS Branches,(SELECT MAKE, MODEL, QUANTITY, SPECS
FROM Equipment AS Equipment
WHERE ConnectoryStore.ConnectoryID = Equipment.ConnectoryID
FOR JSON PATH) AS Equipment,(SELECT AREA, DESCRIPTION
FROM Facility AS Facility
WHERE ConnectoryStore.ConnectoryID = Facility.ConnectoryID
FOR JSON PATH) AS Facility,--(SELECT IndustrySectors.ConnectoryID, Lookup_IndustrySectors.IndustrySector
--FROM IndustrySectors AS IndustrySectors
-- INNER JOIN Lookup_IndustrySectors AS Lookup_IndustrySectors
-- ON IndustrySectors.industrysectorid = Lookup_IndustrySectors.IndustrySectorID
--WHERE IndustrySectors.ConnectoryID = 3918) AS 'Industry_Sectors',
--SELECT IndustrySector
-- FROM Lookup_IndustrySectors
-- WHERE industrysectorid IN (
-- SELECT industrysectorid
-- FROM IndustrySectors
-- --WHERE ConnectoryID = 3918
-- ) FOR JSON PATH,FROM ConnectoryStore AS ConnectoryStore
WHERE ConnectoryStore.ConnectoryID = 3918
FOR JSON PATH;Msg 156, Level 15, State 1, Line 44
Incorrect syntax near the keyword 'FROM'
.
==== END ====How about this:
USE MyDB;
GOSELECT
COMPANYNAME,
(
SELECT C.Capability
FROM Capabilities AS C
WHERE C.ConnectoryID = CS.ConnectoryID
FOR JSON PATH
) AS Capabilities,
(
SELECT NAME, CITY
FROM Branches AS B
WHERE B.ConnectoryID = CS.ConnectoryID
FOR JSON PATH
) AS Branches,
(
SELECT MAKE, MODEL, QUANTITY, SPECS
FROM Equipment AS E
WHERE E.ConnectoryID = CS.ConnectoryID
FOR JSON PATH
) AS Equipment,
(
SELECT AREA, [DESCRIPTION]
FROM Facility AS F
WHERE F.ConnectoryID = CS.ConnectoryID
FOR JSON PATH
) AS Facility,
(
SELECT INDS.ConnectoryID, LIS.IndustrySector
FROM IndustrySectors AS INDS
INNER JOIN Lookup_IndustrySectors AS LIS
ON INDS.industrysectorid = LIS.IndustrySectorID
WHERE INDS.ConnectoryID = 3918
FOR JSON PATH
) AS Industry_Sectors
FROM ConnectoryStore AS CS
WHERE CS.ConnectoryID = 3918
FOR JSON PATH;Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
@ sgmunson (Steve) - If I had a daughter, I would want you to marry her! THANK YOU!!! You did it! I really appreciate your help. - Robert V.
Here's the JSON output (which is exactly what I needed) :
[
{
"COMPANYNAME": "Acme, LLC",
"Capabilities": [
{
"Capability": "Acme, LLC provides machining parts for customers with high volume demands. It also provides out sourcing of secondary process (paint, anodize, plating, Chemfilm...)and assembly and shipping of products to distributors.\r\nServices include:\r\n\r\n CNC Machining \r\n Fixtures \r\n Forming \r\n Drilling \r\n Welding \r\n Polishing \r\n Assembly \r\n High speed metal removal \r\n"
}
],
"Branches": [
{
"NAME": "Magness Manufacturing",
"CITY": "Tijuana"
}
],
"Equipment": [
{
"MAKE": "CMM",
"MODEL": "Mitutoyo"
},
{
"MAKE": "HAAS VMC",
"MODEL": "VF0",
"QUANTITY": "6-9"
},
{
"MAKE": "HAAS VMC",
"MODEL": "VF3",
"QUANTITY": "3"
},
{
"MAKE": "Miller",
"MODEL": "250",
"QUANTITY": "1"
},
{
"MAKE": "Neff Press",
"MODEL": "50 Ton",
"QUANTITY": "6-9"
}
],
"Facility": [
{
"AREA": "20,000 - 29,999 sq. ft.",
"DESCRIPTION": "MMI has built a first class Manufacturing facility with a talented group of individuals for purchasing, import/export, accounting, Production Control and a well-trained Engineering staff with dedicated operators."
}
],
"Industry_Sectors": [
{
"ConnectoryID": 3918,
"IndustrySector": "Automotive & Transport Equipment"
},
{
"ConnectoryID": 3918,
"IndustrySector": "Recreation/Sports/Entertainment/Leisure"
},
{
"ConnectoryID": 3918,
"IndustrySector": "Manufacturing"
},
{
"ConnectoryID": 3918,
"IndustrySector": "Construction & Construction Materials"
},
{
"ConnectoryID": 3918,
"IndustrySector": "Defense/Military/National Security"
},
{
"ConnectoryID": 3918,
"IndustrySector": "Electronics & Miscellaneous Technology"
},
{
"ConnectoryID": 3918,
"IndustrySector": "Transportation"
},
{
"ConnectoryID": 3918,
"IndustrySector": "Energy/Utilities"
}
]
}
] - rob.vig - Tuesday, October 2, 2018 8:21 AM
Glad I could help... although you might not want to put me on such a high pedestal... (too much risk of falling... :crazy: )
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply