New Posts  All Forums:Forum Nav:

SQL select help! - Page 2

post #11 of 16
Can you provide a sample tabular output of what you expect from the query (along with a [stripped down] sample tabular data that the query will run against). Your textual explanation in the first post was too difficult to understand.
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 #12 of 16
I'd give it bash but I don't have SQL on this machine. What errors are you getting if any for the methods you have tried?

[edit]

what they hey, I got nothing to do so I'm going to download SQL express.

[solution]

reserved
Edited by nooboc2012 - 1/13/14 at 3:48am
Bandaids
(15 items)
 
  
MotherboardGraphicsHard DriveOptical Drive
Asrock Z77 Extreme 6 GTX 580 WD 10EALX ASUS DRW 
CoolingOSMonitorMonitor
Havik 140 Windows 7 Ultimate ASUS VH228T Toshiba 32RV600A 
MonitorKeyboardPowerCase
Compaq S2021a Microsoft Wired Keyboard 600 Aero Cool Strike X 1100w Asus Antec 
MouseMouse PadAudio
Logitech MX518 Mionix Ensis 320 Creative 2.1 
  hide details  
Reply
Bandaids
(15 items)
 
  
MotherboardGraphicsHard DriveOptical Drive
Asrock Z77 Extreme 6 GTX 580 WD 10EALX ASUS DRW 
CoolingOSMonitorMonitor
Havik 140 Windows 7 Ultimate ASUS VH228T Toshiba 32RV600A 
MonitorKeyboardPowerCase
Compaq S2021a Microsoft Wired Keyboard 600 Aero Cool Strike X 1100w Asus Antec 
MouseMouse PadAudio
Logitech MX518 Mionix Ensis 320 Creative 2.1 
  hide details  
Reply
post #13 of 16
Thread Starter 
Can't do tabular as my work browser doesn't play nice with the Javascript here frown.gif
Adz's post re-formatted it for me though - it's accurate.

@nooboc2012 - it doesn't report any errors, it just doesn't return the data I want (i.e. it returns "Bar" as well as "Xyz")

Bear in mind, that in live, I'm dealing with about 20000 items - the example is just the different combinations of status they are in, and there are thousands of lines in each one!

If it helps, I can put some "better" data up rather than just random numbers? Doesn't make a difference to the SQL, but might help write the script?
Little Beast
(12 items)
 
Black 'n' blue II
(15 items)
 
 
CPUGraphicsRAMHard Drive
Intel Core i7-4710MQ Nvidia Geforce GTX860M 2GB 16GB Kingston DDR3 1600MHz 240Gb Silicon Power S55/S60 SSD 
Hard DriveOSOSMonitor
1Tb Toshiba HDD 5400rpm Windows 8.1 Linux Mint 18 17.3" LED 1920x1080 
CaseMouseMouse PadAudio
PCSpecialist Optimus V ST17-860 Logitech MX518 Steelseries QcK Creative HS800 Fatal1ty 
CPUMotherboardGraphicsRAM
Core i7 860 @ 1.25V MSI P55-GD65 Xpertvision Radeon HD4850 4GB G.Skill Ripjaw 
Hard DriveOptical DriveCoolingOS
150Gb Velociraptor & 1Tb WD Caviar Black Opticon Lightscribe DVD-RW DL Noctua NH-U12P SE2 Vista Home Premium x64 
MonitorKeyboardPowerCase
Hyundai BlueH H224W 22" LCD Saitek Eclipse II Thermaltake Purepower RX 550 Galaxy III 
Mouse
Patuoxun optical gaming mouse 3200dpi 
  hide details  
Reply
Little Beast
(12 items)
 
Black 'n' blue II
(15 items)
 
 
CPUGraphicsRAMHard Drive
Intel Core i7-4710MQ Nvidia Geforce GTX860M 2GB 16GB Kingston DDR3 1600MHz 240Gb Silicon Power S55/S60 SSD 
Hard DriveOSOSMonitor
1Tb Toshiba HDD 5400rpm Windows 8.1 Linux Mint 18 17.3" LED 1920x1080 
CaseMouseMouse PadAudio
PCSpecialist Optimus V ST17-860 Logitech MX518 Steelseries QcK Creative HS800 Fatal1ty 
CPUMotherboardGraphicsRAM
Core i7 860 @ 1.25V MSI P55-GD65 Xpertvision Radeon HD4850 4GB G.Skill Ripjaw 
Hard DriveOptical DriveCoolingOS
150Gb Velociraptor & 1Tb WD Caviar Black Opticon Lightscribe DVD-RW DL Noctua NH-U12P SE2 Vista Home Premium x64 
MonitorKeyboardPowerCase
Hyundai BlueH H224W 22" LCD Saitek Eclipse II Thermaltake Purepower RX 550 Galaxy III 
Mouse
Patuoxun optical gaming mouse 3200dpi 
  hide details  
Reply
post #14 of 16
Please note that to keep things simple I have used varchar for all attributes, so you may have to cast some of them if they are different. Also apologies for all caps in the solutions but that's how I write my SQL, enjoy thumb.gif
Code:
-- solution

CREATE TABLE TBL_HEADER
(
        HEADGUID varchar(255) PRIMARY KEY,
        STATUS varchar(255),
        NAME varchar(255)
)

CREATE TABLE TBL_ITEM
(
        HEADGUID varchar(255),
        ITEMGUID varchar(255) PRIMARY KEY,
        STATUS varchar(255),
        FOREIGN KEY (HEADGUID) REFERENCES TBL_HEADER(HEADGUID)
)

INSERT INTO TBL_HEADER VALUES
('1', '1', 'Foo'),
('2', '1', 'Bar'),
('3', '1', 'Xyz'),
('4', '0', 'Zzz');

INSERT INTO TBL_ITEM VALUES
('1', '10', '1'),
('2', '20', '1'),
('2', '30', '0'),
('3', '40', '0'),
('4', '50', '0');

-- SOLUTION 1
-- USING SUB QUERY AND MAX
-- I COULD HAVE ALSO USED SUM BUT AS I CHOSE VARCHAR INSTEAD OF INT FOR MY STATUS IT WON'T WORK IN MY EXAMPLE

SELECT * FROM 
        (SELECT A.HEADGUID, A.NAME, MAX(B.STATUS) AS MAX_STATUS FROM
        TBL_HEADER A INNER JOIN TBL_ITEM B
        ON A.HEADGUID = B.HEADGUID
        WHERE A.STATUS <> 0
        GROUP BY A.HEADGUID, A.NAME
        ) C
WHERE C.MAX_STATUS = 0

-- SOLUTION 2
-- USING SUB QUERY AND EXCEPT

SELECT B.HEADGUID, B.NAME FROM
        (SELECT HEADGUID FROM TBL_HEADER WHERE STATUS <> 0
        EXCEPT
        SELECT HEADGUID FROM TBL_ITEM WHERE STATUS <> 0) A
INNER JOIN TBL_HEADER B
ON A.HEADGUID = B.HEADGUID

-- SOLUTION 3
-- USING IN SUB QUERY AND EXCEPT

SELECT B.HEADGUID, B.NAME FROM TBL_HEADER B
WHERE B.HEADGUID IN
        (SELECT HEADGUID FROM TBL_HEADER WHERE STATUS <> 0
        EXCEPT
        SELECT HEADGUID FROM TBL_ITEM WHERE STATUS <> 0)

-- SOLUTION 4
-- I'M PRETTY SURE YOU COULD ALSO CONVERT THE FIRST SOLUTION TO A SUB QUERY WITH AN IN CLAUSE, BUT THE SOLUTION WOULD START GETTING UNNECESSARILY LONG

-- SOLUTION 5
-- YOU COULD ALSO MANUALLY CREATE THE MAX() FUNCTION BY FIRSTLY INNER JOINING TBL_ITEM ON ITSELF AND USING > (GREATER THAN) ON STATUS
-- AGAIN, UNNECESSARILY LONG AS WE HAVE A MAX() FUNCTION ANYWAYS
-- A TYPICAL EXAM QUESTION WHERE YOUR ASKED TO PROVIDE A SOLUTION WITHOUT USING BUILT IN FUNCTIONS

Edited by nooboc2012 - 1/13/14 at 11:48am
Bandaids
(15 items)
 
  
MotherboardGraphicsHard DriveOptical Drive
Asrock Z77 Extreme 6 GTX 580 WD 10EALX ASUS DRW 
CoolingOSMonitorMonitor
Havik 140 Windows 7 Ultimate ASUS VH228T Toshiba 32RV600A 
MonitorKeyboardPowerCase
Compaq S2021a Microsoft Wired Keyboard 600 Aero Cool Strike X 1100w Asus Antec 
MouseMouse PadAudio
Logitech MX518 Mionix Ensis 320 Creative 2.1 
  hide details  
Reply
Bandaids
(15 items)
 
  
MotherboardGraphicsHard DriveOptical Drive
Asrock Z77 Extreme 6 GTX 580 WD 10EALX ASUS DRW 
CoolingOSMonitorMonitor
Havik 140 Windows 7 Ultimate ASUS VH228T Toshiba 32RV600A 
MonitorKeyboardPowerCase
Compaq S2021a Microsoft Wired Keyboard 600 Aero Cool Strike X 1100w Asus Antec 
MouseMouse PadAudio
Logitech MX518 Mionix Ensis 320 Creative 2.1 
  hide details  
