SQL: selective subqueries

Question

Hi,

I'm having an SQL query (MSSQLSERVER) where I add columns to the resultset using subselects:

SELECT P.name, 
(select count(*) from cars C where C.type = 'sports') AS sportscars,
(select count(*) from cars C where C.type = 'family') AS familycars,
(select count(*) from cars C where C.type = 'business') AS businesscars
FROM people P
WHERE P.id = 1;

The query above is just from a test setup that's a bit nonsense, but it serves well enough as example I think. The query I'm actually working on spans a number of complex tables which only distracts from the issue at hand.

In the example above, each record in the table "people" also has three additional columns: "wantsSportscar", "wantsFamilycar" and "wantsBusinesscar". Now what I want to do is only do the subselect of each additional column if the respective "wants....." field in the people table is set to "true". In other words, I only want to do the first subselect if P.wantsSportscar is set to true for that specific person. The second and third subselects should work in a similar manner.

So the way this query should work is that it shows the name of a specific person and the number of models available for the types of cars he wants to own. It might be worth noting that my final resultset will always only contain a single record, namely that of one specific user.

It's important that if a person is not interested in a certain type of cars, that the column for that type will not be included in the final resultset. An example to be sure this is clear:

If person A wants a sportscar and a familycar, the result would include the columns "name", "sportscars" and "familycars".

If person B wants a businesscar, the result would include the columns "name" and "businesscar".

I've been trying to use various combinations with IF, CASE and EXISTS statements, but so far I've not been able to get a syntactically correct solution. Does anyone know if this is even possible? Note that the query will be stored in a Stored Procedure.

Answer 1

I'm mostly an Oracle guy but there's a high chance the same applies. Unless I've misunderstood, what you want is not possible at that level - you will always have a static number of columns. Your query can control if the column is empty but since in the outer-most part of the query you have specified X number of columns, you are guaranteed to get X columns in your resultset.

As I said, I am unfamiliar with MS SQL Server but I'm guessing there will be some way of executing dynamic SQL, in which case you should research that since it should allow you to build a more flexible query.

Answer 2

In your case, there are 8 column layouts possible and to do this, you will need 8 separate queries (or build your query dynamically).

It's not possible to change the resultset layout within a single query.

Instead, you may design your query as follows:

SELECT  P.name, 
        CASE WHEN wantssport = 1 THEN (select count(*) from cars C where C.type = 'sports') ELSE NULL END AS sportscars,
        CASE WHEN wantsfamily = 1 THEN (select count(*) from cars C where C.type = 'family') ELSE NULL END AS familycars,
        CASE WHEN wantsbusiness = 1 THEN (select count(*) from cars C where C.type = 'business') ELSE NULL END AS businesscars
FROM    people P
WHERE   P.id = 1

which will select NULL in appropriate column if a person doesn't want it, and parse these NULL's on client side.

Note that relational model answers the queries in terms of relations.

In your case, the relation is as follows: "this person needs are satisifed with this many sport cars, this many business cars and this many family cars".

Relational model always answers this specific question with a quaternary relation.

It doesn't omit any of the relation members: instead, it just sets them to NULL which is the SQL's way to show that the member of a relation is not defined, applicable or meaningful.

Answer 3

You may be able to do what you want by first selecting the values as separate rows into a temp table, then doing a PIVOT on that table (turning the rows into columns).

Answer 4

It's important that if a person is not interested in a certain type of cars, that the column for that type will not be included in the final resultset. An example to be sure this is clear:

You will not be able to do it in plain SQL. I suggest you just make this column NULL or ZERO.

If you want the query to be dynamically expand when new cars are added, then PIVOTing could help you somewhat.

Answer 5

There are three fundamentals you want to learn to make this work easy. The first is data normalization, the second is GROUP BY, and the third is PIVOT.

First, data normalization. Your design of the people table is not in first normal form. The columns "wantsports", "wantfamily", "wantbusiness" are really a repeating group, although they may not look like one. If you can modify the table design, you will find it advantageous to create a third table, lets call it "peoplewant", with two key columns, personid and cartype. I can go into detail about why this design will be more flexible and powerful if you like, but I'm going to skip that for now.

On to GROUP BY. This allows you to produce a result that summarizes each group in one row of the result.

SELECT 
    p.name, 
    c.type, 
    c.count(*) as carcount
FROM people p, 
   INNER JOIN peoplewant pw ON p.id = pw.personid 
   INNER JOIN cars c on pw.cartype = c.type
WHERE
   p.id = 1
GROUP BY 
   p.name,
   c.type

This (untested) query gives you the result you want, except that the result has a separate row for each car type the person wants.

Finally, PIVOT. The PIVOT tool in your DBMS allows you to turn this result into a form where there is just one row for the person, and there is a separate column for each of the cartypes wanted by that person. I haven't used PIVOT myself, so I'll let somebody else edit this response to provide an example using PIVOT.

If you use the same technique to retrieve data for multiple people in one sweep, keep in mind that a column will appear for each wanted type that any person wants, and zeroes will appear in the PIVOT result for persons who do not want a car type that is in the result columns.

Answer 6

Just came across this post through a google search, so I realize I'm late to this party by a bit, but .. sure this really is possible to do... however, I wouldn't suggest actually doing it this way because it's usually considered a Very Bad Thing (tm).

Dynamic SQL is your answer.

Before I say how to do it, I want to preface this with, Dynamic SQL is a very dangerous thing, if you aren't sanitizing your input from the application.

So, therefore, proceed with caution:

declare @sqlToExecute nvarchar(max);
declare @includeSportsCars bit;
declare @includeFamilyCars bit;
declare @includeBusinessCars bit;

set @includeBusinessCars = 1
set @includeFamilyCars = 1
set @includeSportsCars  = 1

set @sqlToExecute = 'SELECT P.name '

if @includeSportsCars = 1 
    set @sqlToExecute = @sqlToExecute + '(select count(*) from cars C where C.type = ''sports'') AS sportscars, ';
if @includeFamilyCars = 1
    set @sqlToExecute = @sqlToExecute + '(select count(*) from cars C where C.type = ''family'') AS familycars, ';
if @includeBusinessCars = 1
    set @sqlToExecute = @sqlToExecute + '(select count(*) from cars C where C.type = ''business'') AS businesscars '

set @sqlToExecute = @sqlToExecute + ' FROM people P WHERE P.id = 1;';

exec(@sqlToExecute)