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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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

3873 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  
 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.08 seconds. Powered By: Snitz Forums 2000