Combining Multiple Queries into a single Query 4

[FoxProProgrammer]

Is there a way to combine the following three queries into a single query? It works the way it is; I was just curious if there's a better way.

Query 1: SELECT fldlist FROM tblname WHERE somefld = "X"

Query 2: SELECT fldlist FROM tblname WHERE somefld = "Y"

Query 3: SELECT otherflds FROM tblname

Query 4: SELECT [Query 1].*, [Query 2].*, [Query 3].* FROM [Query 1], [Query 2], [Query 3];

All four queries return one record. The fields contain a bunch of aggregate functions from a large table. The reason that I broke it up into three queries is because I didn't know how to specify more than one Where clause in the same query. Note that fldlist contains the same fields for Query 1 and Query 2. The only difference is that I want to retrieve certain records based on the contents of another field (i.e. somefld equal X or Y). Is there a better way to do this?

Thanks a lot,


dz
dzaccess@yahoo.com

 

[SkipVought]

dz,

 SELECT fldlist, '','' FROM tblname WHERE somefld = "X" OR somefld = "Y"
Union
 Select '','','', otherflds FROM tblname

where fldlist has 3 fields and otherfields has 2 fields.

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[FoxProProgrammer]

Skip,

Thanks for your reply. I must not have explained the issue well. Hopefully this is a better explanation.

Let's say that a table contains a field named "Location", the contents of which could be "A" or "B". The user wants to calculate numerous aggregate functions (Max, Min, Count, etc.) on various fields in the table where Location = "A" and Location = "B". For example, if there are fields named "Type1", "Type2", "Type3", etc, the user wants to calculate Count(Type1) only for Location A, Count(Type1) only for Location B, Max(Type2) only for Location A, Min(Type3) only for Location B, etc.

Before I came up with the four queries in my first post, the user calculated a field in the query for each of the fields that he wanted to aggregate. For example, his query included fields such as:

IIf(Location = "A", 1) As LocA
IIf(Location = "B", 1) As LocB

Then he would multiply the number of each type by the value of LocA and LocB in another calculated field in the Query:

(Type1 * LocA) As Type1LocA
(Type1 * LocB) As Type1LocB
(Type2 * LocA) As Type2LocA
(Type2 * LocB) As Type2LocB

.
.
.
etc.

Then he would calculate another field for the aggregates:

Count(Type1LocA) As TotalType1LocA
Count(Type1LocB) As TotalType1LocB
Count(Type2LocA) As TotalType2LocA
Count(Type2LocB) As TotalType2LocB

etc.

Each record in the original table is for one Type at one Location. Needless to say, the query output contained a lot of Null values and fields that were only used as an intermediate step to calculate aggregate functions. For example, if a record represented Type1 at LocA, the LocB field was Null, as were any fields that were multiplied by LocB (Type1LocB, Type2LocB, etc.) It is a clever method, but probably not very clean for a database structure. That's why I came up with the four queries in my first post.

My four queries eliminate all the IIf functions that store 1 or Null in the calculated field, and the fields that multiply by that value.

Query1: Select Type1, Type2, etc From tbl Where Loc = A;

Query2: Select Type1, Type2, etc From tbl Where Loc = B;

Query3: Select otherflds from tbl; (these aren't dependant on the location)

Then the last query calculates the aggregate functions and brings the three queries together in a single output.

Select Count(Query1.Type1) As TotalType1LocA, Count(Query1.Type2) As TotalType2LocA, Count(Query2.Type1) As TotalType1LocB...

I already suggested that he restructure his tables, but he wants a flat file rather than a relational database. This is probably more information than you wanted <grin>, but with this background, is there a better way to structure my queries or is that the best way to do it?

Thanks,


dz
dzaccess@yahoo.com

 

[lespaul]

Select Type1, Type2, etc, 'A Values' As TypeofData From tbl Where Loc = A
UNION
Select Type1, Type2, etc, 'B Values' From tbl Where Loc = B
UNION
Select Type1, Type2, etc, 'Others' From tbl

This will work as long as there are the same number of fields selected in each query. You can "plug" fields if needed:

Select Type1, Type2, '' As Somethingelse, etc, 'A Values' As TypeofData From tbl Where Loc = A
UNION
Select Type1, Type2, '', etc, 'B Values' From tbl Where Loc = B
UNION
Select Type1, Type2, Type3, etc, 'Others' From tbl

In this example, the first two records will have '' in Somethingelse field, but the third row will have the information from field Type3





Leslie

 

[FoxProProgrammer]

Leslie,

I tried a Union yesterday, and got three records instead of one record with all the fields. I'll look at what I did when I get to work later today, and try your suggestion. Maybe I did something different, but my recollection is that it's the same.

Thanks,


dz
dzaccess@yahoo.com

 

[FoxProProgrammer]

Leslie,

I tried a UNION yesterday, and got multiple records instead of one record with all the fields. I'll look at what I did when I get to work later today, and try your suggestion. Maybe I did something different, but my recollection is that it's the same.

Thanks,


dz
dzaccess@yahoo.com

 

[lespaul]

That's right, you will get one record with three fields. Why is it so important to have a single record output?

Leslie

 

[FoxProProgrammer]

I don't own this database. I am simply helping a colleague and that's the way he wants it. He is exporting the data to Excel and wants all the data in one record.

dz
dzaccess@yahoo.com

 

[lespaul]

In that case, I'm going to say that the way you are currently doing it is probably the best way.

leslie

 

[FoxProProgrammer]

Thank you, Leslie & Skip. I sincerely appreciate you all taking so much time to read this issue and giving me an opinion.

Have a nice day,


dz
dzaccess@yahoo.com

 

[PHV]

Have you tried this ?
SELECT X.*, Y.*, O.*
FROM (SELECT fldlist FROM tblname WHERE somefld = "X") As X
, (SELECT fldlist FROM tblname WHERE somefld = "Y") As Y
, (SELECT otherflds FROM tblname) As O
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[FoxProProgrammer]

Wow, it works! Thanks, PHV. You're the master. I can see why your name is at the top of the MVP list. lol

If you can solve the issue discussed at thread701-923574 , I'll vote for you to be Top Dog of the entire Tek-Tips website. )

Take care,

dz
dzaccess@yahoo.com

 

[vanleurth]

Hey SQL eXperts

Hello and good Monday, I mean morning.

Here is my proble. I'm trying to add to values to a sql query by using UNION. For example

SELECT '(All)', '(Personal)' as bogus from [tbl phones]
UNION
SELECT * FROM [tbl phones]

It doesn't work. I know how to do this with just one value, for exmaple '(All)'

Thanks in advance,

V.

 

[lespaul]

there has to be the same number of selected fields in all parts of the union query. So if tbl PHones has more than the two fields that were selected in the first query, this won't work.

What are you trying to accomplish with this (Please answer in a new thread with all the details we will need to help you).

Leslie

 

[vanleurth]

Thanks for your prompt response. I figured out a way to do it with multiple unions (onions, like that french soup !). Here is the solution;

SELECT '(All)' as phgroup FROM [tbl phones]
UNION
SELECT '(Personal)' as phgroup FROM [tbl phones]
UNION
SELECT PhGroup as phgroup FROM [tbl phones]

Thanks anyway,

V.