Creating separate queries with join

[stone28]

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

 

[wiklendt]

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

 

[stone28]

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.

 

[wiklendt]

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.

 

[wiklendt]

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).

 

[stone28]

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:

And what tbl_movieGenreCompact holds:

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.

 

[wiklendt]

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.

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?

 

[stone28]

Sorry for not being more detailed.

So it looks like that:

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

 

[wiklendt]

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

 

[stone28]

hi again,

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

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

 

[namliam]

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...

 

[stone28]

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

 

[namliam]

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

 

[stone28]

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.

 

[wiklendt]

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.

 

[wiklendt]

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.

 

[stone28]

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.....

 

[namliam]

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...

 

[wiklendt]

hi again,

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

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.

 

[namliam]

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...