Search multiple databases and display results on different rows

Question

Aim: I want to query three tables in total and display each line separately.

I only need to display results from TblA and TblF as TblProperty is the parent table so whilst we might search using it I don't need it's data.

i.e. A user might search for a Postcode however a user might only search for a rating in TblA.

I've provided two pieces of code. The first is a cut down version, I think this might help guide both the reader and myself to the solution. The second code is the full version. (I need to add some quotenames etc.. but whilst I'm testing I'm after getting the main part working)

The main point: If I have a one result from TblA and one result from TblF I want two lines of data not one returned.

Using:

SQL Server Management Studio 2012

Query:

I'm looking to get a fresh pair of eyes at this stage. Maybe I need to search both tables first and then the property or look to create a temporary table?

Code 1:

USE DB

DECLARE @QUERY NVARCHAR(MAX)  = ''
DECLARE @QUERYSTRING NVARCHAR(MAX) = ''
DECLARE @sTypeOfUtility  NVARCHAR(MAX) = '2'

SET @QUERY = 
'SELECT 
p.ID AS ID,
p.UPRN AS UPRN, 
COALESCE(a.OverallRiskCategory,''0'') AS RiskType2, 
COALESCE(f.RiskRating,''0'') AS RiskType3, 
COALESCE(a.TypeOfUtility,'''') + COALESCE(f.TypeOfUtility,'''') AS TypeOfUtility
FROM TblProperty AS p'


SET @QUERY = @QUERY + ' INNER JOIN TblA AS a on  a.UPRN  = p.UPRN' 
SET @QUERY = @QUERY + ' INNER JOIN TblFAS f on  f.FIREUPRN =  p.UPRN'

IF @sTypeOfUtility = '2' 
SET @QUERYSTRING =    @QUERYSTRING  + ' AND a.TypeOfUtility LIKE ''%' +  LTRIM(RTRIM(@sTypeOfUtility)) + '%'''

IF @sTypeOfUtility  = '3' 
SET @QUERYSTRING =    @QUERYSTRING  + ' AND f.TypeOfUtility LIKE ''%' +  LTRIM(RTRIM(@sTypeOfUtility)) + '%'''

SET @QUERY = LTRIM(RTRIM(@QUERY)) + ' WHERE   1 = 1 ' +  LTRIM(RTRIM(@QUERYSTRING)) + '   ORDER BY typeofutility DESC'  

EXECUTE(@QUERY)

Code 2 (Fullcode so far but with only two tables):

USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

ALTER PROCEDURE [dbo].[spGridSearch]

@sRiskRating NVARCHAR(50),@sUPRN NVARCHAR(20),
@sPostcode VARCHAR(20), @sPropertyName NVARCHAR(50) ,
@sStreet NVARCHAR(50), @sTypeOfUtility NVARCHAR(10),
@sDateFrom DATETIME, @sDateTo DATETIME 

AS
BEGIN

DECLARE @QUERY NVARCHAR(MAX)  = ''
DECLARE @QUERYSTRING NVARCHAR(MAX) = ''

SET @QUERY = 
'SELECT 
p.ID AS ID,
p.UPRN AS UPRN, 
COALESCE(a.OverallRiskCategory,''0'') AS OverallRiskCategory, 
COALESCE(a.TypeOfUtility,''0'') AS TypeOfUtility, 
COALESCE(a.SurveyDate,'''') AS SurveyDate, COALESCE(a.ItemRef, '''') AS ItemRef, 
COALESCE(a.NextSurveyDue,'''') AS NextSurveyDue ,
COALESCE(a.Recommendations,''NO DATA'') AS Recommendations, 
COALESCE(a.StatusOfIssue,''0'') As StatusOfIssue 
FROM TblProperty AS p '

SET @QUERY = @QUERY + ' LEFT JOIN TblA AS a on  p.UPRN = a.UPRN ' 

IF @sRiskRating <> '1234xyz'
SET @QUERYSTRING =    @QUERYSTRING  + ' AND a.OverallRiskCategory LIKE ''%' +  LTRIM(RTRIM(@sRiskRating)) + '%'''

IF @sTypeOfUtility <> '1234xyz'
SET @QUERYSTRING =    @QUERYSTRING  + ' AND a.TypeOfUtility LIKE ''%' +  LTRIM(RTRIM(@sTypeOfUtility)) + '%'''


--IF @sDateFROM <> '2050-01-01' AND  @sDateTO <> '2050-01-01'
--SET @QUERYSTRING =   @QUERYSTRING  + ' AND a.SurveyDate BETWEEN ' + @sDateFrom + ' AND ' + @sDateTo  

IF @sUPRN <> '1234xyz'
SET @QUERYSTRING =    @QUERYSTRING  + ' AND p.UPRN LIKE ''%' +  LTRIM(RTRIM(@sUPRN)) + '%'''

IF @sPostcode <> '1234xyz'
SET @QUERYSTRING =    @QUERYSTRING  + ' AND p.Postcode LIKE ''%' +  LTRIM(RTRIM(@sPostcode)) + '%'''

IF @sPropertyName <> '1234xyz'
SET @QUERYSTRING =    @QUERYSTRING  + ' AND p.BuildingNo LIKE ''%' +  LTRIM(RTRIM(@sPropertyName)) + '%'''

IF @sStreet <> '1234xyz'
SET @QUERYSTRING =    @QUERYSTRING  + ' AND p.Street LIKE ''%' +  LTRIM(RTRIM(@sStreet)) + '%'''


IF LEN(LTRIM(RTRIM(@QUERYSTRING)))  > 5 
--Remove last as we dont need it
--SET @QUERYSTRING =  LEFT(@QUERYSTRING, NULLIF(LEN(@QUERYSTRING)-1,-1))

SET @QUERY = LTRIM(RTRIM(@QUERY)) + ' WHERE   1 = 1 ' +  LTRIM(RTRIM(@QUERYSTRING))   

EXECUTE(@QUERY) 

END

References: http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a001361784.htm http://www.w3schools.com/sql/sql_join.asp

You want all results from TblA and all results from TblF? Seems like you want to union the results like this http://stackoverflow.com/questions/11956828/sql-server-union-functionality ? — fuchs777, Jan 15 '16 at 13:52
That looks promising, thanks. Friday brain. I'll have a go after food. — indofraiser, Jan 15 '16 at 14:08
@SeanLange I'm only looking to get two tables as results with individual lines, nothing more or less at this time. Once that works, as per notes, I'll tidy and secure the rest. I'll look at the Union but any other thoughts, on that subject I'd e greatful for. — indofraiser, Jan 15 '16 at 14:24
But all of your conditional logic is going to confuse the query optimizer and you will get suboptimal execution plans. And your code is a textbook example of sql injection vulnerability. You desperately need to address the sql injection issue. — Sean Lange, Jan 15 '16 at 14:26
Not disagreeing just getting the first part of the query I had right first :-) Union is the way to go and I'll be updating once I've done that with the rest (as per notes). I'll be back to pick your brains on the second part after it's all updated :-) — indofraiser, Jan 15 '16 at 14:38

score 1 · Accepted Answer · edited May 23 '17 at 10:33

1

As I mentioned in the comments you should use the UNION ALL statement to get all results from both tables. This requires that both select have the same column count and columns should have the same datatype You query would basically look like this:

SELECT 
...
FROM TblProperty AS p
INNER JOIN TblA AS a on  a.UPRN  = p.UPRN

UNION ALL

SELECT 
...
FROM TblProperty AS p
INNER JOIN TblFAS f on  f.FIREUPRN =  p.UPRN

I would also recommend to use use sp_executesql and named parameters like this named parameters in sp_executesql

edited May 23 '17 at 10:33

Community

1
1

answered Jan 15 '16 at 15:18

fuchs777

993
1
14
30

You mentioned UNION but your code contains UNION ALL which are two different things. UNION is putting together all the rows and removes the duplicates while UNION ALL does not remove duplicates. – Mihai-Daniel Virna Jan 15 '16 at 15:21

Search multiple databases and display results on different rows

1 Answers1