Search code examples
sqlsql-servert-sqlinner-join

Repeat data issues SQL

Had a quick browse to see if any previous questions related to my issue, couldn't see any.

Basically I'm doing this database for my online Cert IV course and if I weren't completely stuck (as I have been for the past few months) I wouldn't be asking for major help on this

I've got an Antiques database that is supposed to show the Customer Name, Sales Date, Product Name and Sales Price and only list the items that were sold between 2 dates and order them by said dates. Nothing I do results in not having repeat data

I've got 4 tables for this particular query Customers, Sales and Products, Tables are set up like this: 

CREATE TABLE [dbo].[Customers](
[CustID] [int] IDENTITY(1,1) NOT NULL,
[firstName] [varchar](50) NOT NULL,
[lastName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[Sales](
[SalesNo] [int] IDENTITY(1,1) NOT NULL,
[CustID] [int] NOT NULL,
[salesDate] [date] NOT NULL,
CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[Products](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[prodName] [varchar](50) NOT NULL,
[prodYear] [int] NOT NULL,
[prodType] [varchar](50) NOT NULL,
[salesPrice] [money] NOT NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[ProductSales](
[ProductID] [int] NOT NULL,
[SalesNo] [int] NOT NULL

My query looks like this

SELECT (Customers.firstName + ' ' + Customers.lastName) AS Customers_Name,
    Sales.salesDate, Products.prodName, Sales.salesPrice
FROM Customers, ProductSales JOIN Products ON ProductSales.ProductID = Products.ProductID
                             JOIN Sales ON ProductSales.SalesNo = Sales.SalesNo
WHERE Sales.salesDate BETWEEN '2016-06-03' AND '2016-06-06'
ORDER BY Sales.salesDate

This is what shows up when I run this query:

Queried Data

Any help would be appreciated.

Solution

  • Try below - you need to join customer table properly

    SELECT (Customers.firstName + ' ' + Customers.lastName) AS Customers_Name,
    Sales.salesDate, Products.prodName, Sales.salesPrice
FROM ProductSales JOIN Products ON ProductSales.ProductID = Products.ProductID
                             JOIN Sales ON ProductSales.SalesNo = Sales.SalesNo
JOIN Customers on  Sales.[CustID]=Customers.[CustID]
WHERE Sales.salesDate BETWEEN '2016-06-03' AND '2016-06-06'
ORDER BY Sales.salesDate