New Posts  All Forums:Forum Nav:

SQL help

post #1 of 5
Thread Starter 
*solved*
Edited by surfbumb - 3/15/13 at 12:08pm
Black Silence
(15 items)
 
  
CPUMotherboardRAMHard Drive
i5 3570k @ 4.5 GHz Asus P8Z77-M Pro Kingston HyperX Genesis 8 GB - 1600 MHz Seagate Barracuda 250 GB 
Optical DriveCoolingOSMonitor
Samsung WriteMaster Noctua NH-D14 Windows 10 ASUS VS24AH-P 
KeyboardPowerCaseMouse
Logitech Navigator Enermax Infiniti 650W Fractal R3 Black Pearl Razer Death Adder 
Mouse PadAudio
SteelSeries QcK Mass Altec Lansing FX4021 
  hide details  
Reply
Black Silence
(15 items)
 
  
CPUMotherboardRAMHard Drive
i5 3570k @ 4.5 GHz Asus P8Z77-M Pro Kingston HyperX Genesis 8 GB - 1600 MHz Seagate Barracuda 250 GB 
Optical DriveCoolingOSMonitor
Samsung WriteMaster Noctua NH-D14 Windows 10 ASUS VS24AH-P 
KeyboardPowerCaseMouse
Logitech Navigator Enermax Infiniti 650W Fractal R3 Black Pearl Razer Death Adder 
Mouse PadAudio
SteelSeries QcK Mass Altec Lansing FX4021 
  hide details  
Reply
post #2 of 5
SELECT Order_Num AS "Order Number", Name AS "Sales Rep", Company AS "Customer Name", Name AS "Customer Rep"

You're selecting Name twice, but aliasing it different.
Ol' Sandy
(28 items)
 
"Zeus"
(12 items)
 
Elite Preview
(6 items)
 
CPUMotherboardGraphicsRAM
Intel Xeon E3-1230v3 Gigabyte GA-Z97X-UD5H-BK MSI Gaming GTX 980 Kingston 32GB (4x8) 
Hard DriveHard DriveHard DriveHard Drive
Plextor PX-256M5S 256GB Samsung EVO 1TB Hitachi HDS721010CLA332 Hitachi HDS723020BLA642 
Hard DriveHard DriveHard DriveOptical Drive
Hitachi HDS723020BLA642 Hitachi HUA722010CLA330 WDC WD10EARS-00Z5B1 TSSTcorp CDDVDW SH-S223B 
CoolingCoolingOSMonitor
Phanteks PH-TC14PE with TY-140's Lamptron FCv5 (x2) Windows 8 Pro 64-bit Dell U2412M 
MonitorMonitorMonitorKeyboard
Dell U2412M Dell U2212HM Dell U2713HM Topre Realforce 87UB | Ducky DK9087 G2 Pro 
PowerCaseMouseMouse Pad
Corsair AX-750 Corsair Obsidian 650D Logitech G700 XTRAC Ripper XXL 
AudioAudioAudioAudio
Beyerdynamic DT-770 Pro 250ohm Schiit Bifrost DAC Schiit Asgard 2 HiVi Swan M50W 2.1 
CPUMotherboardRAMHard Drive
Intel Xeon E5-2620 Super Micro X9SRL-F-B 128GB 1333MHz LSI 9271-8i 
OSPowerCase
VMware ESXi 5.5 SeaSonic SS-400FL2 Fractal Define R3 
CPUMotherboardGraphicsRAM
Intel Core i5-3437U HP EliteBook Folio 9470m  Intel HD Graphics 4000  16GB DDR3 SDRAM 
Hard DriveOS
256GB SSD Windows 10 Insider Preview 
  hide details  
Reply
Ol' Sandy
(28 items)
 
"Zeus"
(12 items)
 
Elite Preview
(6 items)
 
CPUMotherboardGraphicsRAM
Intel Xeon E3-1230v3 Gigabyte GA-Z97X-UD5H-BK MSI Gaming GTX 980 Kingston 32GB (4x8) 
Hard DriveHard DriveHard DriveHard Drive
Plextor PX-256M5S 256GB Samsung EVO 1TB Hitachi HDS721010CLA332 Hitachi HDS723020BLA642 
Hard DriveHard DriveHard DriveOptical Drive
Hitachi HDS723020BLA642 Hitachi HUA722010CLA330 WDC WD10EARS-00Z5B1 TSSTcorp CDDVDW SH-S223B 
CoolingCoolingOSMonitor
Phanteks PH-TC14PE with TY-140's Lamptron FCv5 (x2) Windows 8 Pro 64-bit Dell U2412M 
MonitorMonitorMonitorKeyboard
Dell U2412M Dell U2212HM Dell U2713HM Topre Realforce 87UB | Ducky DK9087 G2 Pro 
PowerCaseMouseMouse Pad
Corsair AX-750 Corsair Obsidian 650D Logitech G700 XTRAC Ripper XXL 
AudioAudioAudioAudio
Beyerdynamic DT-770 Pro 250ohm Schiit Bifrost DAC Schiit Asgard 2 HiVi Swan M50W 2.1 
CPUMotherboardRAMHard Drive
Intel Xeon E5-2620 Super Micro X9SRL-F-B 128GB 1333MHz LSI 9271-8i 
OSPowerCase
VMware ESXi 5.5 SeaSonic SS-400FL2 Fractal Define R3 
CPUMotherboardGraphicsRAM
Intel Core i5-3437U HP EliteBook Folio 9470m  Intel HD Graphics 4000  16GB DDR3 SDRAM 
Hard DriveOS
256GB SSD Windows 10 Insider Preview 
  hide details  
