August 20, 2012 at 8:29 pm
I have 2 tables in my database and am RIGHT JOINING the tables and it is acting like an inner join
SELECT [DealerCode]
,iis.[ForecastSegmentId]
,fs.ForecastSegmentId
,[Name]
FROM [dbo].[Sales] iis
RIGHT OUTER JOIN dbo.Forecast fs ON fs.ForecastId = iis.ForecastId
WHERE dealercode = '11'
GROUP BY DealerCode,iis.ForecastId,fs.ForecastId,Name
And I come out with all the rows from the 1st table (Sales).
DealerCodeForecastIdForecastIdName
001144Dual
001155Mortgage
001166Retail
But in the 2nd table have 3 more records (7, 8, and 9).
Where are those???
I tried this with Left and Right Joins as well as Full.
Still didn't get the extra records.
This should be pretty straight forward.
Any ideas what I am missing???
Thanks,
Tom
August 20, 2012 at 8:31 pm
Actually, the topic should say RIGHT JOIN and not LEFT JOIN.
August 20, 2012 at 8:51 pm
I assume, DealerCode belongs to table [dbo].[Sales].
SELECT [DealerCode]
,iis.[ForecastSegmentId]
,fs.ForecastSegmentId
,[Name]
FROM [dbo].[Sales] iis
RIGHT OUTER JOIN dbo.Forecast fs ON fs.ForecastId = iis.ForecastId and iis.dealercode = '11'
GROUP BY iis.DealerCode,iis.ForecastId,fs.ForecastId,Name
_____________
Code for TallyGenerator
August 20, 2012 at 9:18 pm
tshad (8/20/2012)
I have 2 tables in my database and am RIGHT JOINING the tables and it is acting like an inner joinSELECT [DealerCode]
,iis.[ForecastSegmentId]
,fs.ForecastSegmentId
,[Name]
FROM [dbo].[Sales] iis
RIGHT OUTER JOIN dbo.Forecast fs ON fs.ForecastId = iis.ForecastId
WHERE dealercode = '11'
GROUP BY DealerCode,iis.ForecastId,fs.ForecastId,Name
And I come out with all the rows from the 1st table (Sales).
DealerCodeForecastIdForecastIdName
001144Dual
001155Mortgage
001166Retail
But in the 2nd table have 3 more records (7, 8, and 9).
Where are those???
I tried this with Left and Right Joins as well as Full.
Still didn't get the extra records.
This should be pretty straight forward.
Any ideas what I am missing???
Thanks,
Tom
Hi Tom,
Can you please provide DDL and sample records of the tables ?
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
August 20, 2012 at 9:37 pm
Actually, this is a stripped down version of the actual query I was using so it is a little difficult to do that.
But I did figure out my issue, just not sure how to deal with it.
The where clause is causing the problem. The Join is done fine but the the where clause filters out anything that doesn't have DealerCode ="11".
The lines that have the null for a DealerCode, which the extra lines would have, were also filtered out.
I needed to change Where clause to:
WHERE dealercode = '11' OR dealerCode IS NULL.
That works fine with one dealercode but if I have multiple dealercodes, it doesn't work as I get 3 records even if another dealercode is missing others so I can't tell which dealercode the extra records belong to.
Thanks,
Tom
August 20, 2012 at 9:59 pm
tshad (8/20/2012)
Actually, this is a stripped down version of the actual query I was using so it is a little difficult to do that.But I did figure out my issue, just not sure how to deal with it.
The where clause is causing the problem. The Join is done fine but the the where clause filters out anything that doesn't have DealerCode ="11".
The lines that have the null for a DealerCode, which the extra lines would have, were also filtered out.
I needed to change Where clause to:
WHERE dealercode = '11' OR dealerCode IS NULL.
That works fine with one dealercode but if I have multiple dealercodes, it doesn't work as I get 3 records even if another dealercode is missing others so I can't tell which dealercode the extra records belong to.
Thanks,
Tom
Put this, dealercode = '11' , in with the join criteria for the right outer join instead of in the WHERE clause. Let us know how that works.
August 20, 2012 at 10:10 pm
Ok..so if am not wrong then dealer code is in the first table and you are looking for all the records in second table.
If this is the case then I am confused by your statement.
As the extra records do not belongs to any dealer code these are records(having dealer code as NULL) which do not found any match in first table.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
August 21, 2012 at 1:06 am
I made some changes and created a couple of tables to show the issue.
Either way (change where clause or Join with the test for the DealerCode) solves the first issue. Here is the schema and inserts as well as the queries. These tables are just quick and dirty for illustrations only.
CREATE TABLE [dbo].[Sales](
[SalesID] [int] NULL,
[DealerCode] [varchar](15) NULL,
[ForecastID] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Forecast](
[ForecastId] [int] NULL,
[Name] [varchar](50) NULL
) ON [PRIMARY]
INSERT Sales(SalesID, DealerCode, ForecastID) VALUES(1, '11', 4)
INSERT Sales(SalesID, DealerCode, ForecastID) VALUES(2, '11', 5)
INSERT Sales(SalesID, DealerCode, ForecastID) VALUES(3, '11', 6)
INSERT Forecast(ForecastID, Name) VALUES(4, 'Dual')
INSERT Forecast(ForecastID, Name) VALUES(5, 'Mortgage')
INSERT Forecast(ForecastID, Name) VALUES(6, 'Retail')
INSERT Forecast(ForecastID, Name) VALUES(7, 'Wholesale')
INSERT Forecast(ForecastID, Name) VALUES(8, 'Region')
INSERT Forecast(ForecastID, Name) VALUES(9, 'Market')
SELECT DealerCode
,s.ForecastId
,fc.ForecastId
,Name
FROM [Sales] s
RIGHT OUTER JOIN dbo.Forecast fc ON fc.ForecastId = s.ForecastId
WHERE dealercode = '11' OR dealerCode IS NULL
GROUP BY DealerCode,s.ForecastId,fc.ForecastId,Name
ORDER BY DealerCode
Results:
DealerCodeForecastIdForecastIdName
NULLNULL7Wholesale
NULLNULL8Region
NULLNULL9Market
1144Dual
1155Mortgage
1166Retail
But now if I add a few more records with a couple more Dealers, I don’t get the results I am looking for:
INSERT Sales(SalesID, DealerCode, ForecastID) VALUES(1, '5', 4)
INSERT Sales(SalesID, DealerCode, ForecastID) VALUES(2, '5', 5)
INSERT Sales(SalesID, DealerCode, ForecastID) VALUES(3, '5', 6)
INSERT Sales(SalesID, DealerCode, ForecastID) VALUES(1, '5', 7)
INSERT Sales(SalesID, DealerCode, ForecastID) VALUES(2, '5', 8)
INSERT Sales(SalesID, DealerCode, ForecastID) VALUES(3, '8', 4)
The new query:
SELECT DealerCode
,s.ForecastId
,fc.ForecastId
,Name
FROM [Sales] s
RIGHT OUTER JOIN dbo.Forecast fc ON fc.ForecastId = s.ForecastId
WHERE dealercode in ('5','8','11') OR dealerCode IS NULL
GROUP BY DealerCode,s.ForecastId,fc.ForecastId,Name
ORDER BY DealerCode
And the results:
DealerCodeForecastIdForecastIdName
NULLNULL9Market
1144Dual
1155Mortgage
1166Retail
544Dual
555Mortgage
566Retail
577Wholesale
588Region
844Dual
Here I have 1 null record for the whole result set but I want to show what each dealers forecast records are as well as the forecast records that each dealer doesn’t have.
So Dealer 11 should have 3 null records as before. Dealer 5 should have 1 null and Dealer 8 should have 4 null records and have them together with the dealer.
Something like:
DealerCodeForecastIdForecastIdName
NULLNULL7Wholesale
NULLNULL8Region
NULLNULL9Market
1144Dual
1155Mortgage
1166Retail
NULLNULL9Market
544Dual
555Mortgage
566Retail
577Wholesale
588Region
NULLNULL7Wholesale
NULLNULL8Region
NULLNULL9Market
NULLNULL8Region
844Dual
Thanks,
Tom
August 21, 2012 at 2:40 am
The closest I can come is this: -
SELECT s.DealerCode, s.ForecastID, fc.ForecastId, fc.Name
FROM [dbo].[Forecast] fc
OUTER APPLY (SELECT code
FROM (VALUES('5'),('8'),('11'))a(code)
) c
LEFT OUTER JOIN [dbo].[Sales] s ON s.DealerCode = c.code AND s.ForecastID = fc.ForecastID
ORDER BY fc.Name;
I don't fully understand your requirements. If what you want is to have 1 record per dealer whether or not they have a forecast and you're filtering to three dealer codes then surely the most you can have is 3 records per forecast?
Here's the sample data I used (edited to make it easier to read for me): -
SET NOCOUNT ON;
--Create tables
CREATE TABLE [dbo].[Sales] ([SalesID] [int] NULL, [DealerCode] [varchar](15) NULL, [ForecastID] [int] NULL) ON [PRIMARY];
CREATE TABLE [dbo].[Forecast] ([ForecastId] [int] NULL, [Name] [varchar](50) NULL) ON [PRIMARY];
--Insert sample data
INSERT INTO [dbo].[Sales]
SELECT SalesID, DealerCode, ForecastID
FROM (VALUES(1, '11', 4),(2, '11', 5),(3, '11', 6),(1, '5', 4),
(2, '5', 5),(3, '5', 6),(1, '5', 7),(2, '5', 8),
(3, '8', 4)
)a(SalesID, DealerCode, ForecastID);
INSERT INTO [dbo].[Forecast]
SELECT ForecastID, Name
FROM (VALUES(4, 'Dual'),(5, 'Mortgage'),(6, 'Retail'),(7, 'Wholesale'),
(8, 'Region'),(9, 'Market')
)a(ForecastID, Name);
--Expected result
SELECT DealerCode, SalesForecastId, ForecastForecastId, Name
FROM (VALUES(NULL,NULL,7,'Wholesale'),(NULL,NULL,8,'Region'),(NULL,NULL,9,'Market'),
(11,4,4,'Dual'),(11,5,5,'Mortgage'),(11,6,6,'Retail'),(NULL,NULL,9,'Market'),
(5,4,4,'Dual'),(5,5,5,'Mortgage'),(5,6,6,'Retail'),(5,7,7,'Wholesale'),
(5,8,8,'Region'),(NULL,NULL,7,'Wholesale'),(NULL,NULL,8,'Region'),
(NULL,NULL,9,'Market'),(NULL,NULL,8,'Region'),(8,4,4,'Dual')
)a(DealerCode, SalesForecastId, ForecastForecastId, Name)
ORDER BY Name;
Here's my attempt: -
--Actual Query
SELECT s.DealerCode, s.ForecastID, fc.ForecastId, fc.Name
FROM [dbo].[Forecast] fc
OUTER APPLY (SELECT code
FROM (VALUES('5'),('8'),('11'))a(code)
) c
LEFT OUTER JOIN [dbo].[Sales] s ON s.DealerCode = c.code AND s.ForecastID = fc.ForecastID
ORDER BY fc.Name;
Results in: -
DealerCode ForecastID ForecastId Name
--------------- ----------- ----------- --------------------------------------------------
5 4 4 Dual
8 4 4 Dual
11 4 4 Dual
NULL NULL 9 Market
NULL NULL 9 Market
NULL NULL 9 Market
5 5 5 Mortgage
NULL NULL 5 Mortgage
11 5 5 Mortgage
5 8 8 Region
NULL NULL 8 Region
NULL NULL 8 Region
5 6 6 Retail
NULL NULL 6 Retail
11 6 6 Retail
5 7 7 Wholesale
NULL NULL 7 Wholesale
NULL NULL 7 Wholesale
But you have your expected result as : -
DealerCode SalesForecastId ForecastForecastId Name
----------- --------------- ------------------ ---------
5 4 4 Dual
11 4 4 Dual
8 4 4 Dual
NULL NULL 9 Market
NULL NULL 9 Market
NULL NULL 9 Market
5 5 5 Mortgage
11 5 5 Mortgage
NULL NULL 8 Region
NULL NULL 8 Region
5 8 8 Region
NULL NULL 8 Region
5 6 6 Retail
11 6 6 Retail
NULL NULL 7 Wholesale
5 7 7 Wholesale
NULL NULL 7 Wholesale
If you can explain the expected result, I'm sure that someone will be able to help.
August 21, 2012 at 5:58 am
SELECT *
FROM
(SELECT DealerCode FROM Sales GROUP BY DealerCode) m
CROSS JOIN Forecast fc
LEFT JOIN [Sales] s on s.DealerCode = m.DealerCode AND s.ForecastID = fc.ForecastId
ORDER BY m.DealerCode
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 23, 2012 at 12:22 am
That worked really good.
But I couldn't get it to work with a many to many table in the middle
Here is the schema:
CREATE TABLE [dbo].[Dealers](
[DealerID] [int] NULL,
[DealerCode] [varchar](15) NOT NULL,
[DealerName] [varchar](50) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Forecast](
[ForecastId] [int] NULL,
[Name] [varchar](50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[DealerForecastMap](
[DealerForecastMapID] [int] NULL,
[DealerID] [varchar](15) NULL,
[ForecastID] [int] NULL
) ON [PRIMARY]
INSERT Dealers(DealerID, DealerCode, DealerName) VALUES(1, '11', 'Sears')
INSERT Dealers(DealerID, DealerCode, DealerName) VALUES(2, '05', 'Pick And Save')
INSERT Dealers(DealerID, DealerCode, DealerName) VALUES(3, '08', 'Pennys')
INSERT Forecast(ForecastID, Name) VALUES(4, 'Dual')
INSERT Forecast(ForecastID, Name) VALUES(5, 'Mortgage')
INSERT Forecast(ForecastID, Name) VALUES(6, 'Retail')
INSERT Forecast(ForecastID, Name) VALUES(7, 'Wholesale')
INSERT Forecast(ForecastID, Name) VALUES(8, 'Region')
INSERT Forecast(ForecastID, Name) VALUES(9, 'Market')
INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(1, 1, 4)
INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(2, 1, 5)
INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(3, 1, 6)
INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(4, 2, 4)
INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(5, 2, 5)
INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(6, 2, 6)
INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(7, 2, 7)
INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(8, 2, 8)
INSERT DealerForecastMap(DealerForecastMapID, DealerID, ForecastID) VALUES(9, 3, 4)
SELECT d.DealerID, d.DealerCode, DealerName, f.ForecastID, Name
FROM Dealers d
JOIN DealerForecastMap dfm
ON d.DealerID = dfm.DealerID
JOIN Forecast f
ON f.ForecastId = dfm.ForecastID
And the results:
DealerIDDealerCodeDealerNameForecastIDName
111Sears4Dual
111Sears5Mortgage
111Sears6Retail
205Pick And Save4Dual
205Pick And Save5Mortgage
205Pick And Save6Retail
205Pick And Save7Wholesale
205Pick And Save8Region
308Pennys4Dual
How would I show the results with each dealer showing all the Forecasts including the forecasts each one is missing?
Thanks,
Tom
August 23, 2012 at 1:29 am
The same way we've already explained.
SELECT *
FROM (SELECT DealerID, DealerCode, DealerName, ForecastId, Name
FROM Dealers d
CROSS JOIN Forecast f) df
LEFT OUTER JOIN DealerForecastMap m ON df.ForecastId = m.ForecastId AND df.DealerID = m.DealerID;
Results in: -
DealerID DealerCode DealerName ForecastId Name DealerForecastMapID DealerID ForecastId
----------- --------------- -------------------------------------------------- ----------- -------------------------------------------------- ------------------- --------------- -----------
1 11 Sears 4 Dual 1 1 4
1 11 Sears 5 Mortgage 2 1 5
1 11 Sears 6 Retail 3 1 6
1 11 Sears 7 Wholesale NULL NULL NULL
1 11 Sears 8 Region NULL NULL NULL
1 11 Sears 9 Market NULL NULL NULL
2 05 Pick And Save 4 Dual 4 2 4
2 05 Pick And Save 5 Mortgage 5 2 5
2 05 Pick And Save 6 Retail 6 2 6
2 05 Pick And Save 7 Wholesale 7 2 7
2 05 Pick And Save 8 Region 8 2 8
2 05 Pick And Save 9 Market NULL NULL NULL
3 08 Pennys 4 Dual 9 3 4
3 08 Pennys 5 Mortgage NULL NULL NULL
3 08 Pennys 6 Retail NULL NULL NULL
3 08 Pennys 7 Wholesale NULL NULL NULL
3 08 Pennys 8 Region NULL NULL NULL
3 08 Pennys 9 Market NULL NULL NULL
August 23, 2012 at 2:01 am
Actually, it isn't exactly the same way.
I was trying to figure out how to get your way to work with a middle table and couldn't get it to work.
You're way works but it is different.
In the query you have the CROSS JOIN in the Derived Table (df)
SELECT *
FROM (SELECT DealerID, DealerCode, DealerName, ForecastId, Name
FROM Dealers d
CROSS JOIN Forecast f) df
LEFT OUTER JOIN DealerForecastMap m
ON df.ForecastId = m.ForecastId AND df.DealerID = m.DealerID;
In the old query, you have the CROSS JOIN outside of the Derived table (m).
SELECT *
FROM
(SELECT DealerCode FROM Sales GROUP BY DealerCode) m
CROSS JOIN Forecast fc
LEFT JOIN [Sales] s
on s.DealerCode = m.DealerCode AND s.ForecastID = fc.ForecastId
ORDER BY m.DealerCode
I am not sure of the difference but I do know I could not seem to get my query with the middle table to work as it was set up for my other example that had just 2 tables.
Not sure why yet, but will look at it more closely.
Thanks,
Tom
August 23, 2012 at 2:18 am
tshad (8/23/2012)
Actually, it isn't exactly the same way.I was trying to figure out how to get your way to work with a middle table and couldn't get it to work.
You're way works but it is different.
In the query you have the CROSS JOIN in the Derived Table (df)
SELECT *
FROM (SELECT DealerID, DealerCode, DealerName, ForecastId, Name
FROM Dealers d
CROSS JOIN Forecast f) df
LEFT OUTER JOIN DealerForecastMap m
ON df.ForecastId = m.ForecastId AND df.DealerID = m.DealerID;
In the old query, you have the CROSS JOIN outside of the Derived table (m).
SELECT *
FROM
(SELECT DealerCode FROM Sales GROUP BY DealerCode) m
CROSS JOIN Forecast fc
LEFT JOIN [Sales] s
on s.DealerCode = m.DealerCode AND s.ForecastID = fc.ForecastId
ORDER BY m.DealerCode
I am not sure of the difference but I do know I could not seem to get my query with the middle table to work as it was set up for my other example that had just 2 tables.
Not sure why yet, but will look at it more closely.
Thanks,
Tom
Hi Tom
In both queries the intent is to build an intermediate table which is a cross of DealerID and ForecastID, to which you can LEFT JOIN other tables. This ensures that you're starting with the whole picture - every dealer has every forecast.
The structural difference between the two queries is to accommodate the changes in the sample tables and data you've provided.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 23, 2012 at 2:40 am
tshad (8/23/2012)
Actually, it isn't exactly the same way.I was trying to figure out how to get your way to work with a middle table and couldn't get it to work.
You're way works but it is different.
You've got a little defensive there and having re-read what I wrote I can see why. I hadn't intended what I wrote to sound like I was accusing you of not paying attention, I simply wanted to draw your attention to the fact that the logic behind the idea is identical in both your previous question and your new question. As Chris explained (far better than I), the difference in the structure of the query is to accommodate the changes to the schema that you supplied with your question.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply