Database fields advice

[markeh]

Hi there need some advice.

I have a table of site locations in a mySQL database which has 20 fields, the usual ID, address, facilities and the like.

I need to add a dates/prices grid to the information the table contains which is something like:

Date | Weekly Rate | Weekend Rate | Daily Rate | Offers
14th-18th July | £123 | £35 | £10 | somestring
19th-25th July | £132 | £53 | £15 | somestring

etc

There will be a limit of 11 rows of dates, so combined that would be an extra 55 fields added to the total, which seems excessive.

I thought about having each row in a string comma seperated reducing the fields to 11, would that be more efficient that having the extra 44 fields? Any other way of doing this?

Cheers

 

[markeh]

Wasn't me that made the table

I suppose I could create a new table for the dates/rates for this, does seem to make sense, thanks.

 

[markeh]

Is it possible using one SQL statement to pull out one value from the 1st site locations table and then all of the matching dates and prices in the 2nd table for that location? I tried playing around a bit with joins, but don't know how to get it to get the multiple matching rows from the 2nd table.

 

[markeh]

I have tried inner joins but don't know how to get it to do what I want (I have googled this as well and can't find an example that seems to match)

Imagine Table1 has:

siteid | name | address
1 | London House | London Road, Londonshire
2 | Cambridge House | Cambridge Road, Cambridgeshire
3 | Birmingham House | Birmingham Road, Birminghamshire
4 | Liverpool House | Liverpool Road, Liverpoolshire


Table2:

id | siteid | date | week_price | day_price
1 | 1 | 1st july | £390 | £75
2 | 1 | 8th July | £350 | £95
3 | 1 | 15th July | £340 | £85
4 | 2 | 1st july | £290 | £55
5 | 2 | 8th July | £250 | £75
6 | 2 | 15th July | £240 | £65
7 | 3 | 1st july | £340 | £55
8 | 3 | 8th July | £290 | £45
9 | 3 | 15th July | £230 | £35
10 | 4 | 1st july | £240 | £25
11 | 4 | 8th July | £190 | £35
12 | 4 | 15th July | £130 | £15

What I want to appear on page.asp

Name: London House

Address: London Road, Londonshire

Prices:
1st july
Weekly rate: £390
Daily rate: £75

8th July
Weekly rate: £350
Daily rate: £95

15th July
Weekly rate:£340
Daily rate: £85


From what I've managed to do with inner joins all I could get out is (without formatting):

1 | London House | London Road, Londonshire | 1 | 1 | 1st july | £390 | £75
1 | London House | London Road, Londonshire | 2 | 1 | 8th July | £350 | £95
1 | London House | London Road, Londonshire | 3 | 1 | 15th July | £340 | £85

I could use somekind of nested SQL statements but I assume there is a better way.

 

[markeh]

Cheers guys.

<%
while not rs.eof
if counter=1 then
response.write("Name: " & rs("Name") & "<BR>")
response.write("Address: " & rs("Address") & "<BR>")
end if
%>
<%= rs("date") %>
Weekly Rate: <%= rs("wrate") %>
Daily Rate: <%= rs("drate") %>

counter ++
rs.movenext
wend

Is the other way I considered doing it, I just wondered if it was possible to do the same kinda thing but in SQL somehow.