SQL queries - Help needed please

tincat

Hi,

I have a number of tables and have to perform queries on them. Some are easy but some are a bit complicated.

Can anyone help me with the following please?

Picture 3 tables:

employee: employeeNo, branchNo, positionId, firstName, lastName, Address etc
branch: branchNo, branchAddress, branchTelephone
position: positionId, positionName

1) I need to display the manager name, branch number and branch telephone number. The 'manager' position has a positionId = 1.

I tried a number of things eg:

SELECT employee.FirstName employee.LastName branch.branchNo branch.branchAddress WHERE position.positionId = 1

but haven't been able to get it to work.

2) The employee record has a field for birth dates for the employees. I need to list all employees over the age of 25. Now I know the SQL for that : SELECT COUNT (column) FROM table WHERE age > 25, but I don't know how to translate the birthdate into an age and I can't find a formula that fits a birthdate.


Please help to explain if possible. Many thanks.

tincat

SELECT employeeFirstName, employeeLastName from employee
INNER JOIN branch
ON employee.branchId=branch.branchId
WHERE employee.positionId=1

The above query returns the names of the managers, but when I try to add in the branch details (address, phone number and id) it all goes wrong.

If you can explain would be so grateful

victor2

What links the tables? You only show positionId in the employee table. The employee and branch tables presumably have branchId as a common field. Is positionId also in the position table?
Also, are you using Microsoft SQL, or another variant (although they are very similar)?

Edit:
Look at the DATEDIFF function to calculate age - that does vary with different SQL database engines.

hubert_cumberdale

are the dates written in as human dates or

sql dates i think sql dates are written a bit strange

it could be usefull if you could generate a colunm with there age in maybe calculate it from the date of birth and the current date

scottishblondie

Can you please post all the fields for the database tables.

You should be able to use "DATEDIFF" to calculate the difference between the birthday and the current date, and then calculate the number of years from that.

victor2

hubert_cumberdale wrote: »

it could be usefull if you could generate a colunm with there age in maybe calculate it from the date of birth and the current date

You don't really want to store their age - it has a habit of changing as time goes by!
Better to calculate it from date of birth when required, unless you want to store their age as of a particular event.

tincat

victor2 wrote: »

What links the tables? You only show positionId in the employee table. The employee and branch tables presumably have branchId as a common field. Is positionId also in the position table?
Also, are you using Microsoft SQL, or another variant (although they are very similar)?

Edit:
Look at the DATEDIFF function to calculate age - that does vary with different SQL database engines.

I'm using phpMyAdmin.

Yes, positionId is in the employee table and the position table. Sorry. I will go and edit that post. I'll google the DATEDIFF function, thanks

tincat

hubert_cumberdale wrote: »

are the dates written in as human dates or

sql dates i think sql dates are written a bit strange

it could be usefull if you could generate a colunm with there age in maybe calculate it from the date of birth and the current date

They're entered in as 1984-03-25

victor2

Are you using a MySQL database then? phpMyAdmin often goes along with that.

tincat

scottishblondie wrote: »

Can you please post all the fields for the database tables.

You should be able to use "DATEDIFF" to calculate the difference between the birthday and the current date, and then calculate the number of years from that.

Sorry, my initial post wasn't clear

employee: employeeNo, branchNo, positionId, firstName, lastName, Address, gender, employeeSalary
branch: branchNo, branchStreetAddress, branchTown, branchPostcode, branchTelephone
position: positionId, positionName, positionDescription

The foreign keys in blue.

Thanks

tincat

victor2 wrote: »

Are you using a MySQL database then? phpMyAdmin often goes along with that.

I think so. Sorry, I'm new to this.

The database tables are all entered into phpmyadmin, and I've set primary keys and foreign keys among all the tables (there are 10 tables in all), and then I have to do the SQL queries from phpMyAdmin.

all the tables are in innoDB if that makes any difference. I'm just going to google the datediff function, but will be back as soon as poss for any help.

Thanks all