Struggling with SQL join

sql , java , derby Cardiff, United Kingdom
  • 9 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.
  • 9 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!
  • 9 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 ?
  • 9 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. :)
  • 9 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 :)

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it.” - Brian Kernighan