Select row in which one element related with an id appears in other table where the id is the same-CodePudding

I have the following tables:

Table 1:

Number PK	Word PK
1	A
1	B
1	C
2	B
2	A
2	C
3	J
3	K
3	B

Table 2:

Number2	Word2
1	A
2	X
3	B

I want to select the rows of the second table in which the word (Word2) is related with the rows of table 1 in which (Number Pk) is the same as (Number 2). For example, in the first row of the second table we can see Number2=1 and Word2=A, so I would want the program to search in the first table where Number Pk=1 and see if it is related with A, so it is related with A, B and C. So it meets the condition that it is related with A, so that row of the second column would be a solution.

Output:

Number	Word
1	A
3	B

CodePudding user response：

Try

SELECT T1.NumberPK, T1.WorkPK
FROM TABLE1 T1
INNER JOIN TABLE2 T2 ON T1.NumberPK = T2.NumberPK
WHERE T1.WordPK = T2.WordPK

CodePudding user response：

I haven't tested this but this logically or similar should work.

SELECT * FROM table2
WHERE table2.word2 IN (SELECT WordPK FROM table1 WHERE table1.numberPK=table2.Number2)

CodePudding user response：

I don't know your exact field and table names but presumably something along the lines of this would do what you are looking for:

SELECT number, word FROM table1
INTERSECT
SELECT number, word FROM table2

CodePudding user response：

This should work:

SELECT `Table 1`.`Number PK`, MIN(`Table 2`.`Word PK`) AS Word
FROM `Table 1` JOIN `Table 2` ON `Table 1`.`Number PK` = `Table 2`.`Number2`
GROUP BY `Number PK`;

If you have other columns in Table 2 you wish to include:

SELECT t.`Number PK`, t.`Word`, ...others...
FROM (
  SELECT `Table 1`.`Number PK`, `Table 1`.`Word PK` AS Word, ...others...
    ROW_NUMBER() OVER (PARTITION BY `Table 1`.`Number PK` ORDER BY `Table ``.`Word PK`) AS rownum
  FROM `Table 1` JOIN `Table 2` ON `Table 1`.`Number PK` = `Table 2`.`Number2`
) AS t
WHERE t.rownum = 1;