SQL Compound Select Problem 2

[jjc3397]

I am trying to pull License Types By County by having the user type the License Type and County in using @Type1, @Type2, @Type3 and @County1, @County2, and @County3 as an example and pull all of the data from the Table for a certain County and Type. I believe that a Select within a Select would be needed, but I do not know how to write the code to do this. I am new to SQL and I am knowledegeable about simple select statements from and where and having, but this is a complex view to set up because when I try to enter parameters in for Types 010, 011, 020. I get all counties. I want the user to select which county they want data for by the type 010, 011, and 020. I other words, I need data for County 01 if the user has entered 01 for County Parameter for the types entered in as @Type1, @Type2, and @Type3 and so on.

Please help.

jjc3397

 

[Qik3Coder]

Select *
from table1
where

'010' in (@Type1, @type2, @type3)

or

'010' in (Select Types from TypesTable where state = 'WA')

or

'010' EXISTS(Select Types from TypesTable where state = 'WA')

-The answer to your problem may not be the answer to your question.

 

[bborissov]

If I understand you correctly you need records from your table where Country = @County1 and Type = @Type1 or Type = @Type1 or Type = @Type3? Did something like this help:

SELECT *
       FROM YourTable
       WHERE Country = @County1 AND 
             Type IN (@Type1, @Type2, @Type3)

?
(here I assumed that all types have values)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

Something like this maybe?

[COLOR=blue]Select[/color] * [COLOR=blue]from[/color] myTable
[COLOR=blue]inner[/color] [COLOR=blue]join[/color]
(
[COLOR=blue]select[/color] @Type1 [COLOR=blue]as[/color] [COLOR=blue]Type[/color]
union all [COLOR=blue]select[/color] @Type2
union all [COLOR=blue]select[/color] @Type3
) dt
[COLOR=blue]on[/color] myTable.Type = dt.Type

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Whoops, not quick enough. Anyway, I mised the county thing too. So, double whoops!

[COLOR=blue]Select[/color] * [COLOR=blue]from[/color] myTable
[COLOR=blue]inner[/color] [COLOR=blue]join[/color]
(
[COLOR=blue]select[/color] @Type1 [COLOR=blue]as[/color] [COLOR=blue]Type[/color]
union all [COLOR=blue]select[/color] @Type2
union all [COLOR=blue]select[/color] @Type3
) dt
[COLOR=blue]on[/color] myTable.Type = dt.Type
[COLOR=blue]inner[/color] [COLOR=blue]join[/color]
(
[COLOR=blue]select[/color] @County1 [COLOR=blue]as[/color] County
union all [COLOR=blue]select[/color] @County2
union all [COLOR=blue]select[/color] @County3
) ct
[COLOR=blue]on[/color] myTable.County = ct.County

HOpe it helps,

Alex



Ignorance of certain subjects is a great part of wisdom

 

[jjc3397]

Borislav,

Yes,

Your coding works but only when I enter say 01 for County. I need the user to also be able to enter in as many counties as they desire such as @County1, @County2, and @County3, @County4 and so on and have the data pulled with types entered in for @type1, @type2, and @type3 and so on. The user can enter as many as 67 counties by 24 License Types. I need data pulled for any combination of types and counties not just one county. Your coding works but it is pulling only data for one county by the types entered in. If I want County2 entered in I want data for types 010, 011, 031 for example to show for Counties 1 and 2.

jjc3397

 

[jjc3397]

Alex,

Your coding works real good. The UNION ALL want bring in a lot of unecessary data will it? Based on what I have read on use of UNION ALL. One has to be very careful. The UNION ALL works real well here though with what I am trying to do. Thanks very much.



jjc3397

 

[AlexCuse]

jjc3397 -

Glad it works for you

The UNION ALL won't bring in any unnecessary data, as it only is selecting your 6 supplied parameters into 2 resultsets that can be joined to other tables.

However, as it seems you are going to allow users to supply many parameters, you may want to have a look at this: faq183-5207

This will show you how you can use a function to split a comma-separated string into a table, which you could then join to. The performance is not great, but if the maximum number of parameters you will allow is greater than 5 or so this is a very viable option.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[bborissov]

jjc3397,
If you post some example data and what you want from that data it would be easier for us to understand what you want.
How many @Country variables you could have? How many @Type variables you could have? Did you have ALL @Type variables defined for every country? etc.
If you post your actual data (not necessary with real data) and what you want from that data, that would be great

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[jjc3397]

Boris,

I appreciate you answering back. I believe Alex above has found the solution to my problem. I appreciate you taking your time to help me. Your solution works too if you want just one county pulled by one to infinity types.

jjc3397