I have the following tables
ITEM1
ID | NAME | GEARS | ITEM2_ID |
-------------------------------
1 | Test | 56 | 4 |
2 | Test2| 12 | 2 |
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.
Thank you.
The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in the right table; the join will still return a row in the result, but with NULL in each column from the right table.
You can use ISNULL(MAX(T2. LOGIN_TIME), 'Default Value') to replace the NULL . But first you should consider that LOGIN_TIME varchar(255) with values such as 17.07.
The result of a join of null with any other value is null. Because null values represent unknown or inapplicable values, Transact-SQL has no basis to match one unknown value to another. You can detect the presence of null values in a column from one of the tables being joined only by using an outer join.
The LEFT JOIN command returns all rows from the left table, and the matching rows from the right table. The result is NULL from the right side, if there is no match.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With