Reply
post #3 of 5
You need to join salesreps twice, once with orders ( for name of the rep who placed the order) & then with customers (for the name of the rep for that customer)

This is working.
Code:

select 
        O.Order_Num as [Order Number],
        Sr1.name as [Sales Rep],
        C.Company AS [Customer Name],
        SR2.name as [Customer Rep]
from ORDERS O Join Customers C
on O.Cust = C.Cust_num
Join  salesreps SR1
on O.Rep = SR1.Empl_num
Join salesreps SR2
on C.Cust_Rep = SR2.Empl_num

My System
(13 items)
 
  
CPUMotherboardGraphicsRAM
i5 2500k ASUS - P8Z68-V/GEN3 GTX 770 DDR3 - 8G 
Hard DriveOSMonitorPower
WD Green Win 7 x64 Dell ST2320L Corsair 750TX 
CaseMouse
Coolermaster 310 Logitech G502 
  hide details  
Reply
My System
(13 items)
 
  
CPUMotherboardGraphicsRAM
i5 2500k ASUS - P8Z68-V/GEN3 GTX 770 DDR3 - 8G 
Hard DriveOSMonitorPower
WD Green Win 7 x64 Dell ST2320L Corsair 750TX 
CaseMouse
Coolermaster 310 Logitech G502 
  hide details  
Reply
post #4 of 5
Thread Starter 
Quote:
Originally Posted by _Nikhil View Post

You need to join salesreps twice, once with orders ( for name of the rep who placed the order) & then with customers (for the name of the rep for that customer)

This is working.
Code:

select 
        O.Order_Num as [Order Number],
        Sr1.name as [Sales Rep],
        C.Company AS [Customer Name],
        SR2.name as [Customer Rep]
from ORDERS O Join Customers C
on O.Cust = C.Cust_num
Join  salesreps SR1
on O.Rep = SR1.Empl_num
Join salesreps SR2
on C.Cust_Rep = SR2.Empl_num


is there another method to write this even though I understand it...yes it does work, but we haven't gone over using the Join keyword yet or on keyword. We've gone over equi joins and sub queries, basically where you have SELECT FROM WHERE inside a WHERE.
+REP though.
Edited by surfbumb - 3/15/13 at 11:58am
Black Silence
(15 items)
 
  
CPUMotherboardRAMHard Drive
i5 3570k @ 4.5 GHz Asus P8Z77-M Pro Kingston HyperX Genesis 8 GB - 1600 MHz Seagate Barracuda 250 GB 
Optical DriveCoolingOSMonitor
Samsung WriteMaster Noctua NH-D14 Windows 10 ASUS VS24AH-P 
KeyboardPowerCaseMouse
Logitech Navigator Enermax Infiniti 650W Fractal R3 Black Pearl Razer Death Adder 
Mouse PadAudio
SteelSeries QcK Mass Altec Lansing FX4021 
  hide details  
Reply
Black Silence
(15 items)
 
  
CPUMotherboardRAMHard Drive
i5 3570k @ 4.5 GHz Asus P8Z77-M Pro Kingston HyperX Genesis 8 GB - 1600 MHz Seagate Barracuda 250 GB 
Optical DriveCoolingOSMonitor
Samsung WriteMaster Noctua NH-D14 Windows 10 ASUS VS24AH-P 
KeyboardPowerCaseMouse
Logitech Navigator Enermax Infiniti 650W Fractal R3 Black Pearl Razer Death Adder 
Mouse PadAudio
SteelSeries QcK Mass Altec Lansing FX4021 
  hide details  
Reply
post #5 of 5
Thread Starter 
nvm, I figured it out
Code:
select 
        O.Order_Num as "Order Number",
        SR1.name as "Sales Rep",
        C.Company AS "Customer Name",
        SR2.name as "Customer Rep"
FROM Orders O, Customers C, Salesreps SR1, Salesreps SR2
WHERE O.Cust = C.Cust_Num AND O.Rep = SR1.Empl_Num AND C.Cust_Rep = SR2.Empl_Num;


Black Silence
(15 items)
 
  
CPUMotherboardRAMHard Drive
i5 3570k @ 4.5 GHz Asus P8Z77-M Pro Kingston HyperX Genesis 8 GB - 1600 MHz Seagate Barracuda 250 GB 
Optical DriveCoolingOSMonitor
Samsung WriteMaster Noctua NH-D14 Windows 10 ASUS VS24AH-P 
KeyboardPowerCaseMouse
Logitech Navigator Enermax Infiniti 650W Fractal R3 Black Pearl Razer Death Adder 
Mouse PadAudio
SteelSeries QcK Mass Altec Lansing FX4021 
  hide details  
Reply
Black Silence
(15 items)
 
  
CPUMotherboardRAMHard Drive
i5 3570k @ 4.5 GHz Asus P8Z77-M Pro Kingston HyperX Genesis 8 GB - 1600 MHz Seagate Barracuda 250 GB 
Optical DriveCoolingOSMonitor
Samsung WriteMaster Noctua NH-D14 Windows 10 ASUS VS24AH-P 
KeyboardPowerCaseMouse
Logitech Navigator Enermax Infiniti 650W Fractal R3 Black Pearl Razer Death Adder 
Mouse PadAudio
SteelSeries QcK Mass Altec Lansing FX4021 
  hide details  
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Coding and Programming