Two foreign keys of a table referring a same primary key

Dev Shed Forums Sponsor:

September 8th, 2007, 05:03 AM

esupport4

Contributing User

Join Date: Aug 2007

Posts: 37

Time spent in forums: 7 h 49 m 17 sec
Reputation Power: 2

Two foreign keys of a table referring a same primary key

i have a table1 having userid(primary key) and username. I have another table2 having senderid, receiverid fields referring to userid of table1. Now i have created a view wherein the condition i have used is:

where table2.senderid = table1.userid
and table2.receiverid = table1.userid.

the problem is this code will give me only those rows where both senderid and receiverid are same.
I have tried using joins also but didn't get proper result.

Can anyone tell me how to get through this?

September 8th, 2007, 06:43 AM

pabloj

Modding: Oracle MsSQL Firebird

Dev Shed God 7th Plane (8000 - 8499 posts)

Join Date: Jun 2001

Location: Outside US

Posts: 8,066

Time spent in forums: 2 Months 4 Weeks 1 h 56 m 57 sec
Reputation Power: 281

This has nothing to do with foreign keys, you need to join two times on the users table using an alias, something like

sql Code:

Original - sql Code
select t2.* from table2 t2 inner join table1 t1 on t2.senderid = t1.userid inner join table1 t_1 on t2.receiverid = t_1.userid;

SELECT
t2.*
FROM table2 t2 INNER JOIN table1 t1 ON t2.senderid = t1.userid
INNER JOIN table1 t_1 ON t2.receiverid = t_1.userid;

I suggest also to use the inner join syntax instead of the where syntax for joins

__________________
My blog
Tutorials about OSS databases, DBMonster ...
Contribute to OSS Development, fill bug reports!
Developer Shed eSupport
Commented my.ini/my.cnf (ADD YOUR OWN CONFIG TRICK)
An introduction to database normalization
Natural or Surrogate key
Custom ordering for your results
Correlated and uncorrelated subqueries
Don't turn your outer joins into inner joins
Random data (with a bias)

September 8th, 2007, 07:34 AM

esupport4

Contributing User

Join Date: Aug 2007

Posts: 37

Time spent in forums: 7 h 49 m 17 sec
Reputation Power: 2

Thank You

Quote:

Originally Posted by pabloj

This has nothing to do with foreign keys, you need to join two times on the users table using an alias, something like

sql Code:

Original - sql Code
select t2.* from table2 t2 inner join table1 t1 on t2.senderid = t1.userid inner join table1 t_1 on t2.receiverid = t_1.userid;

SELECT
t2.*
FROM table2 t2 INNER JOIN table1 t1 ON t2.senderid = t1.userid
INNER JOIN table1 t_1 ON t2.receiverid = t_1.userid;

I suggest also to use the inner join syntax instead of the where syntax for joins

THANKS A LOT. IT'S WORKING VERY WELL..........

Viewing: Dev Shed Forums > Databases > Firebird SQL Development > Two foreign keys of a table referring a same primary key

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump