Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Combine three tables

Posted on 2013-06-19
5
Medium Priority
?
254 Views
Last Modified: 2013-06-19
Hello:

I have three tables that look like this:

TableA

PersonNane
IDCodeAbbv
WorkID
Location
WorkDate

TableB
IDCodeAbbv
IDCodeName

TableC
WorkID
WorkLocation

In TableA if IDCodeAbbv is not null than WorkID will not be in TableC but IDCodeAbbv will be in TableB. If WorkID is not null than WorkID will be in TableC. At no time, will either IDCodeAbbv or WorkID be null at the same time.

This is an example of how the output should look like

PersonName        WorkID      WorkLocation          IDCodeAbbv               IDCodeName
Dan                          2                 Base 1                                                        
Tom                                                                             SR                        South Rear
John                        4                 Base 2
Sam                                                                             NE                        North Entrance
0
Comment
Question by:RecipeDan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 9

Accepted Solution

by:
COANetwork earned 1600 total points
ID: 39260607
SELECT
	A.PersonName,
	C.WorkID,
	C.WorkLocation,
	B.IDCodeAbbv,
	B.IDCodeName
FROM TableA A WITH(NOLOCK)
LEFT JOIN TableB B WITH(NOLOCK) ON A.IDCodeAbbv = B.IDCodeAbbv
LEFT JOIN TableC C WITH(NOLOCK) ON A.WorkID = C.WorkID

Open in new window

0
 
LVL 8

Assisted Solution

by:didnthaveaname
didnthaveaname earned 400 total points
ID: 39260626
I came up with the following (close to the same as COANetwork's):

select
	A.personName,
	coalesce( B.IDCodeAbbv, '' ) as IDCodeAbbv,
	coalesce( B.IDCodeName, '' ) as IDCodeName,
	coalesce( C.workID, '' ) as workID,
	coalesce( C.workLocation, '' ) as workLocation
from
	tableA as A
		left join tableB as B on A.IDCodeAbbv = B.IDCodeAbbv
		left join tableC as C on A.workID = C.workID;

Open in new window


Pretty much aesthetics, but you your sample output didn't have NULLs in it, so...
0
 
LVL 1

Author Comment

by:RecipeDan
ID: 39260909
Sorry about this...I did not create the database, it was handed to me.

After giviing the data a closer look. In TableA if IDCodeAbbv is not null than WorkID will not be in TableC but IDCodeAbbv will be in TableB. However, when IDCodeAbbv is not null WorkID is given a value of -1
0
 
LVL 9

Expert Comment

by:COANetwork
ID: 39260945
it does not matter, as long as there is no corresponding WorkID = -1 in TableC.  Both mine and didnthaveaname's sampels will work - mine will produce NULL where there is no data, his will produce an empty string.  The actual values of your keys do not matter, what matters is the presence or absence of SAME values in the corresponding tables.
0
 
LVL 1

Author Closing Comment

by:RecipeDan
ID: 39261116
Thank you both for your help
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question