September 28, 2010 at 4:06 am
Hi folks,
I am currently building a query that involves multiple inner joins between two tables.
Table A contains seven foreign key fields that reference the primary key of Table B.
Table B contains data that I need to return in my result set.
As it stands, my SQL will have to look something like..
SELECT join1.Field1,
join2.Field1,
join3.Field1,
join4.Field1,
join5.Field1,
join6.Field1,
join7.Field1
FROM tableA
INNER JOIN tableB as join1
ON tableA.FK1 = join1.PK
INNER JOIN tableB as join2
ON tableA.FK2 = join2.PK
INNER JOIN tableB as join3
ON tableA.FK3 = join3.PK
INNER JOIN tableB as join4
ON tableA.FK4 = join4.PK
INNER JOIN tableB as join5
ON tableA.FK5 = join5.PK
INNER JOIN tableB as join6
ON tableA.FK6 = join6.PK
INNER JOIN tableB as join7
ON tableA.FK7 = join7.PK
Whilst I am confident that this will work, I was wondering if there is a more efficient way in which I could get the values I need from Table B without having to join to it seven times?
TIA,
Chris
September 28, 2010 at 4:36 am
If the seven foreign key fields from any row of Table A reference seven different rows in Table B, then yes you will need to join Table B seven times with seven different aliases. Looks like your Table B is a catch-all lookup.
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
September 28, 2010 at 4:44 am
Hi chris.king..
I think there is no another way.Joining tables will not decrease the Performance.
Regards
varun R
September 28, 2010 at 5:54 am
Chris Morris-439714 (9/28/2010)
If the seven foreign key fields from any row of Table A reference seven different rows in Table B, then yes you will need to join Table B seven times with seven different aliases. Looks like your Table B is a catch-all lookup.
The fields in Table A all reference the same field in Table B, the primary key.
Does that make a difference.
I'm really struggling to see how I can do this without seven seperate joins.
TIA,
Chris
September 28, 2010 at 6:05 am
Hi Chris,
If your table A only has the FK1-FK7 columns and Table b has the PKey and definition, than that is the only way.If not, please post your table structures.
Apart from a stored proc which might run a bit faster because of the caching. Your table structure is normalized and you need a denormalized view.
cheers,
September 28, 2010 at 6:13 am
chris.king (9/28/2010)
Chris Morris-439714 (9/28/2010)
If the seven foreign key fields from any row of Table A reference seven different rows in Table B, then yes you will need to join Table B seven times with seven different aliases. Looks like your Table B is a catch-all lookup.The fields in Table A all reference the same field in Table B, the primary key.
Does that make a difference.
I'm really struggling to see how I can do this without seven seperate joins.
TIA,
Chris
You can use trickery to get around it, but to be honest Chris, this looks correct. I don't think you are doing anything wrong (based upon what you've posted so far). You might want to check that you do in fact need inner joins between your main table and your lookup.
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
September 28, 2010 at 6:26 am
Thanks guys.
I guess I'll have to live with the seven joins.
The only other option would be to de-normalise the data from Table B into Table A.
Chris
September 28, 2010 at 11:28 am
chris.king (9/28/2010)
Thanks guys.I guess I'll have to live with the seven joins.
The only other option would be to de-normalise the data from Table B into Table A.
Chris
If the data from Table B is small, needed regularly, and rarely modified, this isn't necessarily a bad idea. Just make sure your update scripts touch both locations.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply