Query problem

[unknown101]

 

Hi Guys,

 

Wondering if anyone could give me a hand..im pretty new to SQL so go easy   I am trying to achieve the following (example):

 

- A user enters the model of a car (not the manufacturer)

 

- I then check through all the tables (each being different car makes) to see if the model is within the one of those tables.

 

- If the model is within one of those tables, all other models which are also found in that table are printed out.

 

I have tried various things but still no luck.. this is probably quite simple but gotta start somewhere!

 

Cheers

[unknown101]

Sorry guys I may have written that slightly wrong:)

 

Basically what im trying to achieve is to check an atrribute of the user input, I think a better way of explaining it may be through the following:

 

-The user enters a movie title (All movies are stored with title, year, genre, actors in the sql db)

 

-The movie title is then looked up in the db and the film genre is identified i.e. "Horror".

 

-All horror movies will be in a particular table, so I then went to output all other movies from the same table.

 

I think the previous post I missed the beginng process slightly,

 

Cheers

 

 

[GingerRobot]

Having different tables for different genres was a bad idea. You might wish to consider altering your database design, perhaps after reading up on database normalization. The sticky in this forum would be a good place to start.

 

That said, you could achieve this with two separate queries - the first to find the genre and the second to select everything from the relevant table. To the best of my knowledge, you can't achieve this in a single query.

[unknown101]

Ah bummer, I kind of see what your saying now after reading that sticky..

 

Please could you give me a hand with the normalisation (as this is my first attempt:)

 

I have the following details to be stored..

 

-Artist

-Track

-Album

-Year

-Genre

 

From this ive come up with..

 

Table 1

Artist ID  ||   Artist   ||  Genre

 

Table 2

Artist ID || Album ID || Album || Year

 

Table 3

TrackID  || Track || Album ID

 

Any help appreciated..cheers

 

[GingerRobot]

Looks pretty good to me.

[unknown101]

Thanks for that, so now i guess im back to the first question:)

 

So once I take the user input say.. $userSearch,

 

I then use this to form my first query i.e

 

SELECT Genre from TABLE1 where artist=$userSearch

 

So now I have the genre from that artist, but how can I then store this genre in a variable so I can use it in further functions?

 

Cheers