Mixing
Repeat data issues SQL
2
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:
Any help would be appreciated.
sql
sql-server tsql inner-join edited Nov 22 '18 at 8:25
a_horse_with_no_name
300k46458550
asked Nov 22 '18 at 7:36
user3618687user3618687
489
|
show 1 more comment
2
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:
Any help would be appreciated.
sql
sql-server tsql inner-join edited Nov 22 '18 at 8:25
a_horse_with_no_name
300k46458550
asked Nov 22 '18 at 7:36
user3618687user3618687
489
2
This does not look like MySQL problem. Are you sure that you are not using SQL Server instead.
– Madhur Bhaiya
Nov 22 '18 at 7:37
1
DON'T mix implicit and explicit joins DO add sample data as text to the question.
– P.Salmon
Nov 22 '18 at 7:38
@MadhurBhaiya You're right, I've corrected the mistake in the title. Thanks
– user3618687
Nov 22 '18 at 7:50
@P.Salmon an answer has already been provided which works but for anyone coming to this in the future I'll add sample data. Thanks
– user3618687
Nov 22 '18 at 7:51
@user3618687 believe it or not this is a very frequent question on this site.
– P.Salmon
Nov 22 '18 at 7:54
|
show 1 more comment
2
2
2
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:
Any help would be appreciated.
sql
sql-server tsql inner-join edited Nov 22 '18 at 8:25
a_horse_with_no_name
300k46458550
asked Nov 22 '18 at 7:36
user3618687user3618687
489
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:
Any help would be appreciated.
sql
sql-server tsql inner-join sql
sql-server tsql inner-join edited Nov 22 '18 at 8:25
a_horse_with_no_name
300k46458550
asked Nov 22 '18 at 7:36
user3618687user3618687
489
edited Nov 22 '18 at 8:25
a_horse_with_no_name
300k46458550
asked Nov 22 '18 at 7:36
user3618687user3618687
489
edited Nov 22 '18 at 8:25
a_horse_with_no_name
300k46458550
edited Nov 22 '18 at 8:25
a_horse_with_no_name
300k46458550
edited Nov 22 '18 at 8:25
a_horse_with_no_name
300k46458550
300k46458550
asked Nov 22 '18 at 7:36
user3618687user3618687
489
asked Nov 22 '18 at 7:36
user3618687user3618687
489
asked Nov 22 '18 at 7:36
user3618687user3618687
489
489
2
This does not look like MySQL problem. Are you sure that you are not using SQL Server instead.
– Madhur Bhaiya
Nov 22 '18 at 7:37
1
DON'T mix implicit and explicit joins DO add sample data as text to the question.
– P.Salmon
Nov 22 '18 at 7:38
@MadhurBhaiya You're right, I've corrected the mistake in the title. Thanks
– user3618687
Nov 22 '18 at 7:50
@P.Salmon an answer has already been provided which works but for anyone coming to this in the future I'll add sample data. Thanks
– user3618687
Nov 22 '18 at 7:51
@user3618687 believe it or not this is a very frequent question on this site.
– P.Salmon
Nov 22 '18 at 7:54
|
show 1 more comment
2
This does not look like MySQL problem. Are you sure that you are not using SQL Server instead.
– Madhur Bhaiya
Nov 22 '18 at 7:37
1
DON'T mix implicit and explicit joins DO add sample data as text to the question.
– P.Salmon
Nov 22 '18 at 7:38
@MadhurBhaiya You're right, I've corrected the mistake in the title. Thanks
– user3618687
Nov 22 '18 at 7:50
@P.Salmon an answer has already been provided which works but for anyone coming to this in the future I'll add sample data. Thanks
– user3618687
Nov 22 '18 at 7:51
@user3618687 believe it or not this is a very frequent question on this site.
– P.Salmon
Nov 22 '18 at 7:54
2
2
This does not look like MySQL problem. Are you sure that you are not using SQL Server instead.
– Madhur Bhaiya
Nov 22 '18 at 7:37
This does not look like MySQL problem. Are you sure that you are not using SQL Server instead.
– Madhur Bhaiya
Nov 22 '18 at 7:37
1
1
DON'T mix implicit and explicit joins DO add sample data as text to the question.
– P.Salmon
Nov 22 '18 at 7:38
DON'T mix implicit and explicit joins DO add sample data as text to the question.
– P.Salmon
Nov 22 '18 at 7:38
@MadhurBhaiya You're right, I've corrected the mistake in the title. Thanks
– user3618687
Nov 22 '18 at 7:50
@MadhurBhaiya You're right, I've corrected the mistake in the title. Thanks
– user3618687
Nov 22 '18 at 7:50
@P.Salmon an answer has already been provided which works but for anyone coming to this in the future I'll add sample data. Thanks
– user3618687
Nov 22 '18 at 7:51
@P.Salmon an answer has already been provided which works but for anyone coming to this in the future I'll add sample data. Thanks
– user3618687
Nov 22 '18 at 7:51
@user3618687 believe it or not this is a very frequent question on this site.
– P.Salmon
Nov 22 '18 at 7:54
@user3618687 believe it or not this is a very frequent question on this site.
– P.Salmon
Nov 22 '18 at 7:54
|
show 1 more comment
1 Answer
1
active
oldest
votes
2
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
answered Nov 22 '18 at 7:44
fa06fa06
14.8k2917
So I needed to join Customers through the Sales table instead of on it's own? It makes sense, that way it's filtered information instead of just the entire table
– user3618687
Nov 22 '18 at 7:49
@user3618687, yes you need to define relation between customer and sales table properly, in your query u didn't define the relationship
– fa06
Nov 22 '18 at 7:50
1
Thanks, as you can tell I am not as versed in SQL as I would like to be. the whole joins and stuff get me a little confused but thank you so much for the help.
– user3618687
Nov 22 '18 at 7:52
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53425969%2frepeat-data-issues-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
2
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
answered Nov 22 '18 at 7:44
fa06fa06
14.8k2917
So I needed to join Customers through the Sales table instead of on it's own? It makes sense, that way it's filtered information instead of just the entire table
– user3618687
Nov 22 '18 at 7:49
@user3618687, yes you need to define relation between customer and sales table properly, in your query u didn't define the relationship
– fa06
Nov 22 '18 at 7:50
1
Thanks, as you can tell I am not as versed in SQL as I would like to be. the whole joins and stuff get me a little confused but thank you so much for the help.
– user3618687
Nov 22 '18 at 7:52
add a comment |
2
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
answered Nov 22 '18 at 7:44
fa06fa06
14.8k2917
So I needed to join Customers through the Sales table instead of on it's own? It makes sense, that way it's filtered information instead of just the entire table
– user3618687
Nov 22 '18 at 7:49
@user3618687, yes you need to define relation between customer and sales table properly, in your query u didn't define the relationship
– fa06
Nov 22 '18 at 7:50
1
Thanks, as you can tell I am not as versed in SQL as I would like to be. the whole joins and stuff get me a little confused but thank you so much for the help.
– user3618687
Nov 22 '18 at 7:52
add a comment |
2
2
2
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
answered Nov 22 '18 at 7:44
fa06fa06
14.8k2917
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
answered Nov 22 '18 at 7:44
fa06fa06
14.8k2917
answered Nov 22 '18 at 7:44
fa06fa06
14.8k2917
answered Nov 22 '18 at 7:44
fa06fa06
14.8k2917
answered Nov 22 '18 at 7:44
fa06fa06
14.8k2917
14.8k2917
So I needed to join Customers through the Sales table instead of on it's own? It makes sense, that way it's filtered information instead of just the entire table
– user3618687
Nov 22 '18 at 7:49
@user3618687, yes you need to define relation between customer and sales table properly, in your query u didn't define the relationship
– fa06
Nov 22 '18 at 7:50
1
Thanks, as you can tell I am not as versed in SQL as I would like to be. the whole joins and stuff get me a little confused but thank you so much for the help.
– user3618687
Nov 22 '18 at 7:52
add a comment |
So I needed to join Customers through the Sales table instead of on it's own? It makes sense, that way it's filtered information instead of just the entire table
– user3618687
Nov 22 '18 at 7:49
@user3618687, yes you need to define relation between customer and sales table properly, in your query u didn't define the relationship
– fa06
Nov 22 '18 at 7:50
1
Thanks, as you can tell I am not as versed in SQL as I would like to be. the whole joins and stuff get me a little confused but thank you so much for the help.
– user3618687
Nov 22 '18 at 7:52
So I needed to join Customers through the Sales table instead of on it's own? It makes sense, that way it's filtered information instead of just the entire table
– user3618687
Nov 22 '18 at 7:49
So I needed to join Customers through the Sales table instead of on it's own? It makes sense, that way it's filtered information instead of just the entire table
– user3618687
Nov 22 '18 at 7:49
@user3618687, yes you need to define relation between customer and sales table properly, in your query u didn't define the relationship
– fa06
Nov 22 '18 at 7:50
@user3618687, yes you need to define relation between customer and sales table properly, in your query u didn't define the relationship
– fa06
Nov 22 '18 at 7:50
1
1
Thanks, as you can tell I am not as versed in SQL as I would like to be. the whole joins and stuff get me a little confused but thank you so much for the help.
– user3618687
Nov 22 '18 at 7:52
Thanks, as you can tell I am not as versed in SQL as I would like to be. the whole joins and stuff get me a little confused but thank you so much for the help.
– user3618687
Nov 22 '18 at 7:52
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53425969%2frepeat-data-issues-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
This does not look like MySQL problem. Are you sure that you are not using SQL Server instead.
– Madhur Bhaiya
Nov 22 '18 at 7:37
1
DON'T mix implicit and explicit joins DO add sample data as text to the question.
– P.Salmon
Nov 22 '18 at 7:38
@MadhurBhaiya You're right, I've corrected the mistake in the title. Thanks
– user3618687
Nov 22 '18 at 7:50
@P.Salmon an answer has already been provided which works but for anyone coming to this in the future I'll add sample data. Thanks
– user3618687
Nov 22 '18 at 7:51
@user3618687 believe it or not this is a very frequent question on this site.
– P.Salmon
Nov 22 '18 at 7:54