Creating separate queries with join

[stone28]

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

 

[namliam]

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 & ")"

 

[wiklendt]

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.

 

[namliam]

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

 

[stone28]

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

 

[namliam]

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

 

[stone28]

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

 

[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

 

[namliam]

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

 

[stone28]

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

 

[namliam]

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

 

[wiklendt]

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.

 

[stone28]

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