Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › Requesting help with SQL query
New Posts  All Forums:Forum Nav:

Requesting help with SQL query

post #1 of 2
Thread Starter 
I'm having a lot of trouble with this since I haven't touched SQL is over a year.

Here's the tables and their relationships:
171

I have to display the First and Last name along with the amount of software they have assigned, I figured the easiest way would be to simply count the amount of times the EmpID appears in the SoftwareAssigned intersection table. Here's what I have so far.
Code:
SELECT Employee.Lname+""+Employee.Fname AS Name
FROM Employee
WHERE Employee.EmpID IN
(SELECT COUNT(SoftwareAssigned.EmpID) AS NumItems
FROM Software, Employee, SoftwareAssigned
WHERE SoftwareAssigned.EmpID = Employee.EmpID)
ORDER BY Employee.Lname;

Any help would really be appreciated!
CYG X-1
(15 items)
 
  
Reply
CYG X-1
(15 items)
 
  
Reply
post #2 of 2
PurchaseDate should not be a primary key in the Software table. If software can be purchased multiple times, there should be a Purchase table with a PurchaseID key and a SoftwarePurchase table linking them with SoftID and PurchaseID as keys. If it can't be purchased more than once, then placing a primary key on PurchaseDate simply serves no purpose. Also, if you don't need the software description in your query result, you don't need to link to the Software table. If you are linking tables, using the WHERE clause is deprecated. Use JOIN instead.
Code:
SELECT Lname + " " + Fname AS Name, COUNT(*) AS NumItems
FROM Employee INNER JOIN SoftwareAssigned 
  ON Employee.EmpID = SoftwareAssigned.EmpID
GROUP BY Lname, Fname
ORDER BY Lname;

Edited by ChaoticKinesis - 2/6/12 at 9:42pm
     
CPUMotherboardGraphicsRAM
Intel Core i7-4770K Gigabyte G1.Sniper M5 EVGA GTX 780 SC Crucial Ballistix Sport 2 x 8GB 
Hard DriveHard DriveHard DriveOptical Drive
Samsung 840 Pro 256GB Seagate Barracuda 3TB Seagate Barracuda 4TB Asus BD-ROM 
CoolingOSMonitorKeyboard
Noctua NH-D14 Windows 8 Pro Dell U2713HM Ducky YOTD (MX Brown) 
PowerCaseMouseMouse Pad
SeaSonic SS-760XP2 Fractal Design Arc Mini Roccat Savu SteelSeries 9HD 
Audio
Beyerdynamic DT990 250 Ohm 
CPUMotherboardGraphicsRAM
AMD A6-3500 Asrock A75M HD 6530D Samsung 4GB DDR3 1600  
Hard DriveHard DriveHard DriveOptical Drive
Intel 320 80GB Samsung Spinpoint F4 2TB WD Caviar Green 3TB Sony BD-Rom 
CoolingOSMonitorKeyboard
Noctua NH-C12P SE14 Win 7 Professional 64-bit Samsung LN46C600 Lenovo N5902 
PowerCaseOther
Corsair CX430 Antec Veris Fusion HDHomeRun Prime 
CPUMotherboardGraphicsRAM
AMD Phenom II X3 B55 Asus M5A99X EVO Sapphire HD 7950 G.Skill 8GB DDR3 
Hard DriveOptical DriveCoolingMonitor
Samsung F3 1TB LG DVD-RW CM Hyper 212 Plus Dell U2312HM 
KeyboardPowerCaseMouse
Pink KBT Race (MX Blue) Lepa G700-MA Corsair 500R Razer Abyssus 
Mouse Pad
Razer Goliathus Speed 
  hide details  
Reply
     
CPUMotherboardGraphicsRAM
Intel Core i7-4770K Gigabyte G1.Sniper M5 EVGA GTX 780 SC Crucial Ballistix Sport 2 x 8GB 
Hard DriveHard DriveHard DriveOptical Drive
Samsung 840 Pro 256GB Seagate Barracuda 3TB Seagate Barracuda 4TB Asus BD-ROM 
CoolingOSMonitorKeyboard
Noctua NH-D14 Windows 8 Pro Dell U2713HM Ducky YOTD (MX Brown) 
PowerCaseMouseMouse Pad
SeaSonic SS-760XP2 Fractal Design Arc Mini Roccat Savu SteelSeries 9HD 
Audio
Beyerdynamic DT990 250 Ohm 
CPUMotherboardGraphicsRAM
AMD A6-3500 Asrock A75M HD 6530D Samsung 4GB DDR3 1600  
Hard DriveHard DriveHard DriveOptical Drive
Intel 320 80GB Samsung Spinpoint F4 2TB WD Caviar Green 3TB Sony BD-Rom 
CoolingOSMonitorKeyboard
Noctua NH-C12P SE14 Win 7 Professional 64-bit Samsung LN46C600 Lenovo N5902 
PowerCaseOther
Corsair CX430 Antec Veris Fusion HDHomeRun Prime 
CPUMotherboardGraphicsRAM
AMD Phenom II X3 B55 Asus M5A99X EVO Sapphire HD 7950 G.Skill 8GB DDR3 
Hard DriveOptical DriveCoolingMonitor
Samsung F3 1TB LG DVD-RW CM Hyper 212 Plus Dell U2312HM 
KeyboardPowerCaseMouse
Pink KBT Race (MX Blue) Lepa G700-MA Corsair 500R Razer Abyssus 
Mouse Pad
Razer Goliathus Speed 
  hide details  
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Coding and Programming
Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › Requesting help with SQL query