Post reply

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

  • 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

  • May 6, 2005 at 9:28 am

    #557389

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

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

  • May 6, 2005 at 9:41 am

    #557391

    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

  • May 6, 2005 at 9:59 am

    #557395

    and what result are you expecting back ?

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

  • May 6, 2005 at 10:01 am

    #557396

    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

  • May 6, 2005 at 10:10 am

    #557399

    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.
    ******************

  • May 6, 2005 at 10:15 am

    #557404

    Dinakar

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

    Brendan

  • May 6, 2005 at 10:29 am

    #557413

    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.

  • May 6, 2005 at 11:20 am

    #557427

    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

     

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

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