[Mysql] Complex relations in database

[Gerwin7]

Hello everybody!

I first want to mention i have over 5 years of experience with mysql databases, mostly i am using mysql for 'simple' 1-M relations for websites, forums, articles etc. For a new project i have been given the following ERD diagram and data in database. I having problems setting up a query that gives all the data in the database in a result.

The problem is the bookpicture_bookmovie table. Bookimage doens'nt know any Bookimages added in Bookimage->Bookvideo relation. And Bookvideo doens'nt know any Bookvideos added in Bookimage->Bookvideo relation.

Does anyone have a suggestion?

[edited by: physics at 11:00 pm (utc) on Aug. 6, 2008]
[edit reason] Image removed - please explain the problem in the post [/edit]

[Gerwin7]

Well, a picture says more than thousand words. Pitty i cannot post a image here. I try to explain, i got the following tables.
---------------------------------------------

CREATE TABLE book (
idbook INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(idbook)
);

CREATE TABLE bookmovie (
idbookmovie INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
bookfilmpje VARCHAR NULL,
PRIMARY KEY(idbookmovie)
);

CREATE TABLE bookmovietitle (
idbookmovie INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
idbookmovietitle INTEGER UNSIGNED NULL,
PRIMARY KEY(idbookmovie)
);

CREATE TABLE bookmovie_bookmovietitle (
idbookmovie INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
bookmovietitle_idbookmovie INTEGER UNSIGNED NOT NULL,
bookmovie_idbookmovie INTEGER UNSIGNED NOT NULL,
idbookmovietitle INTEGER UNSIGNED NULL,
PRIMARY KEY(idbookmovie),
INDEX bookmovie_bookmovietitle_FKIndex1(bookmovie_idbookmovie),
INDEX bookmovie_bookmovietitle_FKIndex2(bookmovietitle_idbookmovie)
);

CREATE TABLE bookpicture (
idbookpicture INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
bookplaatje VARCHAR NULL,
PRIMARY KEY(idbookpicture)
);

CREATE TABLE bookpicturetitle (
idbookpicture INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
idbookpicturetitle INTEGER UNSIGNED NULL,
PRIMARY KEY(idbookpicture)
);

CREATE TABLE bookpicture_bookmovie (
idbookpicture INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
bookmovie_idbookmovie INTEGER UNSIGNED NOT NULL,
bookpicture_idbookpicture INTEGER UNSIGNED NOT NULL,
idbookmovie INTEGER UNSIGNED NULL,
PRIMARY KEY(idbookpicture),
INDEX bookpicture_bookmovie_FKIndex1(bookpicture_idbookpicture),
INDEX bookpicture_bookmovie_FKIndex2(bookmovie_idbookmovie)
);

CREATE TABLE bookpicture_bookpicturetitle (
idbookpicture INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
bookpicturetitle_idbookpicture INTEGER UNSIGNED NOT NULL,
bookpicture_idbookpicture INTEGER UNSIGNED NOT NULL,
idbookpicturetitle INTEGER UNSIGNED NULL,
PRIMARY KEY(idbookpicture),
INDEX bookpicture_bookpicturetitle_FKIndex1(bookpicture_idbookpicture),
INDEX bookpicture_bookpicturetitle_FKIndex2(bookpicturetitle_idbookpicture)
);

CREATE TABLE booktitle (
idbook INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
booktitle VARCHAR NULL,
PRIMARY KEY(idbook)
);

CREATE TABLE book_bookmovie (
idbook INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
bookmovie_idbookmovie INTEGER UNSIGNED NOT NULL,
book_idbook INTEGER UNSIGNED NOT NULL,
idbookmovie INTEGER UNSIGNED NULL,
PRIMARY KEY(idbook),
INDEX book_bookmovie_FKIndex1(book_idbook),
INDEX book_bookmovie_FKIndex2(bookmovie_idbookmovie)
);

CREATE TABLE book_booktitle (
idbook INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
booktitle_idbook INTEGER UNSIGNED NOT NULL,
book_idbook INTEGER UNSIGNED NOT NULL,
idbooktitle INTEGER UNSIGNED NULL,
PRIMARY KEY(idbook),
INDEX book_booktitle_FKIndex1(book_idbook),
INDEX book_booktitle_FKIndex2(booktitle_idbook)
);

CREATE TABLE book_picture (
idbook INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
bookpicture_idbookpicture INTEGER UNSIGNED NOT NULL,
book_idbook INTEGER UNSIGNED NOT NULL,
idbookpicture INTEGER UNSIGNED NULL,
PRIMARY KEY(idbook),
INDEX book_picture_FKIndex1(book_idbook),
INDEX book_picture_FKIndex2(bookpicture_idbookpicture)
);

--------------------------------------------

I join the following:

SELECT *
FROM book
LEFT JOIN book_booktitle USING (idbook)
LEFT JOIN book_bookimage USING (idbook)
LEFT JOIN book_bookvideo USING (idbook)
LEFT JOIN bookimage USING (idbookimage)
LEFT JOIN bookvideo USING (idbookvideo)
LEFT JOIN bookimage_bookimagetitle USING (idbookimage)
LEFT JOIN bookvideo_bookvideotitle USING (idbookvideo)
LEFT JOIN bookimagetitle USING (idbookimagetitle)
LEFT JOIN bookvideotitle USING (idbookvideotitle)

LEFT JOIN bookimage_bookvideo ?

The problem is the bookpicture_bookmovie table. Bookimage doens'nt know any Bookimages added in Bookimage->Bookvideo relation. And Bookvideo doens'nt know any Bookvideos added in Bookimage->Bookvideo relation.

Does anyone have a suggestion?

[edited by: Gerwin7 at 11:22 pm (utc) on Aug. 6, 2008]

[Gerwin7]

Isn't there nobody that can help me or at least give me some directions?

I begin with joining the bookimage (via book_bookimage) on table book.
Next i'll join bookvideo (via book_bookvideo) on table book.

Because i have joined bookimage and bookvideo there have been created new rows (bookimage_bookvideo). But i cannot 'rejoin' the above joins anymore. They are already joined.

If i try using a subquery, i keep adding subquery's.

I am certain i am making a wrong turn somewhere, maybe the approach is wrong from the start.