May 7, 2007 at 3:35 pm
I want to join two tables
Table1
Employeename Site1
John NY
John NJ
John CT
Table2
Employeename Site2
John TX
John FL
How do I get the result like
Employeename Site1 Site2
John NY TX
John NJ FL
John CT NULL
THanks
Joel
Joel
May 7, 2007 at 3:51 pm
Well, first you would need a key in both tables to link those peices of information together. From what I can gather, these would have foreign keys from a table such as "Employee_Info". Like so:
Employee_id Employeename Site1
1 John NY
2 John NJ
3 John CT
Employee_id Employeename Site2
1 John TX
2 John FL
If this was the case, you would do a left outer join on the two tables.
SELECT T1.Employee, T1.Site1, T2.Site2
FROM Table1 T1 LEFT OUTER JOIN Table2 T2 ON T1.Employee_id = T2.Employee_id
The reason for the outer join is to make sure that when joining the tables, you are including all rows from the left table, even when there is no matching row in the right table.
If I was wrong and there is no foreign key to join the tables on, then the dataset you want returned is not possible from my knowledge.
Hope this helps!
May 7, 2007 at 4:31 pm
Shawn, I think he is holding out on the extent of the table. There is probably multiple names, in which case makes the ID field not useful (at least directly).
Next there needs to be an assumption made that the names are grouped together and in order of significance... of course the order may not be important.
This turns into:
SELECT
empName,
site1,
(
SELECT site2
FROM Sites2 S2_1
WHERE S2_1.empName = S1_1.empName
AND S2_1.ID =
(
SELECT
MIN(S2_2.ID)
+(
SELECT S1_1.ID
- MIN(S1_2.ID)
FROM Site1 S1_2
WHERE S1_2.empName = S1_1.empName
)
FROM Site2 S2_2
)
) AS site2
FROM Site1 S1_1
This also assumes there's always more site1's than site2's
May 7, 2007 at 6:07 pm
kevin..thanks for your response..but there is no id column..
Joel
May 7, 2007 at 6:16 pm
So, why it should be
John NY TX
John NJ FL
John CT NULL
but not
John NY NULL
John NJ TX
John CT FL
_____________
Code for TallyGenerator
May 8, 2007 at 7:49 am
There are 2 options for you...
#1 Add an ID field to each table
or #2 Create temp tables when you run the query and insert the data. You can search this site or BOL for that.
May 8, 2007 at 7:57 am
May 8, 2007 at 8:01 am
It can also be in the format Sergiy suggests...
Joel
May 8, 2007 at 8:02 am
As I stated above: "Next there needs to be an assumption made that the names are grouped together and in order of significance... of course the order may not be important."
with the query I made (btw I never tested it) it filters Sites2 by the corresponding name in Sites1 then it looks for the MIN(ID) of Sites2 + the position of the current site in Sites1. If there is no corresponding ID, it returns null.
May 8, 2007 at 8:08 am
Joel, my guess is this query result was not your idea. You may want to consider, using a UNION, this format.
John Site 1 NY
John Site 1 CT
John Site 2 TX
Where " 'Site 1' AS Site " is a custom field.
May 8, 2007 at 11:30 am
I see what you are saying about the multiple names in the table, I feel like we are not seeing the entire extent of the data/design, or the design of these tables is a bit lacking.
@All
If there is no ID to associate between the two tables, and the name field in each table is not a unique identifier, then this doesn't seem possible (in the result set asked for originally). I would go back and look at my design aas a first step. Some definitive way to relate and uniquely identify employee names between the two tables is what needs to happen in this situation.
May 8, 2007 at 6:15 pm
Sounds like "Whatever"
Do you have any idea what kind of resultset you're trying to retrieve?
Can you explain it in plain English?
_____________
Code for TallyGenerator
May 9, 2007 at 8:42 am
if he can't add the field he needs to use temp tables
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply