October 25, 2010 at 2:46 pm
Dear All,
Please can anyone offer some advice regarding the situation below as to whether it is "acceptable" or a complete programming "no, no!!".
Basically I have three tables:-
Table1 has 3 fields (field1, field2, field3)
Table2 has 3 fields (field1, field2, field4)
Table3 has 3 fields (field3, field4, field5)
The attached diagram should make things easier to understand(providing I attached it properly!!)
As I understand it, a join is a "partnership" between two tables but in the example I have shown, I have completed the join to Table3 by using 1 field from table1 (field3) and 1 field from table2 (field4)
Is this OK? What is the SQL to achieve this?
Looking forward in anticipation to any replies.
Thank you for looking.
Pete
October 25, 2010 at 2:53 pm
Yeah, that's totally within the capabilities of SQL. It's pretty usual, in fact, since it's the basic structure of many-to-many joins.
Are you familiar with writing join statements connecting two tables? If so, just add another line to the "ON" portion that describes the relationship between the extra columns.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 25, 2010 at 3:42 pm
GSquared
Thank you for your prompt reply.
I am familiar with the "ON" clause but cant see how it relates to three tables
i.e. Using my example
select * from tableA
left outer join (say)
tableB
on
tableA.field1=tableB.field1 and
tableA.field2=tableB.field2
left outer join (say)
TableC
on
TableA.field3 = tableC.field3
TableB.field4 = tableC.field4
Is this correct? I cant see how this relates to two tables anymore?
Thank you for your help.
Regards,
Pete
October 25, 2010 at 3:54 pm
pete_bristol (10/25/2010)
GSquaredThank you for your prompt reply.
I am familiar with the "ON" clause but cant see how it relates to three tables
i.e. Using my example
select * from tableA
left outer join (say)
tableB
on
tableA.field1=tableB.field1 and
tableA.field2=tableB.field2
left outer join (say)
TableC
on
TableA.field3 = tableC.field3
TableB.field4 = tableC.field4
Is this correct? I cant see how this relates to two tables anymore?
Thank you for your help.
Regards,
Pete
Well, remove the "(say)", as you probably know, then you're missing an "AND" before "TableB.field4 = tableC.field4". I think you may be ok after that.
FYI, the "SELECT *" will return all the columns from all 3 tables.
I don't understand what you mean by:
I cant see how this relates to two tables anymore?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 26, 2010 at 12:58 am
pete_bristol (10/25/2010)
Dear All,Basically I have three tables:-
Table1 has 3 fields (field1, field2, field3)
Table2 has 3 fields (field1, field2, field4)
Table3 has 3 fields (field3, field4, field5)
As I understand it, a join is a "partnership" between two tables but in the example I have shown, I have completed the join to Table3 by using 1 field from table1 (field3) and 1 field from table2 (field4)
Hi Bristol,
select * from table3 t3 inner join table1 t1
on t3.field3 = t1.field3
inner join table2 t2
on t2.field4 = t3.field4
you can REPLACE * with any columns you want in select query
October 26, 2010 at 3:56 am
fawwad, Alvin
Thanks to you both for your replies.
fawwad,
Referring to your post for a minute. The three tables I posted were obviously a simplified example of my real-life issue but the SQL you provided has made me think about my problem in a different light (and get even more confused in the process!)
My proposal had (more-or-less) Table1 joining to Table2 and then a field from each joining Table3.
You put it the other way round, starting with Table3 joining Table2 and also Table1. Each join being a relationship between two tables and not three as per my example i.e. Table1(part) + Table2(part) = Table3. I know I keep mentioning it but I have read on so many occassions that a "join is only ever a relationship between TWO tables". I hope you get what I am trying to say but its so difficult to explain in writing as oppossed to a verbal conversation!
Anyhow that said, would your SQL and my SQL return the same result? What if there were other joins to/from Tables 1,2,3 that made it such that you couldn't tip the query as you have done? Perhaps the query optimiser figures it all out and generates the same code?
The thing I am most confused about in my SQL is where table 3 is being joined partly by Table1 and Table2 fields, what happens when there are more records in one of those tables than the other?
I think I'm off to experiment....
Pete
October 26, 2010 at 7:44 am
Joins can connect as many tables as you like (up to about 32,000 if I remember correctly). The connections can be as simple or complex as you like. They are often described as connections between two tables, but that's just simplified to make the sentence easier to read in the book that says it.
On the subject of 3,1,2 vs 1,2,3, the results will be the same if they are all Inner Joins. If they are Outer Joins, the results may be different, depending on the actual contents of the tables.
On the subject of more or less rows in one or another table, the results will depend on the data in the tables, and whether you use Inner or Outer Joins.
Copy this into Management Studio and run it, and it'll probably help you see how this works:
IF OBJECT_ID(N'tempdb..#T1') IS NOT NULL
DROP TABLE #T1;
IF OBJECT_ID(N'tempdb..#T2') IS NOT NULL
DROP TABLE #T2;
IF OBJECT_ID(N'tempdb..#T3') IS NOT NULL
DROP TABLE #T3;
CREATE TABLE #T1 (
C1 INT,
C2 INT,
C3 INT);
CREATE TABLE #T2 (
C4 INT,
C5 INT,
C6 INT);
CREATE TABLE #T3 (
C7 INT,
C8 INT,
C9 INT);
INSERT INTO #T1 (C1, C2, C3)
SELECT 1, 2, 3 UNION ALL
SELECT 1, 4, 5;
INSERT INTO #T2
( C4, C5, C6 )
SELECT 6, 2, 7 UNION ALL
SELECT 8, 9, 10;
INSERT INTO #T3
( C7, C8, C9 )
SELECT 3, 7, 11 UNION ALL
SELECT 12, 13, 14;
SELECT *
FROM #T1;
SELECT *
FROM #T2;
SELECT *
FROM #T3;
SELECT *
FROM #T1 AS T1
INNER JOIN #T2 AS T2
ON T1.C2 = T2.C5
INNER JOIN #T3 AS T3
ON T1.C3 = T3.C7
AND T2.C6 = T3.C8;
SELECT *
FROM #T1 AS T1
LEFT OUTER JOIN #T2 AS T2
ON T1.C2 = T2.C5
LEFT OUTER JOIN #T3 AS T3
ON T1.C3 = T3.C7
AND T2.C6 = T3.C8;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 27, 2010 at 1:20 am
Gsquared,
Thank you very much, that helped my understanding tremendously.
Regards,
Pete
October 27, 2010 at 6:18 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply