SQL Query - Beginner - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Query - Beginner

Author

Topic

F4IIP
Starting Member

2 Posts

Posted - 2013-02-23 : 15:14:58

Hey Everyone ,
I am not so good with SQL (just starting) and i am quite stuck with a Query, Perhaps someone can help me.

I have 3 Tables :
1st Table : Actor
2nd Table : Relation_Actor
3rd Table : Relation_Type

Actor :
Actor_ID (key)
Actor_label

>Example of data
Actor_ID Actor Label
1 John
2 Jeff
3 Charles
…

Relation_Actor:
Actor_1 (external key)
Actor_2 (external key)
TR_ID (external key)

>Example of data
Actor_1 Actor_2 TR_ID
1 2 1
2 3 2
…
Relation_Type:
TR_ID (Key)
TR_Label

>Example of data
TR_ID TR_Label
1 IS_Brother
2 IS_Father
….

It is simple probably so basic for you guys.
I would like to extract in a new Table with a query : Actor_1 and Actor_2 and the relation type between them and be able to have the label of Actor_1 and Actor_2 (Actor_label) as well the label for Relation Type (TR_Label).
I just managed so far to get the first part of the query, the list of Actor and their type of relationship

With this Query :
SELECT Relation_Actor.Actor_1, Relation_Actor.Actor_2, Relation_Actor.TR_ID FROM Relation_Actor;

just miss the way to have the label of Actors and Type of Relationship
Thank you in advance
SQL Newbie :)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-23 : 18:01:21

[code]SELECT
a1.Label AS Actor1,
a2.Label AS Actor2,
r.TR_Label AS RelationType
FROM
Relation_Action ra
INNER JOIN Actor a1 ON a1.Actor_ID = ra.Actor_1
INNER JOIN Actor a2 ON a2.Actor_id = ra.Actor_2
INNER JOIN Relation_Type r ON r.TR_ID = ra.TR_ID;[/code]

F4IIP
Starting Member

2 Posts

Posted - 2013-02-23 : 19:45:36

quote:

Originally posted by James K

SELECT
	a1.Label AS Actor1,
	a2.Label AS Actor2,
	r.TR_Label AS RelationType
FROM
	Relation_Action ra
	INNER JOIN Actor a1 ON a1.Actor_ID = ra.Actor_1
	INNER JOIN Actor a2 ON a2.Actor_id = ra.Actor_2
	INNER JOIN Relation_Type r ON r.TR_ID = ra.TR_ID;

Great !!!!!
Thank you James

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources