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!!!!

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;

thanks alot for your answer. appreciate! :)

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.