question

jhowe avatar image
jhowe asked

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!
sql-server-2008sql-server-2008-r2tsql
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image Usman Butt commented ·
Can you please elaborate it a bit more? I guess we would have a much better understanding with some test data and desired output.
0 Likes 0 ·

1 Answer

·
Oleg avatar image
Oleg answered
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
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jhowe avatar image jhowe commented ·
Yea i was trying to figure out a way of not having a horrdendous WHERE clause but it looks like that is unavoidable...
0 Likes 0 ·
jhowe avatar image jhowe commented ·
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...
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@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.
0 Likes 0 ·
jhowe avatar image jhowe commented ·
As the various tables have the same columns i.e. ServiceA.Reason, ServiceB.Reason etc. what if i coalesced them together?
0 Likes 0 ·
jhowe avatar image jhowe commented ·
Yes that's correct if you have many columns that are the same in different tables coalesce them together which will shorten the output!
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.