Struggling with SQL join

• • Reply

  12 years ago

  by griff_the_boarder

  David Griffin

  Cardiff, United Kingdom

  Joined 12 years ago
  Background: I am coding an app in Java / Jython using the JavaDB (aka Derby) embedded database. I have two tables. One has a list of people (name, address, tel) with a primary key called "Ref" In the other I have a list of documents. Each document has (among other things) - a creator (referred to by his/her "Ref") - a recipient (referred to by his/her "Ref") (ie it cross references the People database TWICE) I want to query these two tables and extract a result set that has columns for "creator name" and "recipient name". If I just wanted creator name I would type SELECT PE.Name AS "Creator name" , FROM PeopleTable AS PE , DocumentTable as DO WHERE DO.CreatorRef = PE.Ref AND But now I want both "Creator Name" and "Recipient Name" . Should I join to the PeopleTable twice under two different Aliases ? Thanks in advance.
  Report abuse
• • Reply

  12 years ago

  by James Crowley

  James Crowley

  London, United Kingdom

  Joined 20 years ago
  Hi David, Welcome to developerFusion! Yes, your instinct is exactly right - you need two joins. Something like this: SELECT d.*, c.Name AS "CreatorName", r.Name AS "RecipientName" FROM DocumentTable d INNER JOIN PeopleTable c ON c.Ref=d.CreatorRef INNER JOIN PeopleTable r ON r.Ref=d.RecipientRef Hope that helps!
  Report abuse
• • Reply

  12 years ago

  by griff_the_boarder

  David Griffin

  Cardiff, United Kingdom

  Joined 12 years ago
  Great, thanks. Just out of interest, When I write SELECT d.*, c.Name AS "CreatorName", r.Name AS "RecipientName" FROM DocumentTable d INNER JOIN PeopleTable c ON c.Ref=d.CreatorRef INNER JOIN PeopleTable r ON r.Ref=d.RecipientRef WHERE Is that the same as SELECT d.*, c.Name AS "CreatorName", r.Name AS "RecipientName" FROM DocumentTable d, PeopleTable c , PeopleTable r WHERE c.Ref=d.CreatorRef AND r.Ref=d.RecipientRef AND ? Or is one more correct a strict syntax ?
  Report abuse
• • Reply

  12 years ago

  by James Crowley

  James Crowley

  London, United Kingdom

  Joined 20 years ago
  It's the same thing - it's just a syntactic preference - they'll both ultimately generate the same query plan in the database. I just prefer to keep the join conditions seperate from the "filtering" conditions. :)
  Report abuse
• • Reply

  12 years ago

  by konikula

  Matej Kasper

  Trutnov, Czech Republic

  Joined 15 years ago
  For addition you can also use only single Join with OR condition. But then you must order it by document id, and second by "IsRecipient", and then for every document you recieve two rows, first Recipient, second Sender... but for large data this is not much effective, cause document data are double... for every task
  
  You could also use no Join and Two Union All :)
  Report abuse