SQL: How can I pick a cell value from one table as a condition to select another table

Hi I’m a new learner of SQL. How can I realize this process in SQL or perhaps with python if needed:

First, from table1, I randomly selected two results:

SELECT TOP 2 id, date 
FROM table 1
WHERE date >= 2 AND date <= 6
ORDER BY RAND(CHECKSUM(*) * RAND())

+-----------+                      
|  table1   |                      
+-----------+                      
| id | date | 
+----+------+
| x  |  3   | 
| y  |  4   |  
+----+------+

I need to use the value x and y as conditions to display another table. For instance, using x, I can:

SELECT id, date 
FROM table1
WHERE date >= 2 AND date <= 6 AND id = 'x'
ORDER BY date ASC

+-----------+                      
|  table2   |                      
+-----------+                      
| id | date | 
+----+------+
| x  |  3   |
| x  |  4   |  
| x  |  5   |  
| x  |  6   |  
| x  |  6   |  
+----+------+

What I need is to get the length of table2 without duplication on date. For instance, table2 has 5 rows, but last two duplicate in date. So the final answer is 4 rows.

For id = y, I have to do the same thing (say table3) and compare the length of table3 and table2 to see if consistent.

If yes, then return the length (say, 4 rows); If no, then go back to table1 and select another two id (say, z and y).

I was thinking to use python to select value or create variables, then use python variables in SQL. But it is too much for a new learner. I really appreciate it if someone could help me out this process.

Answer

You can use subqueries with IN clause

Here is too a Version with two diemsnions, maybe this will help also

CREATE TABLE table1 ([id] varchar(2),[date] int)
GO

✓

    SELECT id, date FROM table1

    where date >= 2 and date <= 6

    and id IN (

    SELECT TOP 2 id FROM table1

    WHERE date >= 2 and date <= 6

    ORDER BY RAND(CHECKSUM(*) * RAND())
)

    ORDER BY date ASC

GO

id | date
:- | ---:

SELECT id, date FROM table1
WHERE EXISTS (SELECT 1
              FROM (

    SELECT TOP 2 id,[date] FROM table1

    WHERE date >= 2 and date <= 6

    ORDER BY RAND(CHECKSUM(*) * RAND())) AS table2
              WHERE  table1.[id] = table2.[id]
                        AND table1.[date] = table2.[date])
GO

id | date
:- | ---:

db<>fiddle here