x

conditional select?

Hi all. I have a select statement for example :

SELECT Columns FROM ServiceRequest
LEFT JOIN ServiceA ON ServiceRequest.ServiceRequestID = ServiceA.ServiceRequestID
LEFT JOIN ServiceB ON ServiceRequest.ServiceRequestID = ServiceB.ServiceRequestID
LEFT JOIN ServiceC ON ServiceRequest.ServiceRequestID = ServiceC.ServiceRequestID
LEFT JOIN ServiceD ON ServiceRequest.ServiceRequestID = ServiceD.ServiceRequestID
LEFT JOIN ServiceE ON ServiceRequest.ServiceRequestID = ServiceE.ServiceRequestID etc etc...
These tables are very wide and i am selecting columns from each one. The problem is the output is massive. How do i get results to display ONLY when there is a value i.e. NOT NULL? Thanks for your help!
more ▼

asked Jun 20, 2012 at 12:32 PM in Default

jhowe gravatar image

jhowe
1.1k 52 57 61

Can you please elaborate it a bit more? I guess we would have a much better understanding with some test data and desired output.
Jun 20, 2012 at 12:39 PM Usman Butt
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

It looks like you need to filter out the rows not columns and your criteria is that you only want to include those rows in the outcome which have some information. For example, suppose for ServiceRequestID = 1 there is data in ServiceD. This is good enough to include the row even though other tables don't have any info. However, if ServiceRequestID = 1 has no values in any of the tables on the right hand side (all except ServiceRequest table itself) then this row should not be included. In this case, one way to go is to add (admittedly nasty looking) predicate which will check all included in the select list columns from ServiceA, ServiceB, ServiceC, ServiceD, and ServiceE and include the row only if at least one column has a useful value. For example, suppose you statement includes all columns from ServiceRequest and 2 columns from each of the tables on the right hand side (I will call then ColA and ColB for the sake of this sample). In this case, you statement will (sadly) look like this:

select
    r.*, a.ColA, a.ColB, b.ColA, b.ColB, c.ColA, c.ColB,
    d.ColA, d.ColB, e.ColA, e.ColB
    from ServiceRequest r left join ServiceA a
        on r.ServiceRequestID = a.ServiceRequestID
    left join ServiceB b
        on r.ServiceRequestID = b.ServiceRequestID
    left join ServiceC c
        on r.ServiceRequestID = c.ServiceRequestID
    left join ServiceD d
        on r.ServiceRequestID = d.ServiceRequestID
    left join ServiceE e 
        on r.ServiceRequestID = e.ServiceRequestID
    where -- this is that nasty part :(
        (a.ColA is not null) or (a.ColB is not null)
        or (b.ColA is not null) or (b.ColB is not null)
        or (c.ColA is not null) or (c.ColB is not null)
        or (d.ColA is not null) or (d.ColB is not null)
        or (e.ColA is not null) or (e.ColB is not null);
go

In case if all columns from tables on the right hand side do have values if you have a match (or you simply want to include them anyway) then you can reduce the ugliness of the predicate a bit by only checking the ServiceRequestID for null. In other words, include the row in the results is there is an actual match in any of the 5 tables on the right hand side:

select
    [YOUR_COLUMNS_LIST]        
    from ServiceRequest r left join ServiceA a
        on r.ServiceRequestID = a.ServiceRequestID
    left join ServiceB b
        on r.ServiceRequestID = b.ServiceRequestID
    left join ServiceC c
        on r.ServiceRequestID = c.ServiceRequestID
    left join ServiceD d
        on r.ServiceRequestID = d.ServiceRequestID
    left join ServiceE e 
        on r.ServiceRequestID = e.ServiceRequestID
    where             
       (a.ServiceRequestID is not null)
        or (b.ServiceRequestID is not null)
        or (c.ServiceRequestID is not null)
        or (d.ServiceRequestID is not null)
        or (e.ServiceRequestID is not null);
go
Oleg
more ▼

answered Jun 20, 2012 at 04:18 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

Yea i was trying to figure out a way of not having a horrdendous WHERE clause but it looks like that is unavoidable...
Jun 20, 2012 at 04:44 PM jhowe
The issue with this is that i will always get one row returned and depending on the table i will still get LOTS of NULL values...
Jun 21, 2012 at 10:14 AM jhowe
@jhowe Yes, of course, but from the way your query is written, it appears that you do need to return the row from the ServiceRequest if there is a matching row for it in any of the 5 other tables, so there is not much you can do about it. The technique already excludes the rows where none of the 5 tables on the right hand side has a match, and this is all you can do unless you also want (on the top of removing the blank rows) to remove the column from the select list in case if none of the rows has a value for this column. If you want to go this route and need some help with it then please drop a line here and I can mock up a sample. The problem with this approach is that the list of columns in your select becomes dependent on the data, and so it has to be dynamically deduced. This is still doable, though I am not sure if it is usable.
Jun 21, 2012 at 02:26 PM Oleg
As the various tables have the same columns i.e. ServiceA.Reason, ServiceB.Reason etc. what if i coalesced them together?
Jun 22, 2012 at 09:12 AM jhowe
Yes that's correct if you have many columns that are the same in different tables coalesce them together which will shorten the output!
Jun 22, 2012 at 10:08 AM jhowe
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1842
x587
x290

asked: Jun 20, 2012 at 12:32 PM

Seen: 1196 times

Last Updated: Jun 22, 2012 at 02:33 PM