3 table join

[gertie2008]

Hi,

I've got 3 tables:
1.) book: BookID, Title
2.) bookcomment: BookCommentID, BookID, ContactID, Comment
3.) responsibilitylog: ResponsibilityID, BookID, ContactID, DataFrom, DateTo


Relationships:
book and bookcomment: one to one
book and responsibilitylog: one to many

I want a query that returns book.Title and bookcomment.Comment WHERE BookID=1 AND ContactID=2 regardless if responsibilitylog returns a record,
AND
the query must return all the records with fields book.Title, responsibilitylog.DataFrom and responsibilitylog.DateTo WHERE BookID=1 AND ContactID=2 regardless if bookcomment returns a record

The following query only returns data if there is a record in bookcomment AND responsibilitylog that meet the conditions because of my AND statement, but I don't know how to rectify this. If I use OR in place of AND, I do not get the desired result.

rs = st.executeQuery("select b.Title, rl.DateFrom, rl.DateTo, c.Comment from book b left join responsibilitylog rl on b.BookID = rl.BookID left join bookcomment c on c.BookID = b.BookID WHERE rl.ContactID='" + ContactID + "' AND c.ContactID='" + ContactID + "'");
 

[code green]

regardless if bookcomment returns a record

Then it needs to be LEFT JOIN

[gertie2008]

Thanks for your response,

I changed it to left join, but still:

The query only returns data if there is a record in bookcomment AND responsibilitylog that meet the conditions. I have data in responsibilitylog table that should be returned, but for that ContactID and BookID, there is no record in the bookcomment table and therefore it doesn't return the data from the responsibilitylog table.

[henryrhenryr]

rs = st.executeQuery("select b.Title, rl.DateFrom, rl.DateTo, c.Comment from book b left join responsibilitylog rl on b.BookID = rl.BookID left join bookcomment c on c.BookID = b.BookID WHERE rl.ContactID='" + ContactID + "' AND c.ContactID='" + ContactID + "'");
 

At a glance I think you can work around either:

1. Input default settings - when you add a book to your DB, always add records for comment and responsibility, but add default/null rows. Then you don't have the problem.

2. Probably preferable, I think you should be able to use AND (... OR ...)

select b.Title, rl.DateFrom, rl.DateTo, c.Comment 
from book b 
  left join responsibilitylog rl on b.BookID = rl.BookID 
  left join bookcomment c on c.BookID = b.BookID 
WHERE (rl.ContactID='"+CONTACTID+"' OR rl.ContactID IS NULL)
   AND (c.ContactID='"+CONTACTID+"' OR c.ContactID IS NULL)
 

or perhaps you're looking for this...

...
WHERE (rl.ContactID='"+CONTACTID+"' AND c.ContactID='"+CONTACTID+"') 
   OR (rl.ContactID IS NULL AND c.ContactID IS NULL)
 

Haven't tested though... ;)

Henry

[gertie2008]

Thanks for you input Henry, you've given me some ideas to try out. I have tried various options concerning AND OR. The following example comes pretty close to what I want, except that it returns data from the bookcomment table where the ContactID is not equal to the one that I've specified. I suspect it is because of the way that the joins are made:

select b.BookID, b.Title, b.Author, rl.DateFrom, rl.DateTo, c.Comment from book b left join responsibilitylog rl on b.BookID = rl.BookID left join bookcomment c on c.BookID = b.BookID WHERE (rl.ContactID='"+ContactID+"' OR rl.ContactID IS NULL) OR (c.ContactID='"+ContactID+"' AND c.BookID='"+BookID+"')

I'm basically trying to combine the following 2 queries:
1.)

"SELECT b.BookID, b.Title, b.Author, rl.DateFrom, rl.DateTo FROM book b LEFT JOIN responsibilitylog rl ON b.BookID=rl.BookID WHERE rl.ContactID=" + ContactID + " ORDER BY Title"

2.)

"SELECT b.BookID, b.Title, b.Author, c.Comment FROM book b LEFT JOIN bookcomment c ON b.BookID=c.BookID WHERE c.ContactID=" + ContactID + " ORDER BY Title"

[code green]

The following example comes pretty close to what I want, except that it returns data from the bookcomment table where the ContactID is not equal to the one that I've specified. I suspect it is because of the way that the joins are made:

Maybe your second LEFT JOIN needs to be an INNER JOIN.

INNER JOIN bookcomment c ON c.BookID = b.BookID 

You can try putting WHERE conditions inside the ON clause. Not sure where you are going with this but I mean

JOIN bookcomment c ON (c.BookID = b.BookID AND c.BookID='"+BookID+"')

[gertie2008]

Thanks for your help code green ,

I am one step closer with the following:

"select b.BookID, b.Title, b.Author, rl.DateFrom, rl.DateTo, c.Comment from book b left join responsibilitylog rl on b.BookID = rl.BookID left join bookcomment c on (b.BookID = c.BookID AND c.ContactID='"+ContactID+"') WHERE (rl.ContactID='"+ContactID+"' OR rl.ContactID IS NULL) OR (c.ContactID='"+ContactID+"' AND c.BookID='"+BookID+"')"
 

[coolsti]

I am just glancing at this thread without spending the time to solve the problem, but I do not think it is very difficult to do (maybe I have time later and will repost).

But here a comment: The Book and Bookcomment table are 1 to 1 and, indeed, could actually be combined to form one database table. Therefore I would do the query by joining these two tables first, and then doing a left join on the Responsibility table.

In all the examples I have seen so far, you have first joined the Book and Responsibility table, and then joined the Bookcomment table.

[coolsti]

Try this, it works for me (although I used different table definitions, so if you get a syntax error with what I show below, you may need to modify to fix it).

select b.BookID, b.Title, b.Author, rl.DateFrom, rl.DateTo, c.Comment
from book b left join bookcomment c on b.BookID=c.BookID
left join responsibilitylog rl on b.BookID=rl.BookID
having b.BookID=1 and
((c.ContactID=2 or c.ContactID is NULL) and
(rl.ContactID=2 or rl.ContactID is NULL))

First try this without the having clause. You will see that you get a join of all possibilities: all book ID's and their associated rows in the two other tables. The HAVING clause is then used to filter out the ones you don't wish. The filter is of course over the book ID, and then two other conditions:

1) either the bookcomment ContactID is the value you are looking for or it is NULL (the row is not present for this book in the bookcomment table)

2) either the responsibilitylog ContactID is the value you are looking for, or is NULL (the row is not present for this book in the responsibilitylog table.

Try this. I think it is what you need. Oh, of course you must substitute the "1" and the "2" in my example for whatever variables you are using.