Sign in to follow this  
Boris Karloff

[web] SQL: row where field does not appear more than once.

Recommended Posts

I'm going absolutely mad over some SQL statement I can't seme to get right. I have a table phonenumbers which has among other things a field "id" and a field "type". Neither of these are unique, so an example would be
id     type
-----------
43     0
35     0
43     6
12     0
12     6

So a certain ID may be of multiple types. In this case, ID 43 is of both type 0 and 6. ID 35 is only of type 0, and ID 12 is of type 0 and of type 6, just like id 43. I look for these ID numbers in another table called subscriber. select *, (housenumber+0.00) as housenr from subscriber , phonenumbers where postalcode='3351 AL' and housenumber='41' and phonenumbers.id = subscriber.id AND phonenumbers.type =0 order by postalcode,housenr limit 25 thusly. This returns phonenumber data from certain id's that have a type 0. this works fine. However, now I want to expand this so that it returns data from id's that are -only- of type 0. So ID 12 and 43 would be ignored, because they are also type 6. I just can't seem to find a proper statement for this. Anyone have an idea?

Share this post


Link to post
Share on other sites
It sounds like you need to use DISTINCT. Something like:

select DISTINCT idfield, field1, field2, field3, (housenumber+0.00) as housenr from subscriber , phonenumbers where postalcode='3351 AL' and housenumber='41' and phonenumbers.id = subscriber.id AND phonenumbers.type =0 order by postalcode,housenr limit 25

Obviously replacing the fields in my example query, as necessary. DISTINCT eliminates duplicates in the selected field. So for example, just selecting DISTINCT ids from your table, should return:

43
35
12

See if that's what you want.

Share this post


Link to post
Share on other sites
What's wrong with this?
SELECT DISTINCT *, (housenumber+0.00) AS `housenr`
FROM `subscriber`, `phonenumbers`
WHERE `postalcode`='3351 AL'
AND `housenumber`='41'
AND `phonenumbers.id` = `subscriber.id`
AND `phonenumbers.type` = '0'
ORDER BY `postalcode`,`housenr`
LIMIT 25


Does it not work? I've added ` ` around fieldnames and ' ' around the 0 - I know MySQL sometimes gets grumpy with me so I have got into the habit of doing this - is this not returning the correct values? I doubt this is it, but when I've had problems usually adding these helps... Though that's probably just me. [grin]

Share this post


Link to post
Share on other sites
To me, this seems like you've got your table design wrong. It sounds like you need a one-to-many relationship, where 1 ID can have multiple types. It would be a lot easier to do this, if the tables were designed so that the ID/type were grouped.

Alternatively, something like this might be a quicker fix:


ID Type
43 0|6|12|5
...


Then just filter out the IDs in code. Not elegant, but would save you a table redesign.

Share this post


Link to post
Share on other sites
Yeah, a nested query where the subquery returns only the ids that have only type 0:

select id from phonenumbers where id not in (select id from phonenumbers where type <> 0)



That will give you a list of ids that have only type 0. Now you can take this and put it in the main query:

select *, (housenumber+0.00) as housenr from subscriber , phonenumbers
where postalcode='3351 AL' and housenumber='41'
and phonenumbers.id = subscriber.id
AND phonenumbers.id in (select id from phonenumbers where id not in (select id from phonenumbers where type <> 0))
order by postalcode,housenr limit 25



That oughta do it. There might be a simpler way, though, I didn't think this all the way through.

shmoove

Share this post


Link to post
Share on other sites
Quote:
Original post by evolutional
What SQL provider do you use? This sounds like a job for nested queries ^_-


I agree with evolutional here, the normal solution would be a nested IN query (shmoove provided an example). This should be easy to do unless you are using a limited database like MySQL or (well actually I can't think of any other current databases that lack this kind of basic SQL syntax, but rest assured, they exist. Always check first before changing everything)

Share this post


Link to post
Share on other sites
Quote:
Original post by Michalson
Quote:
Original post by evolutional
What SQL provider do you use? This sounds like a job for nested queries ^_-


I agree with evolutional here, the normal solution would be a nested IN query (shmoove provided an example). This should be easy to do unless you are using a limited database like MySQL or (well actually I can't think of any other current databases that lack this kind of basic SQL syntax, but rest assured, they exist. Always check first before changing everything)


Yeah, I agree with that. It's probably worth me pointing out that what I stated earlier about table design, was merely something you should consider in future database designs, not necessarily for this project.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this