Hello,
I have the following tables and their fields in them
staff :- staff_id, staff_fname, staff_lname, staff_desig_id, staff_org_id
group:- group_id, group_name
division:- division_id, division_name
section:- section_id, section_name
staff_org:- staff_org_id, section_id, division_id, group_id
In the staff_org table,
If the section_id is empty and division_id is null and group_id is not null then the staff member belongs to whole group
If the section_id is empty and division_id is not null and group_id is not null then the staff member belongs to whole division
If the section_id is not null and division_id is not null and group_id is not null then the staff belongs to the indicated section
A staff who is a group director will belong to the whole group. So the first condition gets satisfied. To retrive his details i am using the below query,
$q = "SELECT CONCAT(staff_fname, staff_lname) AS staff_name FROM staff WHERE staff_org_id = 1";
I am checking staff_org_id with a static id which i think iam doing wrong thing. Can someone help in getting the above query correctly?
Thanks
Query
Moderator: General Moderators
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: Query
I am not sure what result you want.
Could you give an example of the result you are expecting?
You can put a php variable instead of the static value 1 by using this...
Do you need to CONCAT the results?
You can join two tables together...(without the CONCAT)
However, if there is not matching result for staff_org_id in both tables the result will not get displayed. If you want the result to show from staff regardless if there is a matching entry in staff_org then you will need to do a left on the staff table.
Could you give an example of the result you are expecting?
You can put a php variable instead of the static value 1 by using this...
Code: Select all
$q = "SELECT CONCAT(staff_fname, staff_lname) AS staff_name FROM staff WHERE staff_org_id = '".$staff_org_id."'";You can join two tables together...(without the CONCAT)
Code: Select all
$q = "SELECT staff_fname, staff_lname FROM staff, staff_org WHERE staff.staff_org_id = staff_org.staff_org_id";Re: Query
For example a staff who is a group director will have the following values,I am not sure what result you want.
staff
staff_id = 1
staff_fname = 'GD_FNAME'
staff_lname = 'FD_LNAME'
staff_desig_id = 1
staff_org_id = 1
staff_org
staff_org_id = 1
section_id = null
division_id = null
group_id = 1
I want to display the group director name from staff table.
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: Query
Do you want to display all the Group Director's names? if so will they all have the same values in staff_org?
If so....
If so....
Code: Select all
q = "SELECT staff_fname, staff_lname FROM staff, staff_org WHERE staff.staff_org_id = staff_org.staff_org_id AND section_id = 'NULL' AND division_id = 'NULL' AND group_id = '1'";