SELECT @@Microsoft SQL Server Management Studio I'm looking to find in my data all examples of where a value in column B, such as "Apple" or "Cherry", is found in column C, and pull the original line of data for column B. Example data stack below.
Column A | Column B | Column C | Column D |
---|---|---|---|
1 | Apple | ||
2 | Orange | Apple | Return |
3 | Plum | ||
4 | Apple | Apple | Receive |
5 | Banana | ||
6 | Cherry | ||
7 | Peach | ||
8 | Peach | Cherry | Receive |
9 | Banana | ||
0 | Cherry | Cherry | Return |
Returned values expected:
Column A | Column B | Column C | Column D |
---|---|---|---|
1 | Apple | ||
2 | Orange | Apple | Return |
4 | Apple | Apple | Receive |
6 | Cherry | ||
8 | Peach | Cherry | Receive |
0 | Cherry | Cherry | Return |
I am fairly new to SQL and unsure if this is a lack of knowledge or a limitation of SQL data manipulation. I am also learning Python and if this needs to be manipulated in Python instead, I'll give it a shot.
Tried:
SELECT *
WHERE
([Column B] is not null or [Column B] like [Column C])
FROM [DF]
Attempted code 2:
SELECT concat([Column B],'-',[Column C])
FROM [DF]
where Column B like concat([Column B],'-',[Column C])
DECLARE @Table TABLE (ColumnA INT, ColumnB VARCHAR(10), ColumnC VARCHAR(10), ColumnD VARCHAR(10));
INSERT INTO @Table (ColumnA, ColumnB, ColumnC, ColumnD) VALUES
(1, 'Apple ', NULL, NULL), (2, 'Orange', 'Apple', 'Return'), (3, 'Plum', NULL, NULL), (4, 'Apple', 'Apple', 'Receive'),
(5, 'Banana', NULL, NULL), (6, 'Cherry', NULL, NULL), (7, 'Peach', NULL, NULL), (8, 'Peach', 'Cherry', 'Receive'),
(9, 'Banana', NULL, NULL), (0, 'Cherry', 'Cherry', 'Return');
It sounds like you're looking for a self-join, where you join a table back to itself:
SELECT *
FROM @Table a
INNER JOIN @Table b
ON a.ColumnB = b.ColumnC;
ColumnA | ColumnB | ColumnC | ColumnD | ColumnA | ColumnB | ColumnC | ColumnD |
---|---|---|---|---|---|---|---|
4 | Apple | Apple | Receive | 2 | Orange | Apple | Return |
4 | Apple | Apple | Receive | 4 | Apple | Apple | Receive |
6 | Cherry | 8 | Peach | Cherry | Receive | ||
0 | Cherry | Cherry | Return | 8 | Peach | Cherry | Receive |
6 | Cherry | 0 | Cherry | Cherry | Return | ||
0 | Cherry | Cherry | Return | 0 | Cherry | Cherry | Return |