Getting duplicate data when using the select statements-CodePudding

I'm getting duplicate data when trying to use the select statements, I have 9 rooms but I'm getting around 50-70 rooms when trying to display them. Help please?

I'm trying to insert data and display it using the select statement.

create table gym (
    GymName VARCHAR(200) primary key,
    openTime time not null,
    closeTime time not null,
    Price decimal not null,
);

create table Spa (
    spaName VARCHAR(200) primary key,
    openTime  time not null,
    closeTime time not null,
    Price decimal not null,
);

create table customer (
    CustomerID int primary key,
    Firstname varchar(200) not null,
    LastName varchar(200) not null,
    DOB date not null check (DATEDIFF(year,DOB,getdate ()) > 18) ,
    Gender char(4) not null check(Gender ='M' or Gender = 'F'),
    Address varchar(200) not null default 'Jordan',
    spaName VARCHAR(200)  foreign key references Spa(spaName),
    GymName VARCHAR(200)  foreign key references gym(GymName),
);

Create table CustomerPhoNo (
    CustomerID int foreign key references customer(CustomerID),
    PhoneNo bigint not null,
);

create table Room (
    roomNo int primary key,
    Availability char(4) not null,
    NoOfBeds int not null,
    Rate int not null,
    CheckIn date,
    CheckOut date,
    Price Decimal not null,
    Breakfast char(4),
    CustomerID int foreign key references customer(CustomerID),
);

create table LocationOfRoom (
    roomNo int foreign key references Room(roomNo),
    seaview char(4),
    Location varchar(20) not null,
);

create table RoomType (
    roomNo int foreign key references Room(roomNo),
    familyRoom char(4),
    doubleRoom char(4),
    singleRoom char(4),
);

create table Gservice (
    GymName VARCHAR(200) foreign key references gym(GymName),
    Service VARCHAR(500) not null,
    MachineType VARCHAR(500) not null,
);

create table PaymentCard (
    CardID int primary key,
    issueDate date not null,
    Expirydate date not null,
    CustomerID int foreign key references customer(CustomerID),
);

insert into customer values (325,'Mohammad','Alasharan','06-04-1984','M','Amman', 'BeautySpa', 'StrongBody')
insert into customer values (348,'John','Shelby','10-18-1998','M','Birmingham', 'LushLife', 'SilverGym')
insert into customer values (495,'Thomas','Hoffman','04-26-1968','M','Johannesburg', 'RelaxationTherapy', 'SilverGym')
insert into customer values (194,'Anne','Frank','07-22-2001','F','Frankfurt', 'BeautySpa', 'StrongBody')
insert into customer values (628,'Katie','Swan','02-10-1997','F','New South Wales', 'LushLife', 'FitnessHeroes')
insert into customer values (246,'Mahmoud','Alkutaifan','04-21-1994','M','Amman', 'BeautySpa', 'FitnessHeroes')
insert into customer values (864,'Karl-Heinz','Rummenigge','09-25-1955','M','Lippstadt', 'RelaxationTherapy', 'FitnessHeroes')
insert into customer values (824,'Dennis','Law','09-21-1979','M','london', 'RelaxationTherapy', 'FitnessHeroes')
insert into customer values (463,'Carles','Puyol','06-17-1973','M','madrid', 'LushLife', 'FitnessHeroes')

insert into Room values (124,'yes','1','4',null,null,'30','yes',null)
insert into Room values (135,'no','2','5','05-06-2022','05-09-2022','55','yes',495)
insert into Room values (121,'yes','1','3',null,null,'40','yes',null)
insert into Room values (139,'no','3','4','05-10-2022','05-14-2022','110','no',194)
insert into Room values (131,'no','3','3','05-18-2022','05-22-2022','130','yes',348)
insert into Room values (136,'no','4','4','04-14-2022','04-24-2022','120','yes',194)
insert into Room values (179,'yes','4','5',null,null,'95','no',null)
insert into Room values (138,'no','3','3','04-02-2022','04-06-2022','75','no',246)
insert into Room values (146,'no','3','5','05-10-2022','05-14-2022','80','yes',864)

insert into LocationOfRoom values (124,'no','south')
insert into LocationOfRoom values (135,'yes','north')
insert into LocationOfRoom values (121,'yes','south')
insert into LocationOfRoom values (139,'no','north')
insert into LocationOfRoom values (131,'no','East')
insert into LocationOfRoom values (136,'yes','west')
insert into LocationOfRoom values (179,'no','south')
insert into LocationOfRoom values (138,'no','west')
insert into LocationOfRoom values (146,'yes','north')

insert into RoomType values (124,'no','no','yes')
insert into RoomType values (135,'no','yes','no')
insert into RoomType values (121,'yes','no','no')
insert into RoomType values (139,'no','no','yes')
insert into RoomType values (131,'no','no','yes')
insert into RoomType values (136,'no','no','yes')
insert into RoomType values (179,'no','no','yes')
insert into RoomType values (138,'no','yes','no')
insert into RoomType values (146,'no','no','yes')

-- display Total number of customers who booked a single room with sea view option
select count(Firstname)
from LocationOfRoom, customer, RoomType, Room
where seaview='yes' and singleRoom='yes'

Any help would be greatly appreciated, thank you in advance!

CodePudding user response：

I believe the solution for your problem is that you need to use DISTINCT:

SELECT COUNT(DISTINCT(Firstname))
FROM LocationOfRoom, customer, RoomType, Room
WHERE seaview='yes' AND singleRoom='yes'

I have tested it and it retrieves 9 Rooms.

CodePudding user response：

Your FROM clause is missing the join condition for each table. In other words you are getting the cartesian product (every combination of rows) of the tables. Even distinct won't help (it will get the wrong answer). Use join conditions to link the keys of each table to each other. I'll leave this an exercise for you to try out, but this should be enough information to help you out.