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!
