Tom's Guide | Tom's Hardware | Tom's Games

Computing.Net > Forums > Web Development > Multiple SQL JOINS

Computer Problems? Computing.Net has over 1,000,000 posts about all things technology related! Over 90% answered within 24 hours! Click here to start participating now! Also, be sure to check out the New User Guide.

Multiple SQL JOINS

Reply to Message Icon

Name: chris_dee
Date: June 8, 2006 at 03:42:35 Pacific
OS: Windows XP sp2, Linux Ubu
CPU/Ram: AMD Athlon 1800xp 256mb R
Comment:

Hi,

I am wanting to display information on a webpage about my music collection.

I have 3 tables, music_info, genres and mediums.

within the music_info table it references the genre and medium through an ID number which coresponds to a record within the genre and medium tables.

i want to obtain all of this information so that i can display it all on the webpage.

I have used JOIN to do part of this shown below:

SELECT music_info.*, genres.*
FROM music_info
INNER JOIN genres
ON music_info.genreID = genres.genreID

I want to be able to include the medium in the statement aswell so that i can use it to display it on the webpage.

I thought maybe i could use the sql shown below to do this but doesnt seem to work in that way.

SELECT music_info.*, genres.*, medium.*
FROM music_info
INNER JOIN genres
ON music_info.genreID = genres.genreID
AND INNER JOIN mediums
ON music_info.mediumID = mediums.mediumID

anyone know how i can obtain all the infromation from all tables in one SQL statement.

I am using ASP and Microsoft Access on IIS (Win XP Pro)

Thanks

Chris



Sponsored Link
Ads by Google

Response Number 1
Name: SN
Date: June 8, 2006 at 08:21:23 Pacific
Reply:

You almost got it...Just get rid of the AND.

SELECT music_info.*, genres.*, medium.*
FROM music_info
INNER JOIN genres
ON music_info.genreID = genres.genreID
INNER JOIN mediums
ON music_info.mediumID = mediums.mediumID

I think the inner join is probably the right choice here, assuming all rows in the music_info table have associated genres and mediums.

Good luck,
-SN


0

Response Number 2
Name: chris_dee
Date: June 8, 2006 at 11:13:51 Pacific
Reply:

I thought that myself and i tried that.

However i am still getting this error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'music_info.genreID = genres.genreID INNER JOIN mediums ON music_info.mediumID = mediums.mediumID'.
/music/index.asp, line 42

i get this when i use the following statement:

strSQL = "SELECT music_info.*, genres.*, mediums.* FROM music_info INNER JOIN genres ON music_info.genreID = genres.genreID INNER JOIN mediums ON music_info.mediumID = mediums.mediumID"

any ideas?

Thanks

Chris



0

Response Number 3
Name: SN
Date: June 8, 2006 at 12:23:02 Pacific
Reply:

Hmmm...Maybe Access requires parens:.

SELECT music_info.*, genres.*, mediums.* FROM (music_info INNER JOIN genres ON music_info.genreID = genres.genreID) INNER JOIN mediums ON music_info.mediumID = mediums.mediumID

Good luck,
-SN


0

Response Number 4
Name: chris_dee
Date: June 8, 2006 at 12:43:27 Pacific
Reply:

Thats done the trick.

Thanks :-)


0

Sponsored Link
Ads by Google
Reply to Message Icon

Related Posts

See More



Post Locked

This post is quite old and has been locked from receiving new replies. Please create a new posting instead.


Go to Web Development Forum Home


Sponsored links
Ads by Google


Results for: Multiple SQL JOINS
Multiple SQL JOINS www.computing.net/answers/webdevel/multiple-sql-joins/2978.html

Multiple SQL Joins www.computing.net/answers/webdevel/multiple-sql-joins/4243.html

SQL database design question www.computing.net/answers/webdevel/sql-database-design-question/1881.html