How to do the search without nested select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to do the search without nested select

Author

Topic

donar
Starting Member

22 Posts

Posted - 2005-05-04 : 08:44:12

Hi, I have a problem need you guys help.
I have a table:MAP_INFO, this table has 4 fields: isn, map_type, map_subtype, scale. these 4 fields together is the key.

Now I need to the exactly search(with no wild card).
one record example like this:
347 location drillhole location 2400
347 location industrial mineral location 9600
347 location study area location 31680
347 location trench location 2400
if we do OR search, no problem. the question is when I do AND search
like if I want to search for the ISN with Map_subtype ='drillhole location' and map_subtype='trench location'
The only way I can do is a nested search:

select isn from map_info where isn in (
select isn from map_info where map_subtype='drillhole location')
and map_subtype='trench location'

or:
select isn from map_info where map_subtype='drillhole location' or
map_subtype='trench location' group by isn
having count(*)=2

I can not use the first one(nested), but for the second one, I can not gurantee that isn, map_subtype is unique.

What should I do with this type of search?

Thanks a lot!

donar
Starting Member

22 Posts

Posted - 2005-05-04 : 13:59:18

Nobody can help me? Please!

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-05-05 : 01:09:21

Im not exactly sure what you are trying to do here... seems like you should revisit your table design stat! Lots of redundant data!

The query you are grasping for may be something like so:

declare @map_info table (isn int, map_type varchar(10), map_subtype varchar(30), scale int)
insert into @map_info
select 347, 'location', 'drillhole location', 2400 union
select 347, 'location', 'industrial mineral location', 9600 union
select 347, 'location', 'study area location', 31680 union
select 347, 'location', 'trench location', 2400  union
select 346, 'location', 'drillhole location', 2400 union
select 346, 'location', 'industrial mineral location', 9600 union
select 346, 'location', 'study area location', 31680 

select isn 
from @map_info
group by isn
having sum(case when map_subtype in ('drillhole location', 'trench location') then 1 else 0 end) = 2

** Please use the syntax above and repost with clarification and desired resultset.

donar
Starting Member

22 Posts

Posted - 2005-05-05 : 10:29:44

Hi, there, thank you for your help.
Maybe I did not make my question clear enough.
First, the redundant data is not my design problem, since in MAP_TYPE, MAP_SUBTYPE are geological terms, and they are different, I can not put them together. Some records may look like have repeat info, that is the way is.

Second thing, the solution you mentioned above actually has the same result as my second one, it is not a nested search, but since isn, and map_subtype can not be the unique identity. there are possibilities that : like this
, if the record is like this
3905| "geochemical"| "mine level plan"| 360
3905| "geological"| "mine site plan"| 600
3905| "location"| "drillhole location"| 600
3905| "location"| "mine level plan"| 360
3905| "location"| "property location"| 999999
use
select isn from map_info group by isn
having sum(case when map_subtype in ('mine level plan', 'drillhole location') then 1 else 0 end) = 3

sum() has to be 3, you will find this record, but in fact, sum() should equals to 2.

so that cause problem like: if you want to search "mine level plan" and "trench location". the above record actually does not match, but since "mine level plan" appears 2 times in that record, you are going to find this one , which is not correct!

I am not sure whether I make it clear or not.

Thanks for help! and waiting for more solutions!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-05-05 : 11:01:12

The way to make your question crystal clear is to:

post the DDL with sample data (like nathans did - something that will run in a QA window).
Then show examples of criteria you want to search by.
Post the expected results for each set of sample criteria (which records of your sample data should be returned).

Also, I think nathans point about table design refers to the fact that instead having an ISN table and a Map_Type table and putting repeating values of ISN_ID and Map_Type_ID in your map_info table, you are repeating the entire description in all your rows.

Be One with the Optimizer
TG

donar
Starting Member

22 Posts

Posted - 2005-05-05 : 12:25:55

Ok. The Map_info table is like this:

declare @map_info table (isn int, map_type varchar(10), map_subtype varchar(30), scale int)
insert into @map_info
select 3905,'geochemical','mine level plan', 360 union
select 3905,'geological', 'mine site plan' 600 union
select 3905,'location', 'drillhole location',600 union
select 3905,'location', 'mine level plan', 360 union
select 3905,'location', 'property location', 999999

isn, map_type, map_subtype, scale together is the unique identity.

My question is if I want to search for map_subtype='mine level plan' and map_subtype='trench location'. if I use the solutions(similar):
1.select isn
from @map_info
group by isn
having sum(case when map_subtype in ('drillhole location', 'trench location') then 1 else 0 end) = 2
OR:
2. select isn from @map_info where map_subtype='drillhole location' or
map_subtype='trench location' group by isn
having count(*)=2

we will find the above record(isn=3905), but in fact, the above record does not match our search condition.

Except we use the nest search:
select isn from @map_info where isn in (
select isn from @map_info where map_subtype='mine level plan')
and map_subtype='trench location'

Is there any other way to do the search?

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-05-05 : 12:50:23

If you you can commit to only searching with specific # of terms you can break them up in the case:

set nocount on

declare @map_info table (isn int, map_type varchar(15), map_subtype varchar(30), scale int)
insert into @map_info
select 3905,'geochemical','mine level plan', 360 union
select 3905,'geological', 'mine site plan', 600 union
select 3905,'location', 'drillhole location',600 union
select 3905,'location', 'mine level plan', 360 union
select 3905,'location', 'property location', 999999


select isn 
from @map_info
group by isn
having sum(case when map_subtype in ('mine level plan') then 1 else 0 end) > 0
	and sum(case when map_subtype in ('trench location') then 1 else 0 end) > 0

donar
Starting Member

22 Posts

Posted - 2005-05-05 : 12:59:08

Thanks a lot!
I knew that you may come out of this solution just after I replied.

It works good.

Thanks again!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-05-05 : 13:15:15

>>My question is if I want to search for map_subtype='mine level plan' and map_subtype='trench location'
Sorry Donar but I'm confused. Why would you want to do an AND condition for 2 different values in the same column? No record can satisfy that criteria.

Also, make sure the sample code you post actually runs (there are truncation and sytax errors in the table definition and sample data) And after I fixed those erors, all 3 of your queries returns the same thing: 0 records ???

EDIT:
I get 0 records with nathans solution too. Oh well, if you guys are happy, there must be something wrong with me

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources