Have an account? Sign in
Login  Register  Facebook
Web Development Problems
Selecting data from second table based on data from 1st table
Hey. Thank you very much for your help with my previous question. This website has been very helpful to me.

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

I have two tables in database:

Table1:
ID	Column1			Column2		     Status
1	smith			john			1
2	jack			smith			0
3	julia			rob			    1
4	mark			smith               1
Table 2: 
ID	Column1			Column2
1	thomas			  lewis
2	scott			  smith
3	john                  evans
4	lopez                 john


Can you please help me building following query:
--------------------------

1. FIRST 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 and last row. In first row, as smith is in column1, we need to get column2 value, which is john. and in last row as smith is in column2, we need to get value of column which is in column1 ie. mark. (we dont select row 2 because there status is not 1).

3. Now we want to select those rows from Table 2 WHICH contains those values(john or mark) 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.



Thanks.
Started: September 18, 2011 Latest Activity: September 18, 2011 mysql select
4 Answers
This should cover #1/2, you've lost me with #3
select if(col1 = 'smith', col2, col1) from table1 
where (col1 = 'smith' or col2 = 'smith') and status = 1

Posted: xtremex
In: September 18, 2011

with Ben\'s query
select * from table2
where column1 in (select if(column1 = \'smith\', column2, column1) from table1 where (column1 = \'smith\' or column2 = \'smith\') and status = 1)
OR
column2 in (select if(column1 = \'smith\', column2, column1) from table1 where (column1 = \'smith\' or column2 = \'smith\') and status = 1)
OR
select * from table2 where
column1 in (select column1 from table1 where column2 = \'Smith\' AND status = 1) OR 
column1 in (select column2 from table1 where column1 = \'Smith\' AND status = 1) OR 
column2 in (select column1 from table1 where column2 = \'Smith\' AND status = 1) OR 
column2 in (select column2 from table1 where column1 = \'Smith\' AND status = 1)

Posted: is_set
In: September 18, 2011

try
select * 
  from  table1 as t1, table2 as t2
 where t1.status = 1
   and (t1.col1 = 'smith' and (t2.col1 = t1.col2 or t2.col2 = t1.col2)
       or t1.col2 = 'smith' and (t2.col1 = t1.col1 or t2.col2 = t1.col1))

Posted: Go
In: September 18, 2011

Probably the best sollution would be to redesign your database, but if you really want to keep your tables, you can try this query:
SELECT IF(t1.col1 = 'smith', t1.col2, t1.col1) AS t1col2,  IF(t2.col1 = t1col2, t2.col2, t2.col1) AS t2col2
FROM table1 AS t1
JOIN table2 AS t2 ON( IF(t1.col1 = 'smith', t1.col2, t1.col1) IN ( t2.col1, t2.col2 ) )
WHERE (t1.col1 = 'smith' OR t1.col2 = 'smith') AND t1.status = 1

Posted: MacOS
In: September 18, 2011

Your Answer

xDo you want to answer this question? Please login or create an account to post your answer