select help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
crollinjones
Forum Newbie
Posts: 2
Joined: Tue Apr 29, 2008 9:08 am

select help

Post 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!
User avatar
flying_circus
Forum Regular
Posts: 732
Joined: Wed Mar 05, 2008 10:23 pm
Location: Sunriver, OR

Re: select help

Post by flying_circus »

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

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: select help

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: select help

Post by RobertGonzalez »

I agree with Vlad. You data needs to be reconsidered. This should be a simple select query at worst.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: select help

Post 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:
Post Reply