Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query Multiple Tables 1

Status
Not open for further replies.
bluesauceuk

bluesauceuk

Programmer
Jan 1, 2001
73
GB
Hello,

I have a problem. I am writing a video list for home in CF. And am having no problems until today.

I have a table called films - this has its name, decription, director etc. But also contains three fields called genre1, genre2 and 3.

These are the ID numbers of the genre table. for example (1 is comedy, 3 is horror etc)

Now I can get one genre description to show, but not all three! How do I do it?

Here's the code:
-----------------

<cfquery name=&quot;getfilms&quot; datasource=&quot;mousemedia2&quot;>
SELECT * FROM film, tape, genre
WHERE film.tapeid = tape.tapeid
AND film.genre1 = genre.genreid
</cfquery>
<!--- end query --->

<LINK REL=&quot;stylesheet&quot; HREF=&quot;../css/crapvids2003.css&quot; TYPE=&quot;text/css&quot;>
</HEAD>

<BODY BGCOLOR=&quot;#FFFFFF&quot; TEXT=&quot;#000000&quot;>


<p><B>List Films</B> <BR>
</p>
<table width=&quot;700&quot; border=&quot;0&quot; cellspacing=&quot;0&quot; cellpadding=&quot;0&quot;>
<tr>
<td>Tape</td>
<td>Name</td>
<td>Actors</td>
<td>Genre</td>
<td>Year</td>
<td>Director</td>
<td>Duration</td>
<td>---</td>

</tr>
<cfoutput query=&quot;getfilms&quot;>
<tr>
<td>#getfilms.realnumber#</td>
<td>#getfilms.name#</td>
<td>#getfilms.Actor1#, #getfilms.Actor2#, #getfilms.Actor3#</td>
<td>(id nos: #getfilms.genre1# #getfilms.genre2# #getfilms.genre3#) what i want, but for all three #getfilms.description#</td>
<td>#getfilms.year#</td>
<td>#getfilms.director#</td>
<td>#getfilms.duration#</td>
<td>&nbsp;</td>
</tr>
</cfoutput>
</table>

 
Sort by date Sort by votes
something like this should work:

<cfquery name=&quot;getfilms&quot; datasource=&quot;mousemedia2&quot;>
SELECT * FROM film, tape, genre
WHERE film.tapeid = tape.tapeid
AND ((film.genre1 = genre.genreid) OR (film.genre2 = genre.genreid) OR (film.genre3 = genre.genreid))
</cfquery>

I personally would make a structure change to the database so that the instead of having the three generes stored in one table, have another table that is effectivelly a link table, called something like filmgenres

The structure would be like this:

filmgenres
FilmID
GenreID

so in the future if you want to store more genre information about a film, instead of adding another field to the films table, you would just add another record to the filegenre table.

FilmID GenreID
1 2
1 1
1 3
1 4
2 4

then you query from above would look like:

<cfquery name=&quot;getfilms&quot; datasource=&quot;mousemedia2&quot;>
SELECT *
FROM film, tape, genre, filmgenre
WHERE film.tapeid = tape.tapeid
AND filmgenre.FilmID = Film.FilmID
AND Genre.GenreID = FilmGenre.GenreID
</cfquery>

Also, just a little tip really, use aliases on your table names. It makes the code easier to read, and if you decided to change the name of your table you only need to change the from statement!


<cfquery name=&quot;getfilms&quot; datasource=&quot;mousemedia2&quot;>
SELECT *
FROM film f, tape t, genre g, filmgenre fg
WHERE f.tapeid = t.tapeid
AND fg.FilmID = f.FilmID
AND g.GenreID = fg.GenreID
</cfquery>

Hope this helps!

Tony
 
Upvote 0 Downvote
Erm... confussed!

I have a table genre... it has

genreid description
1 action
2 comedy
3 horror
4 kids
5 thriller

_____________________________

The film table has, normal fields, plus genre1, 2 and 3, which are the id numbers of the genre table... for example..


Pulp Fiction,
John Travolta, Samuel L. Jackson, Uma Thurman
1 5 7 (this being the numbers of the genres!)
1994
Quentin Tarantino
2:25

__________________________

It is very difficult to explain what I mean...

 
Upvote 0 Downvote
Yep, what I am suggesting to do is to create a new table, called filmgenres, which will have FilmID and GenreID in it. The Genre fields from the Films table will be removed, from the Film table.

You database structure, would basically become like this:

Films
FilmID
name
decription
director

Genres
GenreID
Genres

FilmGenres
FilmID
GenreID

This sort of database structure would allow you, to associate a film with more then one genre if you so wanted to. If you don't want to do this then stick with the structure that you have, but use the or statement from the top of my last post.

bit difficult to describe it here, but i hope you get the idea!

Tony
 
Upvote 0 Downvote
It's Working! But with multiple rows for each film...


I am trying not to use another table.

Is there a simple way of looking up the &quot;real name&quot; of a genre, for each of the genre1, genre2 and genre3 in one query!

here's the Qry as now:

<cfquery name=&quot;getfilms&quot; datasource=&quot;mousemedia2&quot;>
SELECT * FROM film, tape, genre
WHERE film.tapeid = tape.tapeid
AND ((film.genre1 = genre.genreid) OR (film.genre2 = genre.genreid) OR (film.genre3 = genre.genreid))
</cfquery>

Just like you said!

Mark :)
 
