Query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
thatsme
Forum Commoner
Posts: 87
Joined: Sat Apr 07, 2007 2:18 am

Query

Post 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
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Query

Post 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.
thatsme
Forum Commoner
Posts: 87
Joined: Sat Apr 07, 2007 2:18 am

Re: Query

Post 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.
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Query

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