英文:
Converting multiple JOINS to json using JSON AUTO
问题
以下是已翻译的内容：
我目前有以下的SQL查询。
SELECT quote.id,
lines.id
FROM Quote quote
INNER JOIN Quote_Line as lines
ON quote.id = lines.quote_id
FOR JSON AUTO
如预期，它会正确返回以下的JSON：
[
{
"id": 1,
"lines": [
{
"id": 1
}
]
}
]
现在我正在尝试扩展此输出，添加另一个1对1的JOIN（从Quote到Quote_Customer）。如何编写这个JOIN以获得以下输出：
[
{
"id": 1,
"customer": {
"id": 1
},
"lines": [
{
"id": 1
}
]
}
]
我尝试添加一个简单的`LEFT JOIN Quote_Customer customer ON quote.id = customer.quote_id`，但这会在每个`lines`对象内添加一个嵌套的`customer`对象。
有什么想法？
英文:
I currently have the following SQL query.
SELECT quote.id,
lines.id
FROM Quote quote
INNER JOIN Quote_Line as lines
ON quote.id = lines.quote_id
FOR JSON AUTO
As expected it correctly returns the following JSON:
[
{
"id": 1,
"lines": [
{
"id": 1
}
]
}
]
Now i am trying to extend this output with another 1-to-1 JOIN (Quote to Quote_Customer). How do i write this JOIN to get the following output:
[
{
"id": 1,
"customer": {
"id": 1
},
"lines": [
{
"id": 1
}
]
}
]
I have tried adding a simple LEFT JOIN Quote_Customer customer ON quote.id = customer.quote_id, but this adds a nested customer object inside every lines-object.
Any ideas?
答案1
得分: 1
使用SELECT中的相关子查询是一种方法：
CREATE TABLE dbo.Quote (ID int);
CREATE TABLE dbo.QuoteLine (ID int, QuoteID int);
CREATE TABLE dbo.Customer (ID int, QuoteID int);
GO
INSERT INTO dbo.Quote (ID)
VALUES(1);
INSERT INTO dbo.QuoteLine (ID,QuoteID)
VALUES(1,1),(2,1);
INSERT INTO dbo.Customer (ID,QuoteID)
VALUES(1,1);
GO
SELECT Q.ID,
(SELECT ID
FROM dbo.Customer C
WHERE C.QuoteID = Q.ID
FOR JSON AUTO) AS Customer,
Lines.ID
FROM dbo.Quote Q --将名为quote的表别名为quote没有任何意义
INNER JOIN dbo.QuoteLine AS Lines ON Q.ID = Lines.QuoteID
FOR JSON AUTO;
GO
DROP TABLE dbo.Customer;
DROP TABLE dbo.QuoteLine;
DROP TABLE dbo.Quote;
英文:
One method would be to use a correlated subquery in the SELECT:
CREATE TABLE dbo.Quote (ID int);
CREATE TABLE dbo.QuoteLine (ID int,
QuoteID int);
CREATE TABLE dbo.Customer (ID int,
QuoteID int);
GO
INSERT INTO dbo.Quote (ID)
VALUES(1);
INSERT INTO dbo.QuoteLine (ID,QuoteID)
VALUES(1,1),(2,1);
INSERT INTO dbo.Customer (ID,QuoteID)
VALUES(1,1);
GO
SELECT Q.ID,
(SELECT ID
FROM dbo.Customer C
WHERE C.QuoteID = Q.ID
FOR JSON AUTO) AS Customer,
Lines.ID
FROM dbo.Quote Q --Aliasing a table called quote as quote doesn't make any sense
INNER JOIN dbo.QuoteLine AS Lines ON Q.ID = Lines.QuoteID
FOR JSON AUTO;
GO
DROP TABLE dbo.Customer;
DROP TABLE dbo.QuoteLine;
DROP TABLE dbo.Quote;
答案2
得分: 0
尝试运行以下查询：
SELECT quote.quote_id,
(select quote_id from Quote_Customer where quote.quote_id = quote_id For json auto)customer,
lines.quote_id
FROM Quote quote
INNER JOIN Quote_Line as lines
ON quote.quote_id = lines.quote_id
FOR JSON AUTO
你可以获得以下输出：
[
{
"quote_id": 1,
"customer": [
{
"quote_id": 1
}
],
"lines": [
{
"quote_id": 1
}
]
},
{
"quote_id": 2,
"lines": [
{
"quote_id": 2
}
]
}
]
英文:
try to run below query
SELECT quote.quote_id,
(select quote_id from Quote_Customer where quote.quote_id = quote_id For json auto)customer,
lines.quote_id
FROM Quote quote
INNER JOIN Quote_Line as lines
ON quote.quote_id = lines.quote_id
FOR JSON AUTO
you can get below output:
[
{
"quote_id": 1,
"customer": [
{
"quote_id": 1
}
],
"lines": [
{
"quote_id": 1
}
]
},
{
"quote_id": 2,
"lines": [
{
"quote_id": 2
}
]
}
]
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论