Upvote 0 Downvote
I would like each row to show... the genre column with up to three selected genres:

5 Pulp Fiction John Travolta, Samuel L. Jackson, Uma Thurman Action/Thriller/Drama 1994 Quentin Tarantino 2:25

awwhhh!! If you help me sort this out you get 100% feedback! It's driving me mad!
 
Upvote 0 Downvote
Assuming you have three GENRE_ID fields for each film to identify cross-genre films. To show all three genres in one query, you have to join to the genre table three times with different aliases.

SELECT FILM_NAME_TX, GEN1.GEN_DESC, GEN2.GEN_DESC, GEN3.GEN_DESC
FROM FILMS F
LEFT JOIN GENRES GEN1 ON F.GENRE1 = GEN1.GENRE_ID
LEFT JOIN GENRES GEN2 ON F.GENRE2 = GEN2.GENRE_ID
LEFT JOIN GENRES GEN3 ON F.GENRE3 = GEN3.GENRE_ID

This lets you pull the same column (Genre Desc) from the genre table three times one row.

Hope that helps,
Dave
 
Upvote 0 Downvote
Nearly there!

Here is the SQL:

<cfquery name=&quot;getfilms&quot; datasource=&quot;mousemedia2&quot;>
Select
film.FilmID,
film.TapeID,
film.Name,
film.`Desc`,
film.Duration,
film.Genre1,
film.Genre2,
film.Genre3,
film.Actor1,
film.Actor2,
film.Actor3,
film.DateAdded,
film.`Year`,
film.Director,
genre.GenreID,
genre.Description,
genre1.GenreID,
genre1.Description,
genre2.GenreID,
genre2.Description,
`tape`.TapeID,
`tape`.RealNumber
From
film film,
genre genre,
genre genre1,
genre genre2,
`tape` `tape`
Where (
film.TapeID = `tape`.TapeID

And film.Genre1 = genre.GenreID
And film.Genre2 = genre1.GenreID
And film.Genre3 = genre2.GenreID
)
</cfquery>

How do I <cfoutput> the genre's description?

So far.....

<table width=&quot;700&quot; border=&quot;0&quot; cellspacing=&quot;0&quot; cellpadding=&quot;0&quot;>
<tr>
<td>Tape</td>
<td>Name</td>
<td>Actors</td>
<td>Genre</td>
<td>Year</td>
<td>Director</td>
<td>Duration</td>
<td>---</td>

</tr>
<cfoutput query=&quot;getfilms&quot;>
<tr>
<td>#getfilms.realnumber#</td>
<td>#getfilms.name#</td>
<td>#getfilms.Actor1#, #getfilms.Actor2#, #getfilms.Actor3#</td>
<td>#genre1.Description# #genre2.Description# #genre3.Description#</td>
<td>#getfilms.year#</td>
<td>#getfilms.director#</td>
<td>#getfilms.duration#</td>
<td>&nbsp;</td>
</tr>
</cfoutput>
</table>

But this doesn't work!

Help!

Thanks
 
Upvote 0 Downvote
Sorry, I left a bit out of the first SQL statement. You need to alias each GENRE_DESC to a different name in the SELECT clause so you can reference each one.

<cfquery name=&quot;getfilms&quot; datasource=&quot;mousemedia2&quot;>
Select
film.FilmID,
film.TapeID,
film.Name,
film.`Desc`,
film.Duration,
film.Genre1,
film.Genre2,
film.Genre3,
film.Actor1,
film.Actor2,
film.Actor3,
film.DateAdded,
film.`Year`,
film.Director,
genre.GenreID AS GenreID1,
genre.Description AS GenDesc1,
genre1.GenreID AS GenreID2,
genre1.Description AS GenDesc2,
genre2.GenreID AS GenreID3,
genre2.Description AS GenDesc3,
`tape`.TapeID,
`tape`.RealNumber
...

In your code, it looks like you replaced the query name with the Genre table's alias. Keep the query name, then you can simply reference each column by its alias.

<cfoutput query=&quot;getfilms&quot;>
<tr>
<td>#getfilms.realnumber#</td>
<td>#getfilms.name#</td>
<td>#getfilms.Actor1#, #getfilms.Actor2#, #getfilms.Actor3#</td>
<td>#getfilms.genDesc1# #getfilms.genDesc2# #getfilms.genDesc3#</td>
<td>#getfilms.year#</td>
<td>#getfilms.director#</td>
<td>#getfilms.duration#</td>
<td> </td>
</tr>
</cfoutput>

That should get you there.
Dave
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

fdgsogc
  • Locked
  • Question
Displaying results of an SQL Pivot Query
Replies
1
Views
651
jdhilljr
jdhilljr
hifispin2015
  • Locked
  • Question
Creating a slect all button for A Multiple Slect HTML Form Field
Replies
1
Views
297
LyndonOHRC
LyndonOHRC
Ceilicat
  • Locked
  • Question
Query of Queries Union Not Totaling Across Fields
Replies
2
Views
349
OneTrickPony
OneTrickPony
redoakhg
  • Locked
  • Question
SQL Query Help 1
Replies
5
Views
314
redoakhg
redoakhg
raist3001
  • Locked
  • Question
Update multiple files in a site
Replies
2
Views
272
KozarTech
KozarTech

Part and Inventory Search

Sponsor

Back
Top