How to clear Sql data?
this is a question about the next question previously asked .
I have the following data in one db table.
Name LeftId RightId
------------------------------------------
Cat 1
Cat 1
Dog 2
Dog 2
Dog 3
Dog 3
Gerbil 4 5
Cat
Bird
Cow 6
Cow
Cow 7
Dog 8 9
Note that some lines do not contain data for LeftId and RightId.
Now I want to find two different queries
- All rows that have at least one ID in one of the two columns AND a row with NO data in both of these two Id columns.
eg.
Cat 1
Cow 6 (or 7 .. i'm not worried)
- All strings where LeftId and RightId are NULL are grouped under the same name. If another string (with the same name) has a value in LeftId or RightId, that name will not be returned.
eg.
Bird
hmm ..
EDIT: Carry over the first question correctly.
a source to share
For your first query, you need rows that meet both of the following criteria:
-
Name
in a row is displayed in a table on the same row in whichLeftId
andRightId
are NULL. -
Name
in a row is displayed in a table on the same row where at least one ofLeftId
and isRightId
not NULL.
Well, # 1 is executed:
SELECT Name FROM Tbl WHERE (LeftId IS NULL) AND (RightId IS NULL)
And # 2 is executed:
SELECT Name FROM Tbl WHERE (LeftId IS NOT NULL) OR (RightId IS NOT NULL)
You can cross them to see which one is Name
displayed in both lists:
SELECT Name FROM Tbl WHERE (LeftId IS NULL) AND (RightId IS NULL)
INTERSECT
SELECT Name FROM Tbl WHERE (LeftId IS NOT NULL) OR (RightId IS NOT NULL)
Which returns:
Name
----
Cat
Cow
But you want LeftId
and RightId
and you don't care what I think we will aggregate on Name:
SELECT Name, MIN(LeftId) AS LeftId, MIN(RightId) AS RightId
FROM Tbl WHERE Tbl.Name IN (
SELECT Name FROM Tbl WHERE (LeftId IS NULL) AND (RightId IS NULL)
INTERSECT
SELECT Name FROM Tbl WHERE (LeftId IS NOT NULL) OR (RightId IS NOT NULL)
)
GROUP BY Name
What returns
Name LeftId RightId
---- ------ -------
Cat 1
Cow 6 7
lc already suggested using COALESE to turn these two IDs into one. So how about this:
SELECT Name, COALESCE(MIN(LeftId),MIN(RightId)) AS Id
FROM Tbl WHERE Tbl.Name IN (
SELECT Name FROM Tbl WHERE (LeftId IS NULL) AND (RightId IS NULL)
INTERSECT
SELECT Name FROM Tbl WHERE (LeftId IS NOT NULL) OR (RightId IS NOT NULL)
)
GROUP BY Name
Which returns:
Name Id
---- --
Cat 1
Cow 6
For the second query, you need rows that obey the following criteria:
-
Name
displayed only on lines that do not haveLeftId
andRightId
I can't think of a way to make such a self-referencing query in SQL in one set of criteria, so I split it into two criteria. Both must be observed as acceptable:
-
Name
displayed on lines that do not haveLeftId
andRightId
-
Name
does not appear on lines that have eitherLeftId
orRightId
Execution # 1 is simple:
SELECT Name FROM Tbl WHERE (LeftId IS NULL) AND (RightId IS NULL)
But # 2 is tricky. Of course the opposite of # 2 ("all Name
that appear on lines that have either LeftId
or" RightId
), as before:
SELECT Name FROM Tbl WHERE (LeftId IS NOT NULL) OR (RightId IS NOT NULL)
Now comes the tricky bit - we want all lines to obey # 1, but not obey opposite # 2. Here's where EXCEPT is used :
SELECT Name FROM Tbl WHERE (LeftId IS NULL) AND (RightId IS NULL)
EXCEPT
SELECT Name FROM Tbl WHERE (LeftId IS NOT NULL) OR (RightId IS NOT NULL)
Which returns:
Name
----
Bird
This is what we wanted!
a source to share
If I understand you correctly, this is pretty trivial:
1
SELECT *
FROM your_table
WHERE (LeftId IS NOT NULL
AND RightId IS NULL)
OR
(LeftId IS NULL
AND RightId IS NOT NULL)
2:
SELECT *
FROM your_table
WHERE
NOT EXISTS
(SELECT * FROM your_table y1
WHERE (y1.LeftId IS NOT NULL OR y1.RightId IS NOT NULL)
AND y1.name = your_table.name)
If this is not the case, perhaps you can clarify.
Edit: updated
a source to share
I hope I understand you correctly.
Request 1:
SELECT t1.Name, COALESCE(MIN(t1.LeftID), MIN(t1.RightID))
FROM Table t1
WHERE EXISTS(SELECT t2.Name
FROM Table t2
WHERE t2.Name = t1.Name
AND t2.LeftID IS NULL AND t2.RightID IS NULL)
AND COALESCE(MIN(t1.LeftID), MIN(t1.RightID)) IS NOT NULL
GROUP BY t1.Name
Request 2:
SELECT t1.Name
FROM Table t1
WHERE NOT EXISTS(SELECT t2.Name
FROM Table t2
WHERE t2.Name = t1.Name
AND (t2.LeftID IS NOT NULL OR t2.RightID IS NOT NULL))
a source to share
Request 1:
SELECT [Name], [LeftID], [RightID]
FROM [TestTable]
WHERE -- "All rows which have at least 1 Id in one of the two columns"
([LeftID] IS NOT NULL OR [RightID] IS NOT NULL)
OR
-- "Rows with NO data in both of those two Id columns"
([LeftID] IS NULL AND [RightID] IS NULL)
Request 2:
SELECT [Name], [LeftID], [RightID]
FROM [TestTable]
WHERE -- "All the rows where LeftId and RightId are NULL
-- grouped by the same name"
([LeftID] IS NULL AND [RightID] IS NULL)
AND
-- "If another row (with the same name) has a value
-- in the LeftId or RightId, this name will not be returned"
([Name] NOT IN (SELECT DISTINCT [Name] FROM [TestTable]
WHERE [LeftID] IS NOT NULL
OR
[RightID] IS NOT NULL))
GROUP BY [Name], [LeftID], [RightID]
Results:
Name LeftID RightID
-------------------------------------------------- ----------- -----------
Cat 1 NULL
Cat 1 NULL
Dog 2 NULL
Dog 2 NULL
Dog NULL 3
Dog NULL 3
Gerbil 4 5
Cat NULL NULL
Bird NULL NULL
Cow 6 NULL
Cow NULL NULL
Cow NULL 7
Dog 8 9
(13 row(s) affected)
Name LeftID RightID
-------------------------------------------------- ----------- -----------
Bird NULL NULL
(1 row(s) affected)
a source to share