SQL查询JOIN具有不同值的表

I have come across a situation where i need to join two table based on columns have different values.

我遇到过一种情况,我需要根据具有不同值的列加入两个表。

E.g.

Table1 has column T1

表1有列T1

Table2 has column T2

表2列T2
T1 has 100 rows with value P

T1有100行,值为P.

and

T2 has 50 rows with value N and 50 rows with value P

T2有50行,值为N,50行,值为P.

I want to join table Table1 with value P with Table2 that has values N as well as P

我想将表Table1与值P连接,Table2具有值N和P

It should give me total 100 records.

它应该给我总共100条记录。

3 个解决方案

#1

Try this:

SELECT 
  t1.t1, 
  t2.t2
FROM Table1 t1
INNER JOIN Table2 t2 ON t2.t2 IN('N', 'P')
WHERE T1.t1 = 'p';

The predicate IN ('N', 'P') will get the values of t2 from the table2 that have the values N and P.

谓词IN('N','P')将从table2获得具有值N和P的t2的值。
The WHERE clause will get the values of t1 form the tabale1 where the the value is P, you can move this predicate to the JOIN condition.

WHERE子句将从tabale1获取t1的值,其中值为P,您可以将此谓词移动到JOIN条件。

#2

A cross join repeats every row in the right table for every row in the left table. You can then specify any "unrelated" conditions in the where clause:

对于左表中的每一行,交叉连接重复右表中的每一行。然后,您可以在where子句中指定任何“无关”条件:

select  *
from    Table1 t1
cross join
        Table2 t2
where   t1.col1 = 'N'
        and t2.col1 in ('N', 'P')

#3

In your scenario any join will act as a cross join since you have many duplicates in both tables (Table1 with 100 rows of 'P' and Table2 with 50 rows of 'P'),

在您的场景中,任何连接都将充当交叉连接,因为在两个表中都有许多重复项(Table1有100行'P',Table2有50行'P'),

select  t1.*,t2.*
from    Table1 t1
join    Table2 t2
where   t1.field1 = 'N'
        and t2.field1 IN ('N', 'P')

And your requirement to get only 100 rows is not possible. Because, first row of 'P' in table1 will get be joined with 50 rows of table2 with 'P' and so you will get 50 rows in output for each row of table1. And if you really want 100 rows then put a LIMIT OR TOP as a filter condition.

并且您无法获得仅100行的要求。因为table1中的第一行'P'将与50行table2与'P'连接,因此table1的每一行输出将获得50行。如果你真的想要100行,那么将LIMIT或TOP作为过滤条件。

Hope this helps you!!!

希望这对你有所帮助!!!

#1