Hi all need some help I have a database what has the folloing tables in

create table delegate
(DelNo int not null,
DelName varchar(20) not null,
DelAddress varchar(50) not null,
DelTelphone varchar(20),
primary key (DelNo))
engine = innodb;

create table event
(EventNo int not null,
EventDate datetime,
HotelNo int not null,
primary key (EventNo),
foreign key (HotelNo) references hotel (HotelNo))
engine = innodb;

create table hotel
(HotelNo int not null,
HotelName varchar(50) not null,
HotelAddress varchar(80) not null,
primary key (HotelNo))
engine = innodb;

I need to select a list of the course title together with the date and event location where they are being held but not sure how to do this.

any help be good thanks

I feel tired and lazy tonight, so I will just direct you to this post: http://www.webmaster-talk.com/php-fo...tml#post794486

It will explain you the sql joins and how to craft your query.
In your case, as I understand it (and if a course is an event, and you don't need the delegate) you need to do an inner join between the fields event.HotelNo and hotel.HotelNo.
The query is as simple as the one I gave on the other post

select h.HotelName, h.HotelAddress, date_format(e.EventDate,'%W the %e of %M at %k:%i') as EventDate
from event e
  inner join hotel h on e.HotelNo=h.hotelNo

Hi thanks for that I thought it was some sort join but not sure what one it was what are the differences between inner join, left join, right join

Inner = only give me things in both tables.
Left = only give me things in the table on the left, whether they're in the other table or not
Right = the exact reverse of left.

cool thanks

Hi all is it possible todo somthing like this

select delegate.DelName,Booking.EventBB,Booking.EventNo
from delegate INNER JOIN Booking
ON delegate.DelNo = Booking.DelNo;
sum(EventBB) as toatl from Booking;

Something like.

select delegate.DelName,Booking.EventBB,Booking.EventNo,
sum(EventBB) as toatl
from delegate INNER JOIN Booking
ON delegate.DelNo = Booking.DelNo
group by delegate.DelName,Booking.EventBB,Booking.EventNo
;


You could simplify things by using correlation names (aka aliases).

Hi thanks for that but it dont really give me the result I want as I want to it to add all the value in eventbb to give me grand total

what i need to do is give the number of delegates requesting bb accommodation at each hotel .

I don't really understand what you want, how your data is modeled, or the difference between the different queries and what you want?

Most db engines will allow you to fire off multi statement batches. And virtually all allow for subqueries, like

select sum(EventBB) as theSum from (
select delegate.DelName,Booking.EventBB,Booking.EventNo
from delegate INNER JOIN Booking
ON delegate.DelNo = Booking.DelNo
) as innerQuery;

let me try and give u more information I have created a small database

##########################################
#  Table with delgates information 
#  in and DelNo being the primary key.
##########################################


create table delegate
(DelNo int not null,
DelName varchar(20) not null,
DelAddress varchar(50) not null,
DelTelphone varchar(20),
primary key (DelNo))
engine = innodb;


##########################################
#  Inserting information into the
#  delegates table.
##########################################


insert into delegate values
(1,"Jamie","10 villa Road Higham Rochester Kent ME3 7BS","01634-123-4567"),

(2,"Paul","11 villa Road Higham Rochester Kent ME3 7BS","01634-123-4568"),

(3,"Steve","28 maddock way London SE17 3NH","0207-735-9210"),

(4,"Jemma","22 Franklyn Close Maidstone ME17 7BS","01634-123-0827"),

(5,"Robert","10 Cooks road London ME5 7BS","0207-123-4567"),

(6,"Lee","40 John Ruskin Street London ME5 0NX","0207-708-4367"),

(7,"Joanna","27 Maidstone Road Rochester Kent ME3 4BS","01634-123-3940"),

(8,"Philip","109 Walworth Road London SE13 7FG","0207-123-1597"),

(9,"Mark","8 cambridge drive Cheshire CE3 4SQ","01732-123-4567"),

(10,"Arron","60 Kings Drive Gravsend Kent ME14 7TS","01634-156-4361"),

(11,"Bradley","100 victoria Close Surrey SR6 7DS","01819-623-4517"),

(12,"Heather","1 villa Road Higham Rochester Kent ME6 5BS","01634-233-4567"),

(13,"Craig","16 James Road Birmingham BE3 1NS","01204-163-7667"),

(14,"Kieron","88 Sheppy Way Sheppy Kent SH3 7FS","02394-136-4737"),

(15,"Christian","180 Kings Avenue Chatham Kent ME9 1BE","01634-786-4562");



##########################################
#  Table with Course information 
#  in and CourseNO being the primary key
##########################################


create table course
(CourseNo int not null,
CourseTitle varchar(30) not null,
CourseFee decimal(5,2) not null,
CourseDuration varchar(20),
primary key (CourseNo))
engine = innodb;


##########################################
#  Inserting information into the
#  Course table.
##########################################

insert into course values
(4000,"INTRO TO COMPUTER ARCHITECTURE",200,"Four Days"),

(4001,"VISUAL PROGRAMMING TOOLS",300,"Two Days"),

(4002,"SYSTEMS ANALYSIS AND DESIGN",180,"Three Days"),

(4003,"MULTIMEDIA ON THE WEB",150,"One Days"),

(4004,"OBJECT-ORIENTED PROGRAMMING",300,"Five Days"),

(4005,"NETWORKS",350,"Two Days"),

(4006,"WEB PROGRAMMING",300,"Five Days");

##########################################
#  Table with Hotel information 
#  in and HotelNo being the primary key
##########################################


create table hotel
(HotelNo int not null,
HotelName varchar(50) not null,
HotelAddress varchar(80) not null,
primary key (HotelNo))
engine = innodb;


##########################################
#  Inserting information into the
#  Hotel table.
##########################################

insert into hotel values
(001,"Hilton Hotel","South Quay Marsh Wall London, E14 9SH , United Kingdom"),

(002,"Hilton Hotel","129 Bayswater Road London, W2 4RJ, United Kingdom"),

(003,"Hilton Hotel","380 Kensington High Street, London EH1 2AB, United Kingdom"),

(004,"Hilton Hotel","5 More London Place Tooley Street, London SE1 2BY, United Kingdom"),

(005,"Hilton Hotel","Masthead Close Crossways Business Park, Dartford DA2 6QF, United Kingdom"),

(006,"Hilton Hotel","Bearsted Road, Weavering , Maidstone ME145AA, United Kingdom"),

(007,"Holiday Inn","Maidstont Road Chatham, Kent, ME5 9SF, United Kingdom"),

(008,"Holiday Inn","LONDON ROAD, WROTHAM HEATH Stevenoakes, TN15 7RS, United Kingdom"),

(009,"Holiday Inn","MAIDSTONE RD HOTHFIELD, Ashford, TN26 1AR, United Kingdom"),

(010,"Holiday Inn","UNIVERSITY WAY, Dartford, DA1 5PA, United Kingdom");



##########################################
#  Table with Event information 
#  in and Eventno making the primary key 
##########################################


create table event
(EventNo int not null,
EventDate datetime,
HotelNo int not null,
CourseNo int not null,
primary key (EventNo),
foreign key (CourseNo) references course (CourseNo),
foreign key (HotelNo) references hotel (HotelNo))
engine = innodb;


##########################################
#  Inserting information into the
#  booking table.
##########################################

insert into event values
(01,20081019090000,001,4000),
(02,20080524090000,002,4001),
(03,20080923090000,003,4002),
(04,20081017090000,004,4003),
(05,20080709090000,005,4004),
(06,20080404090000,006,4005),
(07,20080302090000,007,4006),
(08,20080805090000,006,4004),
(09,20081012090000,008,4005),
(010,20081111090000,009,4003);

##########################################
#  Table with Booking information 
#  in and Delno and EventNo making a composite key 
##########################################


create table Booking
(DelNo int not null,
EventNo int not null,
EventBB bool,
DelPresenter bool,
BookingFee Decimal(5,2),
primary key (DelNo,EventNo),
foreign key (DelNo) references delegate (DelNo),
foreign key (EventNo) references event (EventNo))
engine = innodb;


##########################################
#  Inserting information into the
#  booking table.
##########################################

insert into Booking values
(1,01,true,false,70),
(1,02,true,false,80),
(2,03,true,true,70),
(2,04,true,true,90),
(3,05,true,true,80),
(3,06,true,true,90),
(4,07,true,false,70),
(4,08,true,false,80),
(5,09,true,true,90),
(5,010,true,true,70),
(5,01,true,true,80),
(6,02,true,true,80),
(6,03,true,true,80),
(7,04,true,false,70),
(7,05,true,false,90),
(8,06,true,false,80),
(9,07,true,true,70),
(9,08,true,true,70),
(10,09,true,false,90),
(11,010,true,false,90),
(12,010,true,true,70),
(13,01,true,true,80),
(14,03,true,false,70),
(15,06,true,true,90),
(15,07,true,true,70);

and I have to retrive data from this database
the number of delegates requesting b&B accommodation at each hotel
the total course fees payable by each delegate
the toatl b&B charge for each delegate
the overall fees payable by each delegate

and I am havign trouble doing this all the information in the database is dummy information by the way

Select DelNo, Count(*) As DelegatesRequestingBandB, Sum(BookingFee) as TotalFees
From
delegate as d Join booking as b On d.DelNo = b.DelNo
Group by DelNo

And so on. I'm not sure if you can get all the data you want through one select statement, or if you'll need a few?

Hi can anyone see a problem with this ??

SELECT delegate.DelName, Booking.DelPresenter,
 Booking.BookingFee, Booking.EventBB,
 course.CourseNo, course.CourseFee, event.EventNo
sum(EventBB+BookingFee) as toatl
From delegate INNER JOIN(
event INNER JOIN(
course INNER JOIN(
Booking)))
ON Booking.DelPresenter = true
AND delegate.DelNo = Booking.DelNo
AND Booking.EventNo = event.EventNo
AND course.CourseNo = event.CourseNo
group by delegate.DelName,Booking.DelPresenter,Booking.EventBB,Booking.EventNo,
course.CourseNo,Booking.BookingFee,course.CourseFee;

I see 2 things. First, I'm not sure whether parens are valid with join clauses, but that's RDBMS specific. Next, the grammar says From X Inner Join Y On Z = Z Inner Join A On B = B Inner Join C On D = D, etc. You've got all your join conditions as AND expressions.

You could change the query to use SQL 92 (pre-join) syntax. From A, B, C Where A.X = B.X And B.X = C.X, etc.