Hi All,

I am trying do create simple online movie DB for myself and my friends to use, but first of course I have to design properly my DB. As I am not really good with SQL I have problems straight at the beginning ;-(. The error I am having is:

"The SWL statement could not be executed because it contains ambiguous outer join. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement".

Here w w w. kamyki. com/access/relations.jpg (please remove the spaces) you can see really simple relations between tables (really simple and I still can’t do it).
So I have table with movies (tbl_movies), table with genre (tbl_genre) and table in between which connects both of them, where I can store id’s of movies with id’s of genre they belong to.

tbl_movieGenreCompact
movie_id
genre_id
1
1,20
2
1,20


So you can see here that movie with id=1 belongs to genre with id=1 and 20 (later on with the ASP server script I will work out what genre which ID is (I think I can’s do it via SQL).
So the query:

SELECT tbl_movies.movie_title, tbl_movies.movie_title_org, tbl_movies.movie_year, tbl_movies.movie_imdb, tbl_movieGenreCompact.genre_id
FROM (tbl_movies INNER JOIN tbl_movieGenreCompact ON tbl_movies.movie_id = tbl_movieGenreCompact.movie_id) INNER JOIN (tbl_genre INNER JOIN tbl_movieGenre ON tbl_genre.genre_id = tbl_movieGenre.genre_id) ON tbl_movies.movie_id = tbl_movieGenre.movie_id;

Generates me the table with movies and genres, but if one movie doesn’t have the genre set yet it won’t be shown. I know I have to separate now that query into two smaller ones to generate all movies and then genres of the ones that have something in the tbl_movieGenreCompact table, but I don’t know how.

Please help…. I know it’s not that difficult as few years ago I am sure I would be able to do this but now I really can’t. I mean I hope all here make sense.

Thanks for all help in advance.

Stone28

why not just use internet movie database? (http://www.imdb.com)

Thanks, shame it has nothing to do with my question. IMDB is not really the place where I can leave some comments about the movie for my friends and vice versa.

Thanks, shame it has nothing to do with my question. IMDB is not really the place where I can leave some comments about the movie for my friends and vice versa.

Shame your question didn't include what you were actually doing.

as for your question: you show a relationship window of three tables (a usual junction setup). but it doesn't seem to relate in any way to the query you are having problems with (where is tbl_movieGenreCompact?)

i presume your issue is the joins themselves. if you try playing with changing the INNER JOIN to either LEFT or RIGHT join, it might solve your issue (or get you some progress anwyay). it's the same as if you went into query design mode and changed the join from option one (where the fields form both tables are equal) to options two (all records from one table) or three (all the records from the other table).

http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=28678&stc=1&d=1251818203

hi again,

as I wrote in my first post: the tbl_movieGenreCompact is the table that conects tbl_movies and tbl_genre. It keeps the movie_id and genre_id that movie belonges to.

That;s the beginning of it:

http://www.kamyki.com/access/db6.jpg

And what tbl_movieGenreCompact holds:
http://www.kamyki.com/access/db6_table.jpg

So it's displaying only movies which have genre_id on them (entry in tbl_movieGenreCompact), but I want to list all movies from tbl_movies and if they have genre entry display it as well. In this case I have one more entry in tbl_movies but it doesn't have any genre yet. Because of that it's not being displayed here.

first of all, the relationship image you show in post#6 (where you show tblmovieGenreCompact) is different to the one you showed in post #1 (showing tblmovieGenre). so it's hard for anyone to know that the two tables are the same - but is it? do you have a tblmovieGenre?

second, your relationship image in post #6 show that the tblmovieGenreCompact has two fields: movieid and genreid. HOWEVER you then present me with a QUERY and tell me it's your table tblmovieGenreCompact. moreover, you are not showing me your design view or SQL - so i cannot draw any conclusions about what's going on ergo i cannot trouble shoot it.

thirdly - an ID should be generated automatically in the individual tables (tbl_movies or tbl_genre) with 'autonumber' and would NOT have a comma in them (as you show in your second image in post #6 with genre_id as the last field in your query).

attached is what a junction table structure SHOULD look like and what its relationship is to the 'single' tables. YOUR TABLE STRUCTURE LOOKS FINE, so there's something else fishy going on.

http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=28695&stc=1&d=1251846761

if you open the table "tblmovieGenreCompact" itself, what do you see in the genre_id field? and what do you see in the movie_id field?

Sorry for not being more detailed.

So it looks like that:

http://www.kamyki.com/access/relations001.jpg

I was thinking, should I have one entry for each movie category, so instead of two here (2 movies, 2 categories each but after comma) would be 4 records (2 movies 2 categories each). I decided for the first option (on the picture), but it will be filled from the application side - so I will process everything in ASP and then just write (genre1, genre2, genre3,....) for each movie. As you can see, that is why I have there ID with comma between. I think I should named it genre_ids not genre_id. But like I've said I will input that from the application side. Or do you think I should keep it as separate records. I thought having it together would be easier to maintain. Anyway, One way or another I still would like to list all movies (3 in this case) but only two of them have some genre(s) against them.

Hope it makes more sense now. Everything else is like in the posts above.

Thanks for help

ok. that makes a little more sense now. i say only a 'little' because i don't have an ASP bone un my body, so i have no idea how it best processes data with commas.

however, the commas are why you are having SQL problems in access. access (as far as i know) joins foreign ID keys as single numbers.

having said that, however, can you show me the design view of your query and we'll try to work something out.

you MAY be able to parse out the IDs into separate IDs using the comma as a flag but this will be extra code that you will need to use in VBA and not in query design - i'm not sure how compatible that will be with an online interface (simply because i've never done an online interface to access (or any other database, for that matter)).

i'd expect, though, that ASP can handle (what i will call) "normal" junctions, just like access.

so what you should have in your junction table (tbl_movieGenreCompact) are records like this:



movieID genreID
1 1
1 20
2 1
2 20

hi again,

I just want to create query which will give me that in the result:

http://www.kamyki.com/access/relations002.jpg

all movies, and genre_id field (not access ID field - just content of that field - it's not autonumber - it's just text field which I will be filling with outside code) next to the ones which have something there.

stone

movieID genreID
1 1
1 20
2 1
2 20


This is the proper way to store a 1:N relationship, not the way your doing it...
You are trying to store "1, 20"
Then Cut out the 1 and 20 into a seperate record/column and relate that column to your Genre table... This is the wrong way around.

Your movieGenre table should look like Wiklendt said. Then if you "need" 1, 20 for some reason you can run retrieval code to list it this way, but the table should not store this...

But in my case my way would work better for me, as I will be doing a lot of processing on the ASP page side, and it will make it easier for making AJAX calls to the DB....
Please can somebody tell me how to create that SQL call. That's is all I need. I might decide to make it your way but first I have to test my approach.

Thank you.

stone

Well since it is a comma seperated text box, have you considered using the IN ?

Where Genre_ID in (yourTable.CommaGenreID)

not sure it would work though... Perhaps you can even put this in the Join, but I am even more doubtfull of that.

The base of any application is and must be your Database, getting things right in the database is #1 priority and then addapting coding to fit. I am an absolute N00B at ASP and only know AJAX as a cleannig agent or Football team.
I still think the core of any decent app is a great strong well designed database. Without the DB, your app is going no where (course without the app no db either)
Ying - Yang
Chicken - Egg

Yeah yeah, still DB #1

Please ;-) I just want to know that SQL query!!!! I will might change the DB but it's really small app for me and my friends so in this case I want to make it as simple as possible for me to move forward and the way with comma looks to me OK (I was using something similar before and it worked OK).

Please just a query.....

Thank you.

Please ;-) I just want to know that SQL query!!!! I will might change the DB but it's really small app for me and my friends so in this case I want to make it as simple as possible for me to move forward and the way with comma looks to me OK (I was using something similar before and it worked OK).

Please just a query.....

Thank you.

well, as far as i know an SQL query will not be able to do what you want with the setup you have.

we have told you how to normalise your database correctly. we can't 'just' give you an SQL query to your problem because your problem is unusual.

i suggest you either normalise your database appropriately, THEN we can 'just' help you get to an SQL query, or you go back to your ASP/AJAX books (this is an access forum) and determine how to parse a string into separate variables.

having said that, if you want to get your hands dirty with VBA, there ought to be a solution there, but keep in mind that's the "round about" way of doing what normally a very simple thing in access.

the other issue with you storing your genreIDs that way is - how are you going to use this setup at the data entry end? it is much simpler to add data to a properly structured database than to use hefty code just to make it almost work in some bare sense.

But I just want you to treat the content of tbl_movieGenreCompact.genre_id as normal String..... There are no ID as far as the SQL is concern..... it's just a string - 10,2 or 1,3 or blablabla..... It doesn't matter..... it's just a string for me no values normal simple string.....

Well since it is a comma seperated text box, have you considered using the IN ?

Where Genre_ID in (yourTable.CommaGenreID)

not sure it would work though... Perhaps you can even put this in the Join, but I am even more doubtfull of that.

See above suggestion from my previous post, which you happilly skipped over... It might work... but I doubt it...

hi again,

I just want to create query which will give me that in the result:

http://www.kamyki.com/access/relations002.jpg

all movies, and genre_id field (not access ID field - just content of that field - it's not autonumber - it's just text field which I will be filling with outside code) next to the ones which have something there.

stone

haven't you done that already? look at the picture of the select query in your post # 6. as far as i can make out, what you wanted was to to list the movie data with the STRING of the genre data. as far as i understand, that is exactly what you've done in post#6.

if what you want is the SQL code, then click the "SQL View" instead of Query Design or Datasheet Views.

But I just want you to treat the content of tbl_movieGenreCompact.genre_id as normal String..... There are no ID as far as the SQL is concern..... it's just a string - 10,2 or 1,3 or blablabla..... It doesn't matter..... it's just a string for me no values normal simple string.....

And her-in lies the problem, it is just a string to you, but to the database it is an ID.... and yes that is FOR THE SQL...

One option that WILL work is to "normalize" this in a query. First question you need to answer is how many values will this string contain at maximum? 2, 5,10, 100?

Now make a function
Function getID(YourString As String, getNumber As Double)
Dim x As Variant
x = Array(10)
x = Split(YourString, ",")
getID = x(getNumber - 1)
End Function
This example allows for up to 10 values in your string, expand or change where needed.

This function you call in a query.

Select MovieID, GetID(YourDatabasString,1) NormGenreID
from tbl_MovieGenreCompact

Now you have the first value only, so you need to fetch all values using a union query

Select MovieID, GetID(YourDatabasString,1) NormGenreID from tbl_MovieGenreCompact
Union all
Select MovieID, GetID(YourDatabasString,2) NormGenreID from tbl_MovieGenreCompact
Union all
Select MovieID, GetID(YourDatabasString,3) NormGenreID from tbl_MovieGenreCompact
Union all
Select MovieID, GetID(YourDatabasString,4) NormGenreID from tbl_MovieGenreCompact
Union all
Select MovieID, GetID(YourDatabasString,5) NormGenreID from tbl_MovieGenreCompact
Union all
Select MovieID, GetID(YourDatabasString,6) NormGenreID from tbl_MovieGenreCompact
Union all
Select MovieID, GetID(YourDatabasString,7) NormGenreID from tbl_MovieGenreCompact
Union all
Select MovieID, GetID(YourDatabasString,8) NormGenreID from tbl_MovieGenreCompact
Union all
Select MovieID, GetID(YourDatabasString,9) NormGenreID from tbl_MovieGenreCompact
Union all
Select MovieID, GetID(YourDatabasString,10) NormGenreID from tbl_MovieGenreCompact

This query has basicaly normalized your "mess" this you can then use to join into the Genre table.

Again this is for 10 genre's per movie add/change per requirement.

It aint pretty
It wont be fast
But it will work...

OK, I can see what the problem is and why you can't understand what I am trying to say - that it's not an ID field.........On my relations screen there shouldn't be connection between tbl_movieGenreCompact and tbl_movieGenre... - that's why I am saying that it's just a string. Sorry again. So it's just a string and then I will work out what numbers (1,2,30) in tbl_movieGenreCompact means with another SQL query to tbl_movieGenre, but that will be in a separate query. Thanks

But not me, but you are missing the point... You shoudlnt work out what 1,2,30 means in a seperate query...

Course this could be an AJAX thing or something... But from a database p.o.v. this is bad. Been there done that.

So if you can then repeat, what is your problem?? I mean... if not this then what?
Are you wanting to fetch the genre's ?? I.e. the string is "1,2,30" then fetch those 3 genre's in a seperate query?? Is that the point?
Then I refert back to my earlier suggestion of using In

You build the query more or less like so...
"Select * from yourGenreTable where GenreID in (" & yourstring & ")"

it's not an ID field.........On my relations screen there shouldn't be connection between tbl_movieGenreCompact and tbl_movieGenre

from everything we've seen about your database, there SHOULD be a connection between tbl_movieGenreCompact and tbl_movieGenre.

from everything we've seen about your database, genreID IS and ID.

from everything we've been told by you about your database i would argue that you WANT the genreID to relate to the tbl_Genre and return the string(!) for that record (not the IDs), which is identified by the database using the ID assigned to it as an autonumber.

HOWEVER: if all you want is a query to return the 'string' (what is the field type in those two tables: tbl_Genre and tbl_movieGenreCompact?) of the "genreID" to the movie, then RUN YOUR SELECT QUERY that you showed everyone you can do from post #6 - as far as you are explaining anything, your select query which you showed us in post #6 have already made (or are capable of making) should do exactly as you want (that is, exactly what you told US you want!).

and please, if you have tried a suggestion on someone's post and it didn't work for you, or if you don't think it's the right approach, then it is customary to say so in another post so that the people who are generously giving their time and expertise don't feel like it's in vain and wasting their time with your question.

But not me, but you are missing the point... You shoudlnt work out what 1,2,30 means in a seperate query...

Course this could be an AJAX thing or something... But from a database p.o.v. this is bad. Been there done that.

So if you can then repeat, what is your problem?? I mean... if not this then what?
Are you wanting to fetch the genre's ?? I.e. the string is "1,2,30" then fetch those 3 genre's in a seperate query?? Is that the point?
Then I refert back to my earlier suggestion of using In

You build the query more or less like so...
"Select * from yourGenreTable where GenreID in (" & yourstring & ")"

Hope that helps

Thank you, thank you and once again thank you guys..... for all the suggestions.

As for my query in the post #6 that is exactly wha tI wanted but as I wrote in the post #6 at the bottom, it lists only movies with genre attached to them. So if I have 10 movies in my main movie table but only 4 of them have genre set (as it will be optional field) then my query will render only those 4 movies not all 10.... And that is where my problem is. So I would like something like that....

SQL = Select (all the movies) and then for each movie select genre if it exists in genre table.

Please tell me that you know what I mean....

Thanks.

sstone28

Are you then looking for an outer join?
To display movies without a genre entered?

I think that is what I need but I have problem with creating one.... That's why I am here.... Looks like I'm not really good at it, because I really think it's preatty simple query, and still too dificoult for me at this point ;-(

SELECT tbl_movies.movie_title, tbl_movies.movie_title_org, tbl_movies.movie_year, tbl_movies.movie_imdb, tbl_movieGenreCompact.genre_id
FROM (tbl_movies INNER JOIN tbl_movieGenreCompact ON tbl_movies.movie_id = tbl_movieGenreCompact.movie_id) INNER JOIN (tbl_genre INNER JOIN tbl_movieGenre ON tbl_genre.genre_id = tbl_movieGenre.genre_id) ON tbl_movies.movie_id = tbl_movieGenre.movie_id;

There is no third movie (as there is no genre set for it yet)

stone28

GOT IT!!!!!!!!
Thank you very much .......... Outer Join is the winner..... So simple... o my god!!!!! Thanks....

SELECT tbl_movies.movie_title, tbl_movies.movie_title_org, tbl_movies.movie_year, tbl_movies.movie_imdb, tbl_movieGenreCompact.genre_id
FROM tbl_movies LEFT OUTER JOIN tbl_movieGenreCompact ON tbl_movies.movie_id = tbl_movieGenreCompact.movie_id;

I have noticed that Access is using just LEFT JOIN instead LEFT OUTER JOIN. It is the same/replaceble?

Thanks again...

stone28

Simply double click the line between the table's you want to outer join and select the option you want it to be...

Damn, don't even tell me about it ;-).... So Simple! I wasn't using Access and SQL for way too long...... Now I can move forard with it. Slowely but surely...

Thank again.

stone28

FYI
Yes Left join and Left outer join are the same thing...
There are two types of joins:
Inner join
Outer join

Then outer join allows "no values" in one of the tables, to indicate what table we have again 2 types
Left Outer Join
Right Outer Join

Which is / can be abreviated to
Left Join
Right Join

GOT IT!!!!!!!!
Thank you very much .......... Outer Join is the winner..... So simple... o my god!!!!! Thanks....

SELECT tbl_movies.movie_title, tbl_movies.movie_title_org, tbl_movies.movie_year, tbl_movies.movie_imdb, tbl_movieGenreCompact.genre_id
FROM tbl_movies LEFT OUTER JOIN tbl_movieGenreCompact ON tbl_movies.movie_id = tbl_movieGenreCompact.movie_id;

I have noticed that Access is using just LEFT JOIN instead LEFT OUTER JOIN. It is the same/replaceble?

Thanks again...

stone28


i had actually told you it was a join problem back in post #5 about 6 weeks ago. :rolleyes:

Yep, you are right... I just wasn't sure at that time what you meant by that.... But thaks for helpping me and for all your time.

stone28