SQL queries - Help needed please

scottishblondie

SELECT firstName, lastName, employee.branchNo, branchTelephone
FROM employee, branch
WHERE positionId == 1 AND employee.branchNo == branch.branchNo

This should work for the first one.

ladymarmalade1970

I haven't done it for a while but try this
SELECT employeeFirstName, employeeLastName, whatever you want from branch table eg branch.location from employee, branch
INNER JOIN branch
ON employee.branchId=branch.branchId
WHERE employee.positionId=1
or have you tried that already??

victor2

ladymarmalade1970's query should work.
Another way is:
SELECT * FROM employee as t1 inner join branch as t2 on t1.branchNo=t2.branchNo where t1.positionId=1

For the age, you could include the following in the field list:
round(datediff(curdate(),dob)/365.25) as age

where dob is a date field in the employee table containing the date of birth, which will show as yyyy-mm-dd when you browse it.
The age calculation is not perfect and could be thrown out a year around birthdays. There is a way round it, but it gets quite complicated!

tincat

SELECT FirstName, Lastname, DateOfBirth, CURDATE(),
DATEDIFF(year,'DateOfBirth', year,'CURDATE()') AS age
FROM employee
Where age >= 25 AND jobId = 1

This is what I came up with for the datediff one, but my error message is

#1582 - Incorrect parameter count in the call to native function 'DATEDIFF'.

This following option did actually list all employees and showed their age in years, but I couldn't get it to only show the over 25's.

SELECT FirstName, Lastname, DateOfBirth, CURDATE(),
(YEAR( CURDATE( ) ) - YEAR( dateOfBirth) ) - (RIGHT( CURDATE( ), 5) < RIGHT( dateOfBirth, 5) )
AS age FROM staff

But if I add

WHERE age >= 25

it comes up with the error: '#1054 - Unknown column 'age' in 'where clause''



Regarding the first question, thank you so much scottishblondie. It worked although it didn't work with the ==. I had to use just one =.

I tried that method first, so thanks ladymarmalade and victor for your suggestions. I would have tried them next.

victor2

Unfortunately you have to put the entire expression in again instead of age in the where statement.

tincat

Hi again,

3) I need to list all the branches and give the total monthly salary paid out.

SELECT branchNo, sum(AnnualSalary)/12 as MonthlySalary from employee where branchNo = 5

This statement works and gives me a 2 column table with branchNo and Monthly Salary (average) but only for one branch.

Does anyone know how to add to the statement to list all of the branch numbers?

Thanks

tincat

victor2 wrote: »

Unfortunately you have to put the entire expression in again instead of age in the where statement.

I'm not sure how to do that. Please could you explain? Do I have to use () or ?

tincat

victor2 wrote: »

Unfortunately you have to
put the entire expression in again instead of age in the where statement.

OMG it worked - you are such a star. All of you are.

If you have a last idea of the average salary question that would be a hattrick. :j

victor2

tincat wrote: »

OMG it worked - you are such a star. All of you are.

If you have a last idea of the average salary question that would be a hattrick. :j

Try something like:
SELECT branchNo,sum(AnnualSalary)/12 as MonthlySalary from employee group by branchNo

The group by is the key to that one!

buglawton

If you are using PHPMyAdmin, it implies the data is on a host accessible to the internet. With such sensitive employee data, better make darn sure it's secure. Wiser to run the DB on your own PC (if you are not doing so already).