CODEWITHSUNDEEP
phpmysqlsql
Akash Gupta
Akash Gupta

Reputation: 318

selecting a record from a table based on column value of another table

I know forming this query is trivial, but I am not able frame the exact query. Based on what I have read so far, the solution may include joining two tables. Here is the problem :

TABLE_ONE {S_NO, DK_NO, FRM , DATE}

TABLE_TWO {S_NO, DK_NO , TO}

I want to select a record from TABLE_ONE based on an input value of DK_NO and value checked for TO field of TABLE_TWO or FRM field of TABLE_ONE

unsuccessful trials so far:

1)

$sql = "select * from TABLE_ONE where DK_NO like '%".$dkno."%' and (FRM='".$department."' or  (TABLE_TWO.DK_NO like '%".$dakno."%' and TABLE_TWO.TO='".$department."'))";

2)

$sql = "select * from TABLE_ONE where DK_NO like '%".$dkno."%' and (FRM='".$department."' or   TABLE_TWO.TO='".$department."')";

Upvotes: 0

Views: 105

Answers (2)

vee
vee

Reputation: 38645

Recommended reading: JOIN.

Using inner join:

"select t1.* 
from table_one t1 inner join table_two t2 on t1.dk_no = t2.dk_no
where (t1.frm = '". $department. "' or t2.to = '".$department. "') 
    and t1.dk_no like '%" . $dkno . "%'";

Upvotes: 1

Sean
Sean

Reputation: 12433

You need to join your tables. Try something like -

SELECT
    T1.*
FROM
    TABLE_ONE T1
JOIN
    TABLE_TWO T2
ON
    T1.DK_NO = T2.DK_NO
WHERE
    T1.DK_NO like '%".$dkno."%' 
AND
    (T1.FRM='".$department."' OR TABLE_TWO.TO='".$department."')

Upvotes: 1

Related Questions