Lookup data from another table

Question

Lookup data from another table

Steve Wilson-461924

Old Hand

Points: 385
More actions
November 5, 2007 at 9:42 am

#176733

Hi Guys,
I have a 3 tables:
stage3 which has this layout:
Stage3ldint
Stage1ldint
Issueldint
IssueCommitmentnvarchar
Leadint
Playerint
ByDatedatetime
Contacts which has this layout:
Autold int
FirstName nchar
LastName nchar
Organisation nvarchar
StakeHCatld int
EmailAdd nvarchar
SiteEntryWord nvarchar
Issues which has this layout:
issueid int
Themeld int
IssuePosId int
IssueDesc varchar
I want to show the name of the Issue (from issues table), Lead (From Contacts Table) and Player (From Contacts Table) using the stage3 table as the main table.
How do I query the database so that the returned data will be something like.
Stage3.Stage3ld, Issues.IssueDesc, Contacts.FirstName, Contacts.LastName, Contacts.FirstName, Contacts.LastName
Thanks for your help.
Best Regards,
Steve Wilson.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

if you have a foreign key reference, it's good practice to use the actual column name from the parent table, instead of an alias.

you never mentioned what the relationships are, so i best guessed them; this might get you started:

SELECT Stage3.Stage3ld, Issues.IssueDesc, Contacts.FirstName, Contacts.LastName, Contacts.FirstName, Contacts.LastName

FROM Stage3

INNER JOIN Issues ON Stage3.Issueld = Issues.Issueld

INNER JOIN Contacts ON Stage3.Player = Contacts.AutoId --<

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Wilson-461924 Old Hand Points: 385 More actions · Answer 2

Hi Lowell,

Thanks for the reply, that worked great.

Only one problem. I need to get the names of the Lead And the PLayer from the Contacts table. I tried:

SELECT tbStage3.Stage3Id, tbIssues.IssueDesc, tbContacts.FirstName, tbContacts.LastName, Con2.FirstName, Con2.LastName

FROM tbStage3

INNER JOIN tbIssues ON tbStage3.IssueId = tbIssues.IssueId

INNER JOIN tbContacts ON tbStage3.Lead = tbContacts.AutoId

INNER JOIN tbContacts Con2 ON tbStage3.Player = tbContacts.AutoId

But No records came back.

Would you know how I can pull out 2 different names from the contacts table.

I really appreciate your help.

Best Regards,

Steve Wilson.

Lowell SSC Guru Points: 323518 More actions · Answer 3

it's just an expansion on the first...you have to join Contacts a second time...to do that, you must ALIAS at least one of the contact table references:

SELECT Stage3.Stage3ld, Issues.IssueDesc, C1.FirstName AS PlayerFirstName, C1.LastName As PlayerLastName, c2.FirstName As LeadFirstName, c2.LastName As LeadLastName

FROM Stage3

INNER JOIN Issues ON Stage3.Issueld = Issues.Issueld

INNER JOIN Contacts C1 ON Stage3.Player = C1.AutoId

INNER JOIN Contacts C2 ON Stage3.Lead= C2.AutoId

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Wilson-461924 Old Hand Points: 385 More actions · Answer 4

Hi Lowell,

Ok I give that a try.

Thanks for all your help.

Best Regards,

Steve Wilson.