Tricky query

Transact-SQL
1

Hi, SQL team!

I've got two tables:

Table1

-------------
| Id1 | Id2 |
-------------
| 1 | 3 |
| 2 | 3 |
| 1 | 4 |
| 2 | 5 |
-------------

Table2

-------
| Id1 |
-------
| 3 |
| 4 |
-------

How can I get

Table3

-------------
| Id1 | Id2 |
-------------
| 1 | 3 |
| 1 | 4 |
-------------

From Table1 and Table2?

Any kind of joins can't help me, window functions as well.
I can get anything by sequence of temporary tables, but it's not the case.

Thanks!

2

This group is actually for SQL Server, not MySQL. Someone may be be able to help you with MySQL, but the focus here is SQL Server.

3

@ScottPletcher, sorry it was typo)))

4

You didn't explicitly state your requirement, so I had to infer it from the sample data in the tables.


--set up sample data for easy use by everyone
CREATE TABLE #table1 ( Id1 int NOT NULL, Id2 int NOT NULL );
CREATE TABLE #table2 ( Id1 int NOT NULL );
INSERT INTO #table1 VALUES
    (1, 3),
    (2, 3),
    (1, 4),
    (2, 5);
INSERT INTO #table2 VALUES
    (3),
    (4);
*/

--then the query itself
;WITH cte_1 AS (
    SELECT Id1, Id2,
        COUNT(Id2) OVER(PARTITION BY Id1) AS Id2_Count
    FROM #table1
),
cte_2 AS (
    SELECT t1.*, COUNT(*) OVER(PARTITION BY t1.Id1) AS row_count
    FROM cte_1 t1
    INNER JOIN #table2 t2 ON t1.Id2 = t2.Id1
)
SELECT Id1, Id2
FROM cte_2 
WHERE Id2_Count = row_count
1 Like
5

@ScottPletcher thanks for quick reply with working solution.

You are right the question needs more explanation.

Table1 is a table with FOREIGN KEYs to some information units (Table1.Id1) with appropriate searching tags (Table1.Id2):

Table1
------------------------------------------------------------------------------------
Id1                              | Id2
------------------------------------------------------------------------------------
Foreign key to some information1 | Foreign key to search tag3
Foreign key to some information2 | Foreign key to search tag3
Foreign key to some information1 | Foreign key to search tag4
Foreign key to some information2 | Foreign key to search tag5
------------------------------------------------------------------------------------

That is Table1 actually means e.g.

Table1
--------------------------------------------------
Id1               | Id2
--------------------------------------------------
Some information1 | Windows socket
Some information2 | Windows socket
Some information1 | Windows kernel
Some information2 | Windows API
--------------------------------------------------

Table2 is a table with Searching tags for searching some information.

Table2
-----------------------------------
Id1                                     
-----------------------------------
Foreign key to search tag3
Foreign key to search tag4
-----------------------------------

That is Table2 actually means e.g.

Table2
----------------------
Id1                                     
----------------------
Windows socket
Windows kernel
----------------------

Table3 is a result table with FOREIGN KEYs to some information units (Table3.Id1) with the required searching tags (Table3.Id2):

Table3
------------------------------------------------------------------------------------
Id1                              | Id2
------------------------------------------------------------------------------------
Foreign key to some information1 | Foreign key to search tag3
Foreign key to some information1 | Foreign key to search tag4
------------------------------------------------------------------------------------

That is Table3 actually means e.g.

Table3
--------------------------------------------------
Id1               | Id2
--------------------------------------------------
Some information1 | Windows socket
Some information1 | Windows kernel
--------------------------------------------------

That is, "Some information2" does not suit us, because in addition to the required search tag "Windows socket", there is an unnecessary tag "Windows API" and there is absence the second required search tag "Windows kernel".

As per the above solution with COUNT might be somewhat error prone in its use for repeated searches with different tags.

6

My code assumed that you wanted Ids in table1 that had ALL matches in table2. That is, if table1 had Id2s of 5, 7, 9, then table2 would have to include 5, 7, 9 (perhaps with other values, perhaps not). If table2 had only, say, 5 and 9, then it would not be a match.

1 Like