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

I have two tables in database:


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.

8 Years
Discussion Span
Last Post by Manuz
SELECT * tablename
WHERE column1 = 'smith' OR column2 = 'smith' AND status = '1'

i think can solve the first two problems


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

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.

Edited by Manuz: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.