How to use SQL join correctly?
I'm using the AdventureWorks database to do some SQL exercices.
I'm trying to transform my code into using the Join statement, but I cannot seem do to it correctly.
This is my code that I originally made to display the custumer name, product name, sales amount as well as quantity and the year when It was bought:
SELECT FirstName, LastName, EnglishProductName, ProductAlternateKey,
SUM(SalesAmount) as sales, sum(OrderQuantity) as Qty, CalendarYear
FROM [AdventureWorksDW2017].[dbo].[FactInternetSales],
[AdventureWorksDW2017].[dbo].[DimProduct],
[AdventureWorksDW2017].[dbo].[DimCustomer],
[AdventureWorksDW2017].[dbo].[DimDate]
WHERE FirstName = 'Julia' And LastName = 'Wright' or
FirstName = 'Cedric' And LastName = 'Ma' or
FirstName = 'David' and LastName = 'Rodriguez'
GROUP BY FirstName, LastName, EnglishProductName, ProductAlternateKey, CalendarYear
ORDER BY CalendarYear DESC, FirstName ASC, LastName
sql
sql-server tsql edited Nov 22 '18 at 13:20
a_horse_with_no_name
296k46451546
asked Nov 22 '18 at 12:27
Luis MontezLuis Montez
83
add a comment |
I'm using the AdventureWorks database to do some SQL exercices.
I'm trying to transform my code into using the Join statement, but I cannot seem do to it correctly.
This is my code that I originally made to display the custumer name, product name, sales amount as well as quantity and the year when It was bought:
SELECT FirstName, LastName, EnglishProductName, ProductAlternateKey,
SUM(SalesAmount) as sales, sum(OrderQuantity) as Qty, CalendarYear
FROM [AdventureWorksDW2017].[dbo].[FactInternetSales],
[AdventureWorksDW2017].[dbo].[DimProduct],
[AdventureWorksDW2017].[dbo].[DimCustomer],
[AdventureWorksDW2017].[dbo].[DimDate]
WHERE FirstName = 'Julia' And LastName = 'Wright' or
FirstName = 'Cedric' And LastName = 'Ma' or
FirstName = 'David' and LastName = 'Rodriguez'
GROUP BY FirstName, LastName, EnglishProductName, ProductAlternateKey, CalendarYear
ORDER BY CalendarYear DESC, FirstName ASC, LastName
sql
sql-server tsql edited Nov 22 '18 at 13:20
a_horse_with_no_name
296k46451546
asked Nov 22 '18 at 12:27
Luis MontezLuis Montez
83
2
If you want to practice joins, you should use thejoinkeyword. Comma separated join style is out of date (by about 30-odd years). Also, you forgot to ask a question.
– HoneyBadger
Nov 22 '18 at 12:32
Note:JOINis an operator, not a statement.
– joop
Nov 22 '18 at 12:32
add a comment |
I'm using the AdventureWorks database to do some SQL exercices.
I'm trying to transform my code into using the Join statement, but I cannot seem do to it correctly.
This is my code that I originally made to display the custumer name, product name, sales amount as well as quantity and the year when It was bought:
SELECT FirstName, LastName, EnglishProductName, ProductAlternateKey,
SUM(SalesAmount) as sales, sum(OrderQuantity) as Qty, CalendarYear
FROM [AdventureWorksDW2017].[dbo].[FactInternetSales],
[AdventureWorksDW2017].[dbo].[DimProduct],
[AdventureWorksDW2017].[dbo].[DimCustomer],
[AdventureWorksDW2017].[dbo].[DimDate]
WHERE FirstName = 'Julia' And LastName = 'Wright' or
FirstName = 'Cedric' And LastName = 'Ma' or
FirstName = 'David' and LastName = 'Rodriguez'
GROUP BY FirstName, LastName, EnglishProductName, ProductAlternateKey, CalendarYear
ORDER BY CalendarYear DESC, FirstName ASC, LastName
sql
sql-server tsql edited Nov 22 '18 at 13:20
a_horse_with_no_name
296k46451546
asked Nov 22 '18 at 12:27
Luis MontezLuis Montez
83
I'm using the AdventureWorks database to do some SQL exercices.
I'm trying to transform my code into using the Join statement, but I cannot seem do to it correctly.
This is my code that I originally made to display the custumer name, product name, sales amount as well as quantity and the year when It was bought:
SELECT FirstName, LastName, EnglishProductName, ProductAlternateKey,
SUM(SalesAmount) as sales, sum(OrderQuantity) as Qty, CalendarYear
FROM [AdventureWorksDW2017].[dbo].[FactInternetSales],
[AdventureWorksDW2017].[dbo].[DimProduct],
[AdventureWorksDW2017].[dbo].[DimCustomer],
[AdventureWorksDW2017].[dbo].[DimDate]
WHERE FirstName = 'Julia' And LastName = 'Wright' or
FirstName = 'Cedric' And LastName = 'Ma' or
FirstName = 'David' and LastName = 'Rodriguez'
GROUP BY FirstName, LastName, EnglishProductName, ProductAlternateKey, CalendarYear
ORDER BY CalendarYear DESC, FirstName ASC, LastName
sql
sql-server tsql sql
sql-server tsql edited Nov 22 '18 at 13:20
a_horse_with_no_name
296k46451546
asked Nov 22 '18 at 12:27
Luis MontezLuis Montez
83
edited Nov 22 '18 at 13:20
a_horse_with_no_name
296k46451546
asked Nov 22 '18 at 12:27
Luis MontezLuis Montez
83
edited Nov 22 '18 at 13:20
a_horse_with_no_name
296k46451546
edited Nov 22 '18 at 13:20
a_horse_with_no_name
296k46451546
edited Nov 22 '18 at 13:20
a_horse_with_no_name
296k46451546
296k46451546
asked Nov 22 '18 at 12:27
Luis MontezLuis Montez
83
asked Nov 22 '18 at 12:27
Luis MontezLuis Montez
83
asked Nov 22 '18 at 12:27
Luis MontezLuis Montez
83
83
2
If you want to practice joins, you should use thejoinkeyword. Comma separated join style is out of date (by about 30-odd years). Also, you forgot to ask a question.
– HoneyBadger
Nov 22 '18 at 12:32
Note:JOINis an operator, not a statement.
– joop
Nov 22 '18 at 12:32
add a comment |
2
If you want to practice joins, you should use thejoinkeyword. Comma separated join style is out of date (by about 30-odd years). Also, you forgot to ask a question.
– HoneyBadger
Nov 22 '18 at 12:32
Note:JOINis an operator, not a statement.
– joop
Nov 22 '18 at 12:32
2
2
If you want to practice joins, you should use the
join keyword. Comma separated join style is out of date (by about 30-odd years). Also, you forgot to ask a question.– HoneyBadger
Nov 22 '18 at 12:32
If you want to practice joins, you should use the
join keyword. Comma separated join style is out of date (by about 30-odd years). Also, you forgot to ask a question.– HoneyBadger
Nov 22 '18 at 12:32
Note:
JOIN is an operator, not a statement.– joop
Nov 22 '18 at 12:32
Note:
JOIN is an operator, not a statement.– joop
Nov 22 '18 at 12:32
add a comment |
1 Answer
1
active
oldest
votes
You need to know what columns in each table relate to each other.
(We can't tell you, because we can't see your database's design.)
For example...
SELECT
FirstName, LastName, EnglishProductName, ProductAlternateKey,
SUM(SalesAmount) as sales, sum(OrderQuantity) as Qty, CalendarYear
FROM
[AdventureWorksDW2017].[dbo].[FactInternetSales]
INNER JOIN
[AdventureWorksDW2017].[dbo].[DimProduct]
ON DimProduct.ProductID = FactInternetSale.ProductID
INNER JOIN
[AdventureWorksDW2017].[dbo].[DimCustomer]
ON DimCustomer.CustomerID = FactInternetSale.CustomerID
INNER JOIN
[AdventureWorksDW2017].[dbo].[DimDate]
ON DimDate.DateID = FactInternetSale.DateID
WHERE
(FirstName = 'Julia' AND LastName = 'Wright' ) or
(FirstName = 'Cedric' AND LastName = 'Ma' ) or
(FirstName = 'David' AND LastName = 'Rodriguez')
GROUP BY
FirstName, LastName, EnglishProductName, ProductAlternateKey, CalendarYear
ORDER BY
CalendarYear DESC, FirstName ASC, LastName
Be careful to to put the table names in front of the column names. Like I did in the join clauses. I can infer FirstName comes from DimCustomer but DimCustomer.FirstName makes it explicit and robust.
answered Nov 22 '18 at 12:38
MatBailieMatBailie
59.3k1475110
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
});
}
});
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%2f53431024%2fhow-to-use-sql-join-correctly%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
You need to know what columns in each table relate to each other.
(We can't tell you, because we can't see your database's design.)
For example...
SELECT
FirstName, LastName, EnglishProductName, ProductAlternateKey,
SUM(SalesAmount) as sales, sum(OrderQuantity) as Qty, CalendarYear
FROM
[AdventureWorksDW2017].[dbo].[FactInternetSales]
INNER JOIN
[AdventureWorksDW2017].[dbo].[DimProduct]
ON DimProduct.ProductID = FactInternetSale.ProductID
INNER JOIN
[AdventureWorksDW2017].[dbo].[DimCustomer]
ON DimCustomer.CustomerID = FactInternetSale.CustomerID
INNER JOIN
[AdventureWorksDW2017].[dbo].[DimDate]
ON DimDate.DateID = FactInternetSale.DateID
WHERE
(FirstName = 'Julia' AND LastName = 'Wright' ) or
(FirstName = 'Cedric' AND LastName = 'Ma' ) or
(FirstName = 'David' AND LastName = 'Rodriguez')
GROUP BY
FirstName, LastName, EnglishProductName, ProductAlternateKey, CalendarYear
ORDER BY
CalendarYear DESC, FirstName ASC, LastName
Be careful to to put the table names in front of the column names. Like I did in the join clauses. I can infer FirstName comes from DimCustomer but DimCustomer.FirstName makes it explicit and robust.
answered Nov 22 '18 at 12:38
MatBailieMatBailie
59.3k1475110
add a comment |
You need to know what columns in each table relate to each other.
(We can't tell you, because we can't see your database's design.)
For example...
SELECT
FirstName, LastName, EnglishProductName, ProductAlternateKey,
SUM(SalesAmount) as sales, sum(OrderQuantity) as Qty, CalendarYear
FROM
[AdventureWorksDW2017].[dbo].[FactInternetSales]
INNER JOIN
[AdventureWorksDW2017].[dbo].[DimProduct]
ON DimProduct.ProductID = FactInternetSale.ProductID
INNER JOIN
[AdventureWorksDW2017].[dbo].[DimCustomer]
ON DimCustomer.CustomerID = FactInternetSale.CustomerID
INNER JOIN
[AdventureWorksDW2017].[dbo].[DimDate]
ON DimDate.DateID = FactInternetSale.DateID
WHERE
(FirstName = 'Julia' AND LastName = 'Wright' ) or
(FirstName = 'Cedric' AND LastName = 'Ma' ) or
(FirstName = 'David' AND LastName = 'Rodriguez')
GROUP BY
FirstName, LastName, EnglishProductName, ProductAlternateKey, CalendarYear
ORDER BY
CalendarYear DESC, FirstName ASC, LastName
Be careful to to put the table names in front of the column names. Like I did in the join clauses. I can infer FirstName comes from DimCustomer but DimCustomer.FirstName makes it explicit and robust.
answered Nov 22 '18 at 12:38
MatBailieMatBailie
59.3k1475110
add a comment |
You need to know what columns in each table relate to each other.
(We can't tell you, because we can't see your database's design.)
For example...
SELECT
FirstName, LastName, EnglishProductName, ProductAlternateKey,
SUM(SalesAmount) as sales, sum(OrderQuantity) as Qty, CalendarYear
FROM
[AdventureWorksDW2017].[dbo].[FactInternetSales]
INNER JOIN
[AdventureWorksDW2017].[dbo].[DimProduct]
ON DimProduct.ProductID = FactInternetSale.ProductID
INNER JOIN
[AdventureWorksDW2017].[dbo].[DimCustomer]
ON DimCustomer.CustomerID = FactInternetSale.CustomerID
INNER JOIN
[AdventureWorksDW2017].[dbo].[DimDate]
ON DimDate.DateID = FactInternetSale.DateID
WHERE
(FirstName = 'Julia' AND LastName = 'Wright' ) or
(FirstName = 'Cedric' AND LastName = 'Ma' ) or
(FirstName = 'David' AND LastName = 'Rodriguez')
GROUP BY
FirstName, LastName, EnglishProductName, ProductAlternateKey, CalendarYear
ORDER BY
CalendarYear DESC, FirstName ASC, LastName
Be careful to to put the table names in front of the column names. Like I did in the join clauses. I can infer FirstName comes from DimCustomer but DimCustomer.FirstName makes it explicit and robust.
answered Nov 22 '18 at 12:38
MatBailieMatBailie
59.3k1475110
You need to know what columns in each table relate to each other.
(We can't tell you, because we can't see your database's design.)
For example...
SELECT
FirstName, LastName, EnglishProductName, ProductAlternateKey,
SUM(SalesAmount) as sales, sum(OrderQuantity) as Qty, CalendarYear
FROM
[AdventureWorksDW2017].[dbo].[FactInternetSales]
INNER JOIN
[AdventureWorksDW2017].[dbo].[DimProduct]
ON DimProduct.ProductID = FactInternetSale.ProductID
INNER JOIN
[AdventureWorksDW2017].[dbo].[DimCustomer]
ON DimCustomer.CustomerID = FactInternetSale.CustomerID
INNER JOIN
[AdventureWorksDW2017].[dbo].[DimDate]
ON DimDate.DateID = FactInternetSale.DateID
WHERE
(FirstName = 'Julia' AND LastName = 'Wright' ) or
(FirstName = 'Cedric' AND LastName = 'Ma' ) or
(FirstName = 'David' AND LastName = 'Rodriguez')
GROUP BY
FirstName, LastName, EnglishProductName, ProductAlternateKey, CalendarYear
ORDER BY
CalendarYear DESC, FirstName ASC, LastName
Be careful to to put the table names in front of the column names. Like I did in the join clauses. I can infer FirstName comes from DimCustomer but DimCustomer.FirstName makes it explicit and robust.
answered Nov 22 '18 at 12:38
MatBailieMatBailie
59.3k1475110
answered Nov 22 '18 at 12:38
MatBailieMatBailie
59.3k1475110
answered Nov 22 '18 at 12:38
MatBailieMatBailie
59.3k1475110
answered Nov 22 '18 at 12:38
MatBailieMatBailie
59.3k1475110
59.3k1475110
add a comment |
add a comment |
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.
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%2f53431024%2fhow-to-use-sql-join-correctly%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
If you want to practice joins, you should use the
joinkeyword. Comma separated join style is out of date (by about 30-odd years). Also, you forgot to ask a question.– HoneyBadger
Nov 22 '18 at 12:32
Note:
JOINis an operator, not a statement.– joop
Nov 22 '18 at 12:32