Reply
post #15 of 16
Quote:
Originally Posted by chemicalfan View Post

Ok, got the query working (it wasn't "1" and "0" - it was "Active" and "Closed"!), but the data is wrong. It returns rows including header records where there are open & closed item records (in my example, header record 2, which shouldn't be returned)

So, in words, you want to select the ID and name of all Headers that are not closed and do not have any active items. Is that correct?
Code:
select h.HeadGUID, h.Name from Header h where h.Status != 'Closed' and 'Active' not in (select Status from Item where HeadGUID = h.HeadGUID);

Edited by jvolkman - 1/13/14 at 8:56pm
Cube
(9 items)
 
  
CPUMotherboardRAMHard Drive
i7-4930k EVGA X79 Dark Corsair Vengeance Pro Samsung 840 Pro 
CoolingMonitorPowerCase
Corsair H100i Viewsonic VP2770 EVGA SuperNova 1000P2 Corsair Air 540 
Mouse
Corsair M65 
  hide details  
Reply
Cube
(9 items)
 
  
CPUMotherboardRAMHard Drive
i7-4930k EVGA X79 Dark Corsair Vengeance Pro Samsung 840 Pro 
CoolingMonitorPowerCase
Corsair H100i Viewsonic VP2770 EVGA SuperNova 1000P2 Corsair Air 540 
Mouse
Corsair M65 
  hide details  
Reply
post #16 of 16
Thread Starter 
Thanks for all your replies (REP+ coming tonight!), got the solution and it wasn't any of the above (although the above look good). Got some advice that the status could be "converted" to a number/bit easily using a CASE WHEN statement, so the final code looked like (substituted names vs live):
Code:
SELECT t1.HeadGUID, t1.Name  

FROM Header t1  

INNER JOIN (  

SELECT HeadGUID  

FROM Item  

GROUP BY HeadGUID  

HAVING sum(CASE WHEN Status='Closed' THEN 0 ELSE 1 END)=0

) t2   

ON t1.HeadGUID = t2.HeadGUID  

WHERE t1.Status = 1 
Little Beast
(12 items)
 
Black 'n' blue II
(15 items)
 
 
CPUGraphicsRAMHard Drive
Intel Core i7-4710MQ Nvidia Geforce GTX860M 2GB 16GB Kingston DDR3 1600MHz 240Gb Silicon Power S55/S60 SSD 
Hard DriveOSOSMonitor
1Tb Toshiba HDD 5400rpm Windows 8.1 Linux Mint 18 17.3" LED 1920x1080 
CaseMouseMouse PadAudio
PCSpecialist Optimus V ST17-860 Logitech MX518 Steelseries QcK Creative HS800 Fatal1ty 
CPUMotherboardGraphicsRAM
Core i7 860 @ 1.25V MSI P55-GD65 Xpertvision Radeon HD4850 4GB G.Skill Ripjaw 
Hard DriveOptical DriveCoolingOS
150Gb Velociraptor & 1Tb WD Caviar Black Opticon Lightscribe DVD-RW DL Noctua NH-U12P SE2 Vista Home Premium x64 
MonitorKeyboardPowerCase
Hyundai BlueH H224W 22" LCD Saitek Eclipse II Thermaltake Purepower RX 550 Galaxy III 
Mouse
Patuoxun optical gaming mouse 3200dpi 
  hide details  
Reply
Little Beast
(12 items)
 
Black 'n' blue II
(15 items)
 
 
CPUGraphicsRAMHard Drive
Intel Core i7-4710MQ Nvidia Geforce GTX860M 2GB 16GB Kingston DDR3 1600MHz 240Gb Silicon Power S55/S60 SSD 
Hard DriveOSOSMonitor
1Tb Toshiba HDD 5400rpm Windows 8.1 Linux Mint 18 17.3" LED 1920x1080 
CaseMouseMouse PadAudio
PCSpecialist Optimus V ST17-860 Logitech MX518 Steelseries QcK Creative HS800 Fatal1ty 
CPUMotherboardGraphicsRAM
Core i7 860 @ 1.25V MSI P55-GD65 Xpertvision Radeon HD4850 4GB G.Skill Ripjaw 
Hard DriveOptical DriveCoolingOS
150Gb Velociraptor & 1Tb WD Caviar Black Opticon Lightscribe DVD-RW DL Noctua NH-U12P SE2 Vista Home Premium x64 
MonitorKeyboardPowerCase
Hyundai BlueH H224W 22" LCD Saitek Eclipse II Thermaltake Purepower RX 550 Galaxy III 
Mouse
Patuoxun optical gaming mouse 3200dpi 
  hide details  
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Coding and Programming