Retrieve rows from one table based on conditions generated from another table

Question

0.00/5 (No votes)

See more:

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.

Posted 30-Dec-14 11:16am

Member 11033015

Updated 2-Jan-15 0:07am

v4

Add a Solution

Comments

Sumit Jawale 31-Dec-14 3:49am

Can you please elaborate your exact need ?? Its not clear to understand what exactly you want.

Member 11033015 31-Dec-14 14:59pm

I've edited my question a little further...Can you help me with the updated question? Hope it's clear enough now...Thanks in advance.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

BechirBenltaief · Accepted Answer · 2015-01-01T06:54:00

Solution 2

Hi,

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

Hope that help
Bechir.

SQL

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)

Posted 1-Jan-15 6:54am

BechirBenltaief

Comments

Member 11033015 1-Jan-15 16:44pm

Thanks for your answer Bechir. That's exactly the solution I was looking for.besides, after searching about ISNULL(),I think I'd better use IFNULL() here since I'm working on mysql database.They have the same functionality,Don't they? Thanks again.

Sumit Jawale 2-Jan-15 1:28am

Correct solution.

BechirBenltaief · Accepted Answer · 2014-12-30T23:19:00

Solution 1

hi,

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

Hope that help,
Bechir.

Posted 30-Dec-14 23:19pm

BechirBenltaief

Comments

Member 11033015 31-Dec-14 14:57pm

Thanks for your answer Bechir.Now, I've edited my question a little further...Can you help me with the updated question? Thanks in advance.