id type
-----------
43 0
35 0
43 6
12 0
12 6
[web] SQL: row where field does not appear more than once.
Author
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
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?
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.
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.
What's wrong with this?
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]
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 25Does 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]
Author
The problem with both those solutions is that it still returns the results with ID's 43 and 12. I don't want those: ID's 43 and 12 are of type 0, yes, but -also- of type 6. I want the ID's that are -only- of type 0.
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:
Then just filter out the IDs in code. Not elegant, but would save you a table redesign.
Alternatively, something like this might be a quicker fix:
ID Type43 0|6|12|5...Then just filter out the IDs in code. Not elegant, but would save you a table redesign.
Yeah, a nested query where the subquery returns only the ids that have only 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:
That oughta do it. There might be a simpler way, though, I didn't think this all the way through.
shmoove
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 25That oughta do it. There might be a simpler way, though, I didn't think this all the way through.
shmoove
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)
Quote:Original post by MichalsonQuote: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.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
