Bringing several fields from different tables to 1

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Bringing several fields from different tables to 1

Author

Topic

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-07-26 : 04:20:02

Hi,

I've been asked to produce a query where the user wants can type in a family member name and they can find the details of that person. We have a family table, an other family table and a other-significant adults table. How do I name a searchable list of names into 1 field bringing in all family members? They are all linked to a unique familyID number. The user wants to be able to type in a name and it brings up the family they are attached to. I know how to set up the filets but not sure how to search in multiple fields at once.

Thanks,

Jim

Jim

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-26 : 12:19:26

I am not sure exactly what you are facing based on the way you worded your questions. It is always helpful when you ask if you include some basic tables structures and a few rows of data.

I think you might be looking for something like the following: 2 family tables and 1 significant adult tables and you want to search 1 parameter against 2 different family tables and bring back the family as well as adults or using a list to search.

CREATE TABLE #Family1
(
FamilyID Int IDENTITY(1,2),
FamilyName varchar(25)

)

CREATE TABLE #Family2
(
FamilyID Int IDENTITY(2,2),
FamilyName varchar(25)

)

CREATE TABLE #SignificantAdults
(
SugAdultID Int IDENTITY(1,1),
FamilyID Int ,
AdultName varchar(50)

)

INSERT INTO #Family1
VALUES('Smith'),('Jones'),('Thompson')

INSERT INTO #Family2
VALUES('Ray'),('Lee'),('McDougal')

INSERT INTO #SignificantAdults
Values(1 ,'Jane Smith')
,(1 ,'Gerry Smith')
,(1 ,'Kim Smith')
,(3 ,'Frank Jones')
,(3 ,'Tom Jones')
,(5 ,'Scott Thompson')
,(2 ,'Tina Ray')
,(2 ,'Stevie Ray')
,(4 ,'Bruce Lee')
,(4 ,'Tim Lee')
,(6 ,'John McDougal')
,(6 ,'Jane McDougal')

-- Script

DECLARE @Name varchar(50) = 'Ray'

;With JoinFamilyTables
AS
(
SELECT * FROM #Family1
UNION ALL
SELECT * FROM #Family2
)

-- if the 2 family tables are the same, you can merge them together in the CTE and have 1 columns to search on
SELECT J1.FamilyName,J2.SugAdultID,J2.FamilyID,J2.AdultName
FROM JoinFamilyTables J1
INNER JOIN #SignificantAdults J2 ON J1.FamilyID = J2.FamilyID
WHERE J1.FamilyName = @Name

-- if the family tables are different structures, you could do something like this
SELECT ISNULL(j2.FamilyName,j3.FamilyName) FamilyName,J1.*
FROM #SignificantAdults J1
LEFT JOIN #Family1 J2 ON J2.FamilyID = J1.FamilyID
LEFT JOIN #Family2 J3 ON J3.FamilyID = J1.FamilyID
WHERE J2.FamilyName = @Name OR J3.FamilyName = @Name

--if you need to search a list -- you could also just create a proc and pass in a table variable rather than parse/unpack a string : xml is also an option
DECLARE @List varchar(100) = 'RAY,LEE,SMITH,'
DECLARE @FamilyList TABLE(FamilyName Varchar(50)) -- declare a table value

WHILE CHARINDEX(',',@List,1) >0 --- there are many many ways to unpack\parse a string - this is fairly quick and simple for concept purposes
BEGIN
INSERT INTO @FamilyList SELECT SUBSTRING(@List, 1,CHARINDEX(',',@List ,1) -1)
SELECT @List = REPLACE(@list,SUBSTRING(@List,1,CHARINDEX(',',@List,1)),'')
END

;With JoinFamilyTables
AS
(
SELECT * FROM #Family1
UNION ALL
SELECT * FROM #Family2
)

SELECT J1.FamilyName,J2.SugAdultID,J2.FamilyID,J2.AdultName
FROM JoinFamilyTables J1
INNER JOIN #SignificantAdults J2 ON J1.FamilyID = J2.FamilyID
INNER JOIN @FamilyList J3 ON J3.FamilyName = J1.FamilyName

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-07-27 : 11:02:14

Hi Michael,

I'll have a go with some of these tomorrow when back in the office.

Basically, we have 4 tables with different family memebers names. We get criminal record background checks sent back to us and we find it hard to find exactly who the check is for. I was asked to produce a query where they can type who the record check was for and it'll say what the familyIDs available for that name are, to help narrow down the search.

If I can't get your work above to work i'll post back the tables I am using.

Thank you,

Jamie

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources