Page 1 of 1

select help

Posted: Tue Apr 29, 2008 9:11 am
by crollinjones
I have a table:
region_id, heatsource.
The region_id is repeated for each heatsource say Oil, Natural Gas, Electricity
I want to select all the records where the region_id has Oil but not Natural Gas. Some regions have Oil, Natural Gas, and Electricity heatsources but some have only Oil and Electricity.

Thanks for any help you can give!

Re: select help

Posted: Tue Apr 29, 2008 1:21 pm
by flying_circus

Code: Select all

 
SELECT * FROM `mytable` WHERE `region_id`='oil' AND `region_id`!='natural gas' ;
 

Re: select help

Posted: Tue Apr 29, 2008 1:40 pm
by crollinjones
flying_circus wrote:

Code: Select all

 
SELECT * FROM `mytable` WHERE `region_id`='oil' AND `region_id`!='natural gas' ;
 
I didn't explain myself clearly. I need to select all the records where the region_id has a heatsource of "Oil" but no corresponding record for heatsource = "Natural Gas". The above gives me all the records with heatsource="Oil" regardless of whether or not there is a corresponding heatsource = "Natural Gas". For example
Record 1 region_id=AB-A, heatsource="Oil"
Record 2 region_id=AB-A, heatsource="Natural Gas"
Record 3 region_id=NB-A, heatsource="Oil"
Record 4 region_id=NB-A, heatsource="Natural Gas"
Record 5 region_id=NF-A, heatsource="Oil"

I need a select query which selects Record 5.

Re: select help

Posted: Tue Apr 29, 2008 1:53 pm
by VladSun
[sql]SELECT     region_idFROM     regionLEFT JOIN    region AS region_1 ON region_1.region_id = region.region_id     AND region_1.heatsource != 'Oil' WHERE     ISNULL(region_1.region_id)    AND region.heatsource = 'Oil' [/sql]

Also, I don't think your DB table is normalized.

Re: select help

Posted: Tue Apr 29, 2008 2:16 pm
by RobertGonzalez
I agree with Vlad. You data needs to be reconsidered. This should be a simple select query at worst.

Re: select help

Posted: Tue May 06, 2008 2:57 pm
by califdon
Normalization, normalization, normalization. I venture to say that 90% of the Query problems people post are the result of poorly structured data. Maybe there should be a license requirement for developing databases, like a driver's license! :wink: