December 6, 2007 at 8:53 am
I can,t get this select statement right
i want to pull data from three tables.
One of the columns in two of the tables contain matching data.
I want the rows returned from the three tables that match data from these matching columns
Three tables .....table 1/2/3
table1 column 1 data = lists of fruit
table2 column 1 data = lists of fruit
table3 customers
(Note I have a foreign key relation ship between table 3 and table 1.)
SELECT table1.column1, table2.column1, table3.firstname, table3.lastname, table3.address
FROM table1, table2, table3
WHERE table1.column1 = table2.column1
The WHERE clause does.nt work.
December 6, 2007 at 9:07 am
textman (12/6/2007)
SELECT table1.column1, table2.column1, table3.firstname, table3.lastname, table3.addressFROM table1, table2, table3
WHERE table1.column1 = table2.column1
The WHERE clause does.nt work.
what do you mean by it doesn't work?
from what I see it should return cartesian product of (join of table1 and table 2) and table3. you should add condition comparing fields between table1 and table3 despite foreign key between them.
...and your only reply is slàinte mhath
December 6, 2007 at 9:16 am
Do you mean Inner join
December 6, 2007 at 9:27 am
yes, it depends on your needs, but if you have foreign key I assume that you'd rather have inner join.
btw, syntax you use also represents inner join and
select t1.a, t2.a, t3.a
from t1, t2, t3
where t1.a = t2.a
and t1.a = t3.a
is equivalent to
select t1.a, t2.a, t3.a
from (t1 inner join t3 on t1.a = t3.a) inner join t2 on t1.a = t2.a
you'd better get used to inner join syntax, the other is quite obsolete.
...and your only reply is slàinte mhath
December 6, 2007 at 9:31 am
Thats it Rookie....thanks so much
Really pleased with that
😀
December 6, 2007 at 11:23 am
You really do need to change the query in some fashion to get all three tables joined together
SELECT table1.column1
,table2.column1
,table3.firstname
,table3.lastname
,table3.address
FROM table1
INNER JOIN table2
ON table1.PK = table2.FK
INNER JOIN table3
ON table2.PK = table3.FK
WHERE ?
You have to define the joins between tables that are included in the FROM clause or you end up with cartesian products (everything joined against everything, for each row in table1, return all the rows in table3).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 6, 2007 at 1:47 pm
Hi Grant
Thanks so much reply. I don,t know how to mark the
topic as "sorted".
I started another post and found the answer and it,s
the same as yours using two inner joins.
I,m new here to sql and this forum and I can,t believe how hard
it is trying to describe the tsql problem that you get.
Can you recommend a good reference book on tsql .
Theres loads out there but i wondered if you have a fav
Thanks so much again
Richard
December 7, 2007 at 6:23 am
It's not exactly an introductory book, but my absolute favorite TSQL book is Itzik Ben Gan's Inside T-SQL Querying. It comes from Itzik's course, which is fantastic.
But, for something a bit more introductory, although it's out of date now, Ken Henderson's Guru's Guide to TSQL is very good.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 7, 2007 at 10:37 am
good job ...I,ll amazon them
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply