SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Query - Beginner
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

F4IIP
Starting Member

2 Posts

Posted - 02/23/2013 :  15:14:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 02/23/2013 :  18:01:21  Show Profile  Reply with Quote
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;
Go to Top of Page

F4IIP
Starting Member

2 Posts

Posted - 02/23/2013 :  19:45:36  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000