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!
select help
Moderator: General Moderators
- flying_circus
- Forum Regular
- Posts: 732
- Joined: Wed Mar 05, 2008 10:23 pm
- Location: Sunriver, OR
Re: select help
Code: Select all
SELECT * FROM `mytable` WHERE `region_id`='oil' AND `region_id`!='natural gas' ;
-
crollinjones
- Forum Newbie
- Posts: 2
- Joined: Tue Apr 29, 2008 9:08 am
Re: select help
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 exampleflying_circus wrote:Code: Select all
SELECT * FROM `mytable` WHERE `region_id`='oil' AND `region_id`!='natural gas' ;
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
[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.
Also, I don't think your DB table is normalized.
There are 10 types of people in this world, those who understand binary and those who don't
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Re: select help
I agree with Vlad. You data needs to be reconsidered. This should be a simple select query at worst.
Re: select help
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! 