I have the following tables
我有以下几张桌子
ITEM1
ITEM1
ID | NAME | GEARS | ITEM2_ID |
-------------------------------
1 | Test | 56 | 4 |
2 | Test2| 12 | 2 |
ITEM3
ITEM3
ID | NAME | DATA | ITEM2_ID |
-------------------------------
1 | Test | 1 | 1 |
2 | Test7| 22 | 3 |
ITEM2
第二条
ID | VALUE |
--------------------
1 | is simple |
2 | is hard |
3 | is different|
4 | is good |
5 | very good |
And my query
我的查询
SELECT TOP(3) * FROM (
SELECT ID,
rankTable.RANK as RANK_,
TOTALROWS = COUNT(*) OVER()
FROM ITEM2
INNER JOIN
CONTAINSTABLE(ITEM2, [VALUE], 'ISABOUT("good")') as rankTable
ON ITEM2.ID = rankTable.[KEY]
) as ITEM2table
LEFT JOIN (
SELECT ID,
NAME,
GEARS,
ITEM2_ID
FROM ITEM1
) as ITEM1table
ON ITEM1table.ITEM2_ID = ITEM2table.ID
LEFT JOIN (
SELECT ID,
NAME,
DATA,
ITEM2_ID
FROM ITEM3
) as ITEM3table
ON ITEM3table.ITEM2_ID = ITEM2table.ID
and the results
结果
How to remove (if is possible) the first row (ID = 5) using the above SQL query ? Also I want to show TOTALROWS = 1 because other row contains NULL's except first 3 columns.
如何使用上面的SQL查询删除(如果可能)第一行(ID = 5) ?我还想显示TOTALROWS = 1，因为其他行包含NULL的值，除了前3列。
Thank you.
谢谢你！
3 个解决方案
#1
3
If I understand correctly, you want to keep only the rows where either the first or the second (or both) outer join succeeds:
如果我理解正确，您希望只保留第一个或第二个(或两个)外部连接成功的行:
WHERE ITEM1table.ITEM2_ID IS NOT NULL
OR ITEM3table.ITEM2_ID IS NOT NULL
Some simplifications can be done on the query. No need for the nested subqueries:
可以在查询中进行一些简化。不需要嵌套子查询:
SELECT TOP(3)
ITEM2table.ID,
rankTable.RANK as RANK_,
TOTALROWS = COUNT(*) OVER(),
ITEM1table.*,
ITEM3table.*
FROM
ITEM2
INNER JOIN
CONTAINSTABLE(ITEM2, [VALUE], 'ISABOUT("good")') as rankTable
ON ITEM2.ID = rankTable.[KEY]
LEFT JOIN
ITEM1 as ITEM1table
ON ITEM1table.ITEM2_ID = ITEM2.ID
LEFT JOIN
ITEM3 as ITEM3table
ON ITEM3table.ITEM2_ID = ITEM2.ID
WHERE ITEM1table.ITEM2_ID IS NOT NULL
OR ITEM3table.ITEM2_ID IS NOT NULL
ORDER BY something --- you need to order by something
--- if you use TOP. Unless you want
--- 3 (random) rows.
#2
3
Maybe there's an obvious reason, but if you want to eliminate rows where the second table doesn't have a match, why are you using a left join? It seems like your first join should be an inner join and your second should be left - that would give you the results you want in this case.
可能有一个明显的原因，但是如果您想消除第二个表没有匹配项的行，为什么要使用左连接呢?看起来你的第一个连接应该是一个内部连接，而你的第二个连接应该被保留——在这种情况下，这会得到你想要的结果。
#3
0
You can either use INNER JOIN instead of LEFT JOIN, or put
可以使用内连接代替左连接，也可以使用put
WHERE ITEM1table.ID IS NOT NULL AND ITEM3table.ID IS NOT NULL
at the end of your query
在查询的末尾
#1
3
If I understand correctly, you want to keep only the rows where either the first or the second (or both) outer join succeeds:
如果我理解正确，您希望只保留第一个或第二个(或两个)外部连接成功的行:
WHERE ITEM1table.ITEM2_ID IS NOT NULL
OR ITEM3table.ITEM2_ID IS NOT NULL
Some simplifications can be done on the query. No need for the nested subqueries:
可以在查询中进行一些简化。不需要嵌套子查询:
SELECT TOP(3)
ITEM2table.ID,
rankTable.RANK as RANK_,
TOTALROWS = COUNT(*) OVER(),
ITEM1table.*,
ITEM3table.*
FROM
ITEM2
INNER JOIN
CONTAINSTABLE(ITEM2, [VALUE], 'ISABOUT("good")') as rankTable
ON ITEM2.ID = rankTable.[KEY]
LEFT JOIN
ITEM1 as ITEM1table
ON ITEM1table.ITEM2_ID = ITEM2.ID
LEFT JOIN
ITEM3 as ITEM3table
ON ITEM3table.ITEM2_ID = ITEM2.ID
WHERE ITEM1table.ITEM2_ID IS NOT NULL
OR ITEM3table.ITEM2_ID IS NOT NULL
ORDER BY something --- you need to order by something
--- if you use TOP. Unless you want
--- 3 (random) rows.
#2
3
Maybe there's an obvious reason, but if you want to eliminate rows where the second table doesn't have a match, why are you using a left join? It seems like your first join should be an inner join and your second should be left - that would give you the results you want in this case.
可能有一个明显的原因，但是如果您想消除第二个表没有匹配项的行，为什么要使用左连接呢?看起来你的第一个连接应该是一个内部连接，而你的第二个连接应该被保留——在这种情况下，这会得到你想要的结果。
#3
0
You can either use INNER JOIN instead of LEFT JOIN, or put
可以使用内连接代替左连接，也可以使用put
WHERE ITEM1table.ID IS NOT NULL AND ITEM3table.ID IS NOT NULL
at the end of your query
在查询的末尾