linking a lookup table

mattux

mattux

mattux

Associate
Joined
17 Oct 2002
Posts
312
Location
herts
hi there
i need to write a sql query that will get 5 fields from a table, one of these fields is linked to a lookup table so i need to drop the value of that field but use it to get the value it links to in the other table.
fields in rates table are

ratesid <-primary
seasonid <-linked to lookup
startdate
enddate
price

fields in seasons table are

seasonid <-primary
season

im not sure how to link tha table in the sql statement
i want to be able to access it in php by $a_row[season] $a_row[startdate] $a_row[enddate] $a_row[price]

be gratefull if someone could help me out

matt
 
select season, startdate, enddate from rates join seasons on rates.seasonid = seasons.seasonid

would this give me what im after?
 
If I've got what you mean, something like this should fit right up your alley...

SELECT * FROM Rates INNER JOIN Seasons ON Rates.SeasonID = Seasons.SeasonID

*edit*
To answer the above, yes, yes that would work.

LOL
 
hey spunkey thanks it worked fine.
now what about adding a record to rates
if i used a drop down box with all the seasons in it how can i end up just adding the seasonid that is relative to the season i want, so my sql query would have to match the season and and just input the season id.

i hope you understand that

insert into rates season, startdate, enddate, price ........season needs to be looked up, the seasonid value returned and placed into the seasonid of the rates table

cheers
 
Last edited:
can anyone help me with the insert version of this sql query please

the description of what i need is in the post above

cheers

matt
 
Hi Matt,

In the seasons dropdown box, you need to give the items a value which is their seasonid, but display their actual name like this...

Code: 
<select name="cboSeasonID">
  <option value="1">Winter</option>
  <option value="2">Autumn</option>
  <option value="3">Spring</option>
  <option value="4">Summer</option>
</select>

Then you can directly enter the seasonid from the form using your insert SQL, rather than having to run another query to get the ID.
 
You must log in or register to reply here.
Back
Top Bottom