Page 1 of 1

Query

Posted: Sun Jul 06, 2008 1:07 pm
by thatsme
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

Re: Query

Posted: Sun Jul 06, 2008 3:27 pm
by jaoudestudios
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...

Code: Select all

$q = "SELECT CONCAT(staff_fname, staff_lname) AS staff_name FROM staff WHERE staff_org_id = '".$staff_org_id."'";
Do you need to CONCAT the results?

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";
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.

Re: Query

Posted: Sun Jul 06, 2008 8:33 pm
by thatsme
I am not sure what result you want.
For example a staff who is a group director will have the following values,

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.

Re: Query

Posted: Mon Jul 07, 2008 2:02 am
by jaoudestudios
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....

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'";