Question 113166 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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
Tags:

4

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
  3. 0

    Try

    SELECT T1.NumberPK, T1.WorkPK
FROM TABLE1 T1
INNER JOIN TABLE2 T2 ON T1.NumberPK = T2.NumberPK
WHERE T1.WordPK = T2.WordPK
    Login or Signup to reply.
  4. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search