Get text from parent table for 2 fields in child table.

Question

Post reply

Get text from parent table for 2 fields in child table.

Brendan Collins

SSC Eights!

Points: 852
More actions
May 6, 2005 at 9:21 am

#164424

Hi
I have a table containing contact details for people in a company (tblContacts). In a second table (tblAgenda) I have two fields ProposedBy and SecondedBy which are both numeric keys linking to the id field (ContactID) of the tblContacts table.
What I need is a SQL statement which will return all the records from the tblAgenda table and instead of the ProposedBy and SecondedBy fields, return the relevant names which are in the FullName field of the tblContacts table.
Anyone get the idea of what I am trying to explain? If so any help would be appreciated as so far the records I am returning are incorrect.
Thanks in advance
Brendan

Viewing 9 posts - 1 through 8 (of 8 total)

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

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 1

can you post some sample data to give us an idea...

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

Brendan Collins SSC Eights! Points: 852 More actions · Answer 2

TblContacts:

ContactID - Primary Key

FullName - text field

Company - text field

Address - text field

tblAgenda

AgendaID - Primary Key

AgendaItem - Text field

ProposedBy - Numeric field linking to ContactID of tblContacts

SecondedBy - Numeric field linking to ContactID of tblContacts

Example data would be:

tblContacts:

ContactID = 1

FullName = "John Doe"

Company = "Acme"

Address = "123 Uphill Road"

ContactID = 2

FullName = "Paul Smith"

Company = "Toys R Us"

Address = "33 MyStreet"

tblAgenda =

AgendaID = 1

AgendaItem = "Make cuppa"

ProposedBy = 1

SecondedBy = 2

Hope this gives a better idea!

Thanks so far.

Brendan

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 3

and what result are you expecting back ?

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

Brendan Collins SSC Eights! Points: 852 More actions · Answer 4

Hi

Instead of the following record being returned:

AgendaID = 1

AgendaItem = "Make cuppa"

ProposedBy = 1

SecondedBy = 2

I would like it to return:

AgendaID = 1

AgendaItem = "Make cuppa"

ProposedBy = "John Doe"

SecondedBy = "Paul Smith"

Thanks again

Brendan

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 5

DECLARE @t1 TABLE ( ContactID INT, FullName VARCHAR(40) )

INSERT INTO @t1 ( contactid, fullname) VALUES (1, 'name1')

INSERT INTO @t1 ( contactid, fullname) VALUES (2, 'name2')

INSERT INTO @t1 ( contactid, fullname) VALUES (3, 'name3')

DECLARE @t2 TABLE (agid INT, pby INT, secby INT)

INSERT INTO @t2 (agid, pby, secby ) VALUES (1,1,2)

INSERT INTO @t2 (agid, pby, secby ) VALUES (2,2,3)

SELECT

agid,

proposedby = (SELECT t1.fullname FROM @t1 t1 WHERE t1.contactid = t2.pby),

secby = (SELECT t1.fullname FROM @t1 t1 WHERE t1.contactid = t2.secby)

FROM

@t2 t2

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

Brendan Collins SSC Eights! Points: 852 More actions · Answer 6

Dinakar

Thanks for all your help. I will give this a try.

Brendan

Farrell Keough SSCoach Points: 17414 More actions · Answer 7

Or you could try the following:

CREATE TABLE #TblContacts( ContactID int,

FullName varchar(25),

Company varchar(25),

Address varchar(25))

CREATE TABLE #TblAgenda( AgendaID int,

AgendaItem varchar(25),

ProposedBy int, -- field linking to ContactID of tblContacts

SecondedBy int) -- field linking to ContactID of tblContacts

INSERT INTO #TblContacts VALUES( 1, 'John Doe', 'Acme', '123 Uphill Road')

INSERT INTO #TblContacts VALUES( 2, 'Paul Smith', 'Toys R Us', '33 My Street')

INSERT INTO #TblAgenda VALUES( 1, 'Make cuppa', 1, 2)

SELECT Agenda.AgendaID, Agenda.AgendaItem, Proposed.FullName, Seconded.FullName

FROM #TblAgenda Agenda

INNER JOIN( SELECT ContactID, FullName FROM #TblContacts

INNER JOIN #TblAgenda ON( ContactID = ProposedBy))

Proposed ON( Agenda.ProposedBy = Proposed.ContactID)

INNER JOIN( SELECT ContactID, FullName FROM #TblContacts

INNER JOIN #TblAgenda ON( ContactID = SecondedBy))

Seconded ON( Agenda.SecondedBy = Seconded.ContactID)

DROP TABLE #TblContacts

DROP TABLE #TblAgenda

I wasn't born stupid - I had to study.

ron k SSCertifiable Points: 6103 More actions · Answer 8

Select a.AgendaId, a.AgendaItem,

c1.FullName as ProposedBy,

c2.FullName as SecondedBy

From tblAgenda a

Join tblContacts c1

On c1.ContactId = a.ProposedBy

Join tblContacts c2

On c2.ContactId = a.SecondedBy