根据从另一个表生成的条件从一个表中检索行 [英] Retrieve rows from one table based on conditions generated from another table

问题描述

我的mysql数据库中有两个表...



以下是我的表的示例结构...



表1：



id | elem1 | elem2 | elem3 .....

1 | 10      | 20      | 15

。

。

。



表2：



id | param1 | param2 | param3 | param4 ...

1 |    5         | 8           | 10          | 20

。

。


每个Table2行
，我想检索Table1的行，其中，for例如：

1-elem1¶m1 match

2-elem2¶m2 match

3-elem3介于param3和param4之间



我认为我可以做的一种方法是通过查询循环Table2行来执行查询，并且对于Table2的每一行，检索满足条件的Table1行，由另一行查询...但是任何人都可以在不使用循环的情况下帮助我进行SQL查询。我认为JOIN在这里很有用..但我不知道在这里使用它们esp。条件3.



更新：



对于我的Table2的某些行，它可能是任何一个列param3和param4值为NULL（例如0），例如，我可以有以下情况：



情况1-> param3不是NULL但是param4为NULL：

在这种情况下，我希望我的上述条件3更改为（elem3> param3）。



情况2- > param3为NULL但param4不为NULL：

在这种情况下，我希望我的上述条件3更改为（elem3< param4）。



情况3-> param3和param4都是NULL：

在这种情况下，我根本不想要条件3。



对于某些行，param1和param2也可能是NULL（例如0）。

就像上面一样，在这种情况下，我想忽略这些行的连接等效条件还有。



如果不使用循环这可能吗？



我很感激，如果你能帮助我...提前谢谢。

I have two tables in my mysql database...

Followings are the sample structure of my tables...

Table1:

id | elem1 | elem2 | elem3 .....
1 | 10       | 20       | 15
.
.
.

Table2:

id | param1 | param2 | param3 | param4 ...
1 |    5          | 8            | 10           | 20
.
.

for each Table2 Row, I want to retrieve Table1 Rows in which,for example :
1-elem1 & param1 match
2-elem2 & param2 match
3-elem3 is between param3 and param4

One way I think I can do is to do the query by looping through Table2 rows by a query,and for each row of Table2,retrieve Table1 rows which meet the conditions, by another query...But can anyone help me with a sql query without using loops. I think "JOIN"s would be useful here..But I don't know to use them here esp. for condition 3.

Update:

For some rows of my Table2, it's possible that any of the columns param3 & param4 values are NULL(or 0 for example) and for example,I can have following situations:

situation 1->param3 is Not NULL BUT param4 is NULL:
In this case I want my above condition 3 to change to(elem3 > param3).

situation 2->param3 is NULL but param4 is not NULL:
In this case I want my above condition 3 to change to(elem3 < param4).

situation 3->both param3 & param4 are NULL:
In this case I don't want to have condition 3 at all.

It's also possible for some rows that param1 and param2 are NULL(or 0 for example).
just like above, in such cases, I want to ignore join equivalent conditions for these rows as well.

Is this possible without using loops?

I appreciate if you could help me with this...Thanks in advance.

推荐答案

你的脚本看起来像这样，以防param3 ou param4为空



希望有帮助

Bechir。

Hi,

Your script will look like this in case param3 ou param4 are null

Hope that help
Bechir.

SELECT TABLE1.*, TABLE2.*
FROM TABLE1
INNER JOIN TABLE2 ON TABLE1.ELEM1 = TABLE2.PARAM1
   AND TABLE1.ELEM2 = TABLE2.PARAM2
WHERE TABLE1.ELEM3 BETWEEN ISNULL(TABLE2.PARAM3, TABLE1.ELEM3) AND ISNULL(TABLE2.PARAM4, TABLE1.ELEM3)

hi，



使用'where'或'inner join'是一样的想法。

哟你的情况：

Using 'where' or 'inner join' is the same thinks.
in your case :

SELECT table1.*, table2.*
from table1 
inner join table2 on table1.elem1 = table2.param1
   and table1.elem2 = table2.param2
where table1.elem3 between table2.param3 and table2.param4

希望有所帮助，

Bechir。

Hope that help,
Bechir.

这篇关于根据从另一个表生成的条件从一个表中检索行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！