0

Hi all, i would like to ask,
if i have 3 tables in database and i have a search bar in my web page to search staff id to update details.

The staff id being searched in search bar is assume as $search_id = $_POST['searchid'];

The 3 tables in database are:-
1st table - details
details_id, staff_id, staff_name

2nd table - approval
details_id, staff_id, approve_date

3rd table - disapproval
details_id, staff_id, disapproval_date

how can i write all conditions in a single query to perform action below:-
1) select all the data in DETAILS table WHERE the staff_id equal to the $search_id AND order by DETAILS ID in DESC
2) select the approve date from APPROVAL table WHERE the staff_id equal to the $search_id AND order by DETAILS ID in DESC
3) select the disapproval date from DISAPPROVAL table WHERE the staff_id equal to the $search_id AND order by DETAILS ID in DESC

**I only able to write up to:-
SELECT * FROM details WHERE staff_id LIKE '$search_id ORDER BY details_id DESC;

anyone can assist me on combining all these conditions into a single query?**
Thank you so much!!!!

3
Contributors
3
Replies
14
Views
3 Years
Discussion Span
Last Post by coullone
1

This should work, haven't tested it but it's a standard join of three tables sharing a common column. Of course the select can be changed to refer to the specific columns from each table you need by referring to their name e.g. dis.disapproval_date

SELECT * FROM details AS d JOIN approval AS a ON d.staff_id = a.staff_id JOIN disapproval AS dis
ON dis.staff_id = d.staff_id WHERE d.staff_id LIKE '%$search_id%' ORDER BY d.id DESC;
0

That showed me a way of fixing a niggling little problem that I never got round to fixing!
Thanks chili91

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.