[kaash1]

The select query given below selects either column1 OR column2 from Table2 depending on which column fulfills the condition.
Now how can i know with that it it was column1 OR column2 ?(i want to know if it was column1 or column2 of table2)

PHP Code:

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) 

I will appreciate any help.
Thanks

[edriven]

Looks like you go overly creative or I am missing what you're trying to do.

I would use a union query. Not sure why you use a sub-select if the lastname is always smith. Kinda weird.



Select 'COLUMN1' as SourceColumn , * from table1 t
where column1 ='smith'
union
Select 'COLUMN2' as SourceColumn , * from table1 t
where column2 ='smith'

[kaash1]

Thanks for the answer. I try to explain what I'm trying to do.

I have 2 tables, table1 and table2.
I have a specific user, lets say 'smith'. it can be IN either column1 or column2 of TABLE1.
I want to select all those rows from Table1 which contains smith in Column1 or COlumn2 or Table1.
Now, IF it was in column1 THEN I want to get the value of column2 in that row. If it was in column2 then i want to get the value of column1 in that row.
Once I have got the value, I want to select all those rows in Table2 which have that value in either Column1 or Column2 of Table2.

Thank you very much for your help again.

[UnrealEd]

It looks like you might have to redesign your database a little bit so you only have to check just one field for a value instead of two.

[kaash1]

Thanks for your reply.. Can you please give me some hint what I should change in database. Currently table1 has ID, colulm1, column2, status and table2 has 3 fields, ID, column1 and column2. Many thanks.

[UnrealEd]

what's the content of the column1 and coulmn2 field. Since you're using these two fields for table-cross-reerencing, I suggest you optimize the tables for those two columns. If you provide me with some little more details about those values, I might give you some hints

[kaash1]

Thanks again for your reply;
As i said, Table1 is very simple, it has 3 columns.
ID Column1 Column2 Status.
1 alex smith 1

Here I check if there is a specific user is connected with other user. That specific user can be in column1 or Column2. Simple, right?
Now, my concerned person is the one who is connected with the specific user(alex). So, i select the rows where there is the alex, then i select the opposite column in that row. (if its in first column, select second column, otherwise select 1st; in the above example we select 'smith').

Now There is Table2. Which keeps the activity data of the user what we got(smith). For example we got the user 'smith' from previous table.

And this table has the activity that smith and john hate each other.


ID user1 user2 action
1 john smith hate

With the above table design, I can get the activity of 2 users john, and smith in 1 row.

As you said i should redesign database so that i have to check only one column in Table2. The only thing I can think of is , i add record for each user on each row, for example,
in Table 2.

1 john smith hate
2 smith john hate

because 2 users had this activity so i added 2 rows, instead of 1.
I just tried to avoid it as i thought it might add lots of extra rows in database or Is that method ok ?

is there any better way to do it?

[UnrealEd]

It doesn't seem to be that obvious any longer how to improve the tables

The double rows aren't that much of a problem. The amount of rows is not that big of a deal unless you end up in millions and millions of rows. To me this is the best solution, because now you have a strong relation between the user, and the other users he/she hates. On the other hand, this is usually done when there's a one way relation, and not both ways (like your case, where they hate each other).

Another option is to CONCAT (this is a mysql function) the two columns, and try to find the user in there:

Code:

SELECT
  *
FROM
  table2
WHERE
  CONCAT(column1, column2) LIKE '%alex'
OR
  CONCAT(column1, column2) LIKE 'alex%'