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

Answer 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