Problem on Querying

[JerieLsky]

Hi, I'm having a problem on how to query from the following tables. Here are the tables.

PATIENT:
PK - PatientNo
- FirstName
- LastName
- MiddleName
- Address
- Age
FK - PageNo

PAGE
PK - PageNo

BOOK
PK - BookNo

BOOKPAGE 'Junction Table
PK - BookNo
PK - PageNo

Business Rules:
1. A book can contain many pages, and each page can belong to different books.
Example: Book 1 contains pages 1, 2, 3, etc.
Book 2 contains pages 1, 2, 3, etc.
2. Each Page contains different information about patients and a patient only can be listed in one page on a certain book.
Example: Book 1 contains Patient 1 and 2 on Page 1.
Book 1 contains Patient 3 and 4 on Page 2.

Book 2 contains Patient 5 and 6 on Page 1.
Book 2 contains Patient 7 and 8 on Page 2.
'Here a patient cannot be listed more than once in a book and a page.
'I've attached a screenshot on the ER-Diagram.

What I want is to query the list of patients that belongs to a certain book no. How can I arrive with that answer? What is the correct SQL statement to be used? I used this SQL statement,

"SELECT * FROM PATIENT INNER JOIN PAGE ON ([PATIENT].[PageNo]=[PAGE].[PageNo]) WHERE [BOOK].[BookNo] = '" + BookValue + "'"

but I am encountering this error message: "The current row is not available."
And I'm not sure if my SQL statement is right. Please help me. Thanks.

[tesuji]

Hi

Error message is correct for book of [BOOK].[BookNo] isn't listed in from clause. Also join with bookpage which makes the many-to-many relationship is missing.

select b.bookno, a.pageno, PatientNo, FirstName, LastName from patient p 
  join page a on p.pageno = a.pageno
   join bookpage bp on bp.pageno = a.pageno
   join book b on b.bookno = bp.bookno
     where ...

Your erm seems to be nearly correct for

1. A patient can only be on one page (pk of page become non identifying fk of patient) and a page has many patients
2. A book has many pages and a page is in many books (curious on the latter)

3. But "a patient only can be listed in one page on a certain book" is not complied with.

-- tesu

[JerieLsky]

Thank you for the reply.
What do you mean on 1. (pk of page become non identifying fk of patient)?

On 3. A patient can only be listed once on a certain page and in only one book, according to the client that was interviewed they only list the name of a patient once and is not duplicated in any other pages of any book, what happens here is that they search for that certain patient, then they only update/add a status of that patient if he/she has a new case.

I was thinking, the BOOK, PAGE and PATIENT entities. Should this be a Ternary Relationship?

[JerieLsky]

Hi

Error message is correct for book of [BOOK].[BookNo] isn't listed in from clause. Also join with bookpage which makes the many-to-many relationship is missing.

select b.bookno, a.pageno, PatientNo, FirstName, LastName from patient p 
  join page a on p.pageno = a.pageno
   join bookpage bp on bp.pageno = a.pageno
   join book b on b.bookno = bp.bookno
     where ...

Your erm seems to be nearly correct for

1. A patient can only be on one page (pk of page become non identifying fk of patient) and a page has many patients
2. A book has many pages and a page is in many books (curious on the latter)

3. But "a patient only can be listed in one page on a certain book" is not complied with.

-- tesu

Hi again,

I tried re-analyzing the tables that I made, I think it's a ternary relationship.

So how do I create the SQL query from a ternary relationship, same thing that I want to achieve is to list all patients from a specific book no.

[tesuji]

Well, you should carefully read and understand my reply.

Again, your business rule #1 is in direct contradiction to rule #2. Furthermore, rule#2 contradicts your ER-Diagram:

rule #1. A book can contain many pages, and each page can belong to different books.

rule #2. Each Page contains different information about patients and a patient only can be listed in one page on a certain book.

Your ER-Diagram tells that between entity book and entity page be a many-to-many relationship what is made by relationship BOOKPAGE commented 'Junction Table' by yourself.

We can't proceed until these contradictions are solved, especially your ERD should be correct concerning the many-to-many relationship represented by BOOKPAGE.

I didn't mention any "ternary" relationship for there isn't any. There is only one binary relationship so far.

Maybe you think of traditional books: A book consists of many pages. A page can only belong to a particular book. If so, there is a one-to-many relationship between book and page, and you need to change rule#1.

>>> What do you mean on 1. (pk of page become non identifying fk of patient)?
Primary key (pk) of entity page goes into entity patient. So it becomes foreign key (fk) there. This foreign key is not member of patient's pk, therefore "non identifying fk". This one-to-many relationship, designed by yourself, is entirely correct.

Btw, what about that error message: "The current row is not available." ?

>>> I tried re-analyzing the tables that I made, I think it's a ternary relationship.
Then you should post your new ERM.

-- tesu

[pratik_garg]

hi JerieLsky,

can you please look at your table structure once again..??

PATIENT:
PK - PatientNo
- FirstName
- LastName
- MiddleName
- Address
- Age
FK - PageNo
i think you need one more column here having information about book no

just think about this ...
page no is repeating in many books -- E.g. you have page no 1 in all book--
then your query, if resolved, will give all patient name which is on first page in all books it means that you can not filter patient with book_no in your present table structure...
you got me na??
so just check your table structure again and take my this suggestion in your mind..
or else you can do one more thing...
you can add patient id in BOOKPAGE table as one more primary key..
so that for perticular book and page you can enter more then one record in BOOKPAGE table, if there is.

it will automatically solve your problem as then you will easily get all patient information just by join two table only..