I need help with the following query, i will be so thankful if anyone can help me please.

I have two tables in database:

Table1:

Column1			Column2			   Status
smith			john				1
jack			smith				0
julia			 rob				1

Table 2:

Column1			Column2
thomas			  lewis
scott			  smith
john                     evans
lopez                    john

Can you please help me building following query:
--------------------------
1. I want to select all rows from Table 1 WHERE there is smith in column1 OR column2 AND status 1.

2. IF there is smith in column 1, then take the value from column 2 (on same row), and if there is smith in column2, then select value in column 1 of the row.
(For example, from above Table1, it should select first row and then as smith is in column1, we take the column2 value, which is john;

3. Then select those rows from Table 2 WHICH contains that value(john) in column1 or column2 of table 2, (which we got by selecting from Table 1.) So in above table 2, it should select last 2 rows.
----------------------------

SELECT * tablename
WHERE column1 = 'smith' OR column2 = 'smith' AND status = '1'

i think can solve the first two problems

Hey,
I think you cannot acheive through a single query.You need to write php codes.
1. First Query =

SELECT col1,col2 FROM `table1` WHERE (`col1` = "smith" or `col2` = "smith" ) and `status` ="1"

Now check which col got the value
2.

if($res['col1'] == "smith")
$key = $res['col2']
if($res['col2'] == "smith")
$key = $res['col1']

3. Second Query =

SELECT * FROM `table2` WHERE (`col1` = "$key" or `col2` = "$key" )

This gives you the logic. Now arrange the codes with proper loop statements and all.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.