Sql help please.

Results 1 to 2 of 2

Thread: Sql help please.

  1. #1
    Join Date
    Dec 1969

    Default Sql help please.

    I have table with company names (companyidentification), people names (person), director table and director board table which shows if directors are also members of other booards.<BR>I wanna return company name, director&#039;s name and also name of the other company on which my director is also a member.<BR>Director board has company id&#039;s. How do I get the last column to be the names of those other companies??? <BR>I cannot use ci.Sname because it will give me director original company and not the other comapny.<BR>Am I clear?<BR><BR>select <BR>ci.sName as companyName,<BR>p.sName as Name,<BR>NAME OF THE COMPNAY FROM DIRECTOR BOARD TABLE<BR><BR><BR>from director d<BR><BR>join companyidentification ci on d.gcompanyid = ci.gcompanyid<BR>join person p on d.gpersonid = p.gpersonid<BR>left outer join directorboard db on d.gDirectorID = db.gDirectorID<BR><BR>where ci.gCompanyid = &#039;XYZ&#039;

  2. #2
    Join Date
    Dec 1969

    Default Show us the table layouts...

    ...it&#039;s hard to tell from your description what the right answer is.<BR><BR>For one thing, I don&#039;t understand the point of having both a DIRECTOR table *and* a DIRECTORBOARD table.<BR><BR>I would think that only 3 tables are needed:<BR><BR>table: Company<BR> gcompanyID : primary key<BR> sName : text<BR><BR>table: Person<BR> gpersonID : primary key<BR> sName : text<BR><BR>table: DirectorCompany<BR> gcompanyID : foreign key to Company table<BR> gpersonID : foreign key to Person table<BR><BR>And then it&#039;s easy:<BR><BR>SELECT c1.sName AS CompanyName, <BR> p.sName AS DirectorName,<BR> c2.sName AS OtherCompanyName<BR>FROM (<BR> ( Company AS c1 INNER JOIN DirectorCompany AS dc1<BR> ON c1.gcompanyID = dc1.gcompanyID )<BR> INNER JOIN Person AS p<BR> ON p.gpersonID = db1.gpersonID <BR> ) LEFT JOIN (<BR> DirectorCompany AS dc2 INNER JOIN Company AS c2<BR> ON dc2.gcompanyID = c2.gcompanyID )<BR> ON ( dc2.gpersonID = dc1.gpersonID<BR> AND dc2.gcompanyID &#060;&#062; dc1.gcompanyID )<BR><BR>See the easy trick? You just have to join to the Many-to-many table twice and thence to the company table twice. <BR><BR>As I said, I don&#039;t see the need for a separate Board and DirectorBoard table. What purpose do they server that one can&#039;t serve???<BR><BR>I might have the parentheses wrong in that messy join (but the concept is right), and in any case Access might find it too complex, but even in Access it could be done by setting up one or two pre-defined (stored) queries.<BR><BR>Incidentally, if your director sits on 3 or more boards, then you will get multiple records for him/her.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts