Anyone Familiar with SQL?

[AgnessGP]

Hi,

Basically require some help running some SQL queries. The program I am using for this is phpmyadmin (piece of s***) but it's what we've got.

Let me know if you can assist!

Thank you in advance!

Sam

 

[colin79666]

Might be able to help, I've done bits of MySQL in the past though most of my work stuff is MSSQL these days.

What sort of thing are you trying to do?

 

[Freebo]

What queries do you need to run?

 

[fingers99]

About 15 years since I've used MySQL, but it's all incredibly well documented (and MySQL has an excellent and helpful forum, too).

For PhpMyAdmin try this, for MySQL try this.

One day, Windows developers will write decent documentation...............

 

[AgnessGP]

thanks for the help! those sites were really useful.

Iv been doing things like this (one that took me about 3 hours to crack because im new to this)

SELECT manager_allocation.Allocation_ID, manager_allocation.person_ID,
P.Firstname, P.Surname, manager_allocation.manager_ID, SR.Role_name, M.Surname,
M.Firstname
FROM manager_allocation
LEFT OUTER JOIN person P ON
manager_allocation.person_ID = P.person_ID
LEFT OUTER JOIN person M ON
manager_allocation.manager_ID = M.person_ID
INNER JOIN staff_role_allocation
SRA ON P.Person_ID = SRA.Person_ID
INNER JOIN staff_role SR ON SRA.Role_ID =
SR.Role_ID

Hating it! Every task is becoming a few hours struggle!

 

[TallTom]

Sam

Best bit of advice I can give you, is what I do if I am doing complex Excel formulas (Yes they can get reasonably complex!)

Write down the report output in text on a piece of paper, sort of like a pseudo code, then link bits to it, etc find the links and then start writing your query, will make writing them quicker in the future.

Also doubles as tick list to ensure you have got everything you want in your report, and make you think to ensure that you do it the most effiecent way.

It is a bit difficult to show an example of what I mean on Fiatforum! but I will try show below

Report:
Person, HRID, Desk, Computer Serial, Department

Data:
Person
(First/Last - Table x - Link HR ID)

HR ID
(P Key)

Desk
(Location table - HRID link)

Computer Serial
(Equipment Table - Link Desk)

Department
(Department table - HR ID)

Then you combine and add actual other bits to give you the above.

 

[AgnessGP]

Spot on mate! been using this since and managed to work out most of them now! Cheers!

 

[AgnessGP]

for example, struggling with this:

SELECT D.fk1_department_ID



FROM allocation_to_department



INNER JOIN allocation_to_department D ON D.fk1_department_id =
D.fk1_department_id


INNER JOIN allocation_to_department E ON D.fk1_department_id =
E.fk1_department_id


INNER JOIN allocation_to_department F On D.fk1_department_id =
F.fk1_department_id


INNER JOIN allocation_to_department G On D.fk1_department_id =
G.fk1_department_id





WHERE D.fk1_department_id=3

What I need to return is all staff that have worked in every department. There are 4 departments, and clearly from the below image, person 1 has worked in department 1,2,3,4. But how do I return this?



My sql above tries to differentiate the rows of data within one column but doesnt work when i introduce "AND =2, 3, etc". It only works for =X.

 

[colin79666]

It would be handy if you could us a list of fields in each table.

Anyway it sounds like you want something like
SELECT PERSON_ID p
JOIN DEPARTMENT_ID d [do all your joining stuff to link the tables]
WHERE department_id IN (1,2,3,4)

 

[AgnessGP]

its here mate the list:

Not sure if i need to join any other tables into this, I presumed it would be self joins. Either way iv spent hours and got no where

Tables:

allocation_id
start_date
end_date
home_department
fk1_department_id
fk2_person_id

If you need anything else to help understand let me know! I am really grateful for any help offered as really struggling!

 

[colin79666]

This is horrible but would work:

SELECT DISTINCT( fk2_person_id) FROM allocation_to_department
WHERE fk2_person_id IN (SELECT fk2_person_id FROM allocation_to_department WHERE fk1_department_id = 1)
AND
fk2_person_id IN (SELECT fk2_person_id FROM allocation_to_department WHERE fk1_department_id = 2)
AND
fk2_person_id IN (SELECT fk2_person_id FROM allocation_to_department WHERE fk1_department_id = 3)
AND
fk2_person_id IN (SELECT fk2_person_id FROM allocation_to_department WHERE fk1_department_id = 4)

 

[AgnessGP]

brilliant, this works! I know its messy but it works! thanks a lot

 

[AgnessGP]

cany anyone assist with anthing else? got some other stuff im struggling with

 

[AgnessGP]

Right, im looking to update a field based on if a member of staff has worked there for more than 5 years. It is looking like a datediff function but this doesnt seem to be recognised by this version ofSQL.

If anyone thinks they can help it would be much appreciated! Been trying with this for hours now!

Thanks,

Sam

 

[colin79666]

UPDATE some_field
SET VALUE = 'whatever'
WHERE start_date < '2008-01-07' (use appropriate date)

You could use DATEADD to take 5 years off the SYSDATE if you need to automate it.
http://www.w3schools.com/sql/func_dateadd.asp

 

[AgnessGP]

thanks a lot colin!

sorry not replied sooner.

Youve been a huge help

Thank you