February 17, 2010 at 1:32 pm
hello, I m trying to do the following:
1. join table1 with table2 on column1
the result will be matched_records from tab1 and unmatched_records from tab1
then
2. join the matched_records with unmatched_records on column2
note that the performance is very important in this project.
Any help will be much appreciated, thank you
February 17, 2010 at 1:37 pm
It would help if you would provide table defs (CREATE TABLE statements), sample data (series of INSERT INTO statements for the tables), expected results based on the sample data, and what you have done so far to solve your problem.
February 17, 2010 at 1:55 pm
Also state which columns you want to select.
- Only from table1 or from both tables ?
- How can you differentiate both result sets ?
I have a slight impression you are designing a table1 with two foreign keys towards the same table2
I would prefer a extra relationship table stating the type of relationship for each of the foreign keys.
rough example
Table1 ( myId, FatherID int null, MotherId int null , ...)
foreign key FatherID towards table2
foreign key MotherID towards table2
Table2 ( PersonID int primary key, Name varchar(128) not null, Sex char(1), ....)
Select T1.*
, T2.name as ParentName
, 'Father' as ParentType
from table1 T1
inner join table2 T2
on T2.PersonID = T1.FatherID
Select T1.*
, T2.name as ParentName
, 'Mother' as ParentType
from table1 T1
inner join table2 T2
on T2.PersonID = T1.MotherID
-------------------------------
Add a Relationship table
Table3 ( myID -- FK to Table1
, ParentID -- FK to table2
, ParentType -- add relationship info here
, tsregistration datetime default getdate()
, WhoRegistered sysname default suser_sname()
, tsmodification datetime default getdate()
, WhoModified sysname default suser_sname()
)
The new join
Select T1.*
, T2.name
, T3.ParentType
from table1 T1
inner join table3 T3
on T3.myID = T1.myID
inner join table2 T2
on T2.PersonID = T3.ParentID
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 17, 2010 at 2:18 pm
Hi thanks for the reply.
here is more details:
tab1 (postalCode, city,....)
tab2 (postalCode, .....)
step1: join tab1 with tab2 on postalCode and get all columns from tab1 and tab2 (simple join)
step2: take unmatched records from tab1 and join them with matched records on city (this is what I m asking help on it)
thanks
February 17, 2010 at 2:38 pm
SELECT 'step1' AS Step, tab1.*, tab2.*
FROM tab1
INNER JOIN tab2 ON tab2.postalCode = tab1.postalCode
UNION ALL
SELECT 'step2' AS Step, tab1.*, tab2.*
FROM (
SELECT tab1.*
FROM tab1
LEFT JOIN tab2 ON tab2.postalCode = tab1.postalCode
WHERE tab2.postalCode IS NULL) tab1
INNER JOIN tab2 ON tab2.city = tab1.city
Edit: "note that the performance is very important in this project."
Please provide all details which you feel may be relevant.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 18, 2010 at 6:37 am
Thanks ChrisM@home for the reply, but tab2 doesn't have 'city' column.
as mentioned in the step 2, I want to join, on city, records matched in step1 with those didnt match (and all records are from tab1)
thanks
February 18, 2010 at 7:01 am
APIJENA (2/18/2010)
Thanks ChrisM@home for the reply, but tab2 doesn't have 'city' column.as mentioned in the step 2, I want to join, on city, records matched in step1 with those didnt match (and all records are from tab1)
thanks
Please post the table defs (CREATE TABLE statements), sample data (as a series of INSERT INTO statements) that represents the problem you are attempting to solve, expected results based on the sample data.
Help us help you and in return you will get TESTED code instead of shots in the dark.
February 18, 2010 at 7:21 am
APIJENA (2/18/2010)
Thanks ChrisM@home for the reply, but tab2 doesn't have 'city' column.as mentioned in the step 2, I want to join, on city, records matched in step1 with those didnt match (and all records are from tab1)
thanks
Also <<step2: take unmatched records from tab1 and join them with matched records on city (this is what I m asking help on it)
>>
Do you mean, take the (set of) rows from tab1 which don't have a match in tab2, and join them back to tab1 on [city]?
How many rows are in tab1? How many rows do you expect in your output?
If there are 10 rows in tab1 matching exactly 10 rows in tab2, and 10 rows in tab1 which don't match tab2, how many unmatched rows do you expect in your output?
How many rows in tab1 have the same value for city? What's the total rowcount for tab1?
SELECT 'step1' AS Step, tab1.*
FROM tab1
INNER JOIN tab2 ON tab2.postalCode = tab1.postalCode
UNION ALL
SELECT 'step2' AS Step, tab1.*
FROM (
SELECT tab1.*
FROM tab1
LEFT JOIN tab2 ON tab2.postalCode = tab1.postalCode
WHERE tab2.postalCode IS NULL) d
INNER JOIN tab1 ON tab1.city = d.city
Edit: inserted revised code
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply