December 19, 2011 at 1:40 am
Consider i have two tables SourceCode and SC_Charts
Let say, I have 2 Sourcing charts ChartA and ChartB for 2011. In ChartA, I have selected SC1, SC3 and SC4. In chartB, I have SC2,SC4,SC5
Output of the view should be
Table - SourceCode
SourceCodeSRCValueDescYear
SC13Pas0
SC221GL0
SC3101TRT#12011
SC4102TRT#22011
SC5103TRT#32011
Table - SC_Charts
YearSC chartSourceCode
2011ChartASC1
2011ChartASC3
2011ChartASC4
2011ChartBSC2
2011chartBSC4
2011ChartBSC5
2012ChartXSC1
2012ChartXSC2
Output
YearSourcing_Chart_IDSRC_IDSRC_ValueDescriptionIsSourceCodeExistInSourcingChart
2011ChartASC13Pas1
2011ChartASC221GL0
2011ChartASC3101TRT#11
2011ChartASC4102TRT#21
2011ChartASC5103Pas0
2011ChartBSC13Pas0
2011ChartBSC221GL1
2011ChartBSC3101TRT#10
2011ChartBSC4102TRT#21
2011ChartBSC5103TRT#31
For a given Sourcing chart, if the 'valid source codes' is in the chart then return IsSourceCodeExistInSourcingChart = 1 else 0
Please can anyone say which would be the best possible join can be used to get the task.
Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com
December 19, 2011 at 1:52 am
Hi Sami
Can you change your data samples to DDL please? CTREATE TABLE and INSERTs. If you're not sure how to do it, read the link in my sig (please read this)
Cheers
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 19, 2011 at 3:19 am
CREATE TABLE [dbo].[SourceCode](
[SourceCode] [varchar](50) NOT NULL,
[SRCvalue] [varchar](50) NULL,
[Description] [varchar](50) NULL,
[Years] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
INSERT [dbo].[SourceCode] ([SourceCode], [SRCvalue], [Description], [Years]) VALUES (N'SC1', N'3', N'PAS', 0)
INSERT [dbo].[SourceCode] ([SourceCode], [SRCvalue], [Description], [Years]) VALUES (N'SC2', N'21', N'GL', 0)
INSERT [dbo].[SourceCode] ([SourceCode], [SRCvalue], [Description], [Years]) VALUES (N'SC3', N'101', N'TRT1', 2011)
INSERT [dbo].[SourceCode] ([SourceCode], [SRCvalue], [Description], [Years]) VALUES (N'SC4', N'102', N'TRT2', 2011)
INSERT [dbo].[SourceCode] ([SourceCode], [SRCvalue], [Description], [Years]) VALUES (N'SC5', N'103', N'TRT3', 2011)
CREATE TABLE [dbo].[Relation](
[ChartID] [varchar](50) NULL,
[SourceCode] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
INSERT [dbo].[Relation] ([ChartID], [SourceCode]) VALUES (N'1', N'SC1')
INSERT [dbo].[Relation] ([ChartID], [SourceCode]) VALUES (N'1', N'SC3')
INSERT [dbo].[Relation] ([ChartID], [SourceCode]) VALUES (N'1', N'SC4')
INSERT [dbo].[Relation] ([ChartID], [SourceCode]) VALUES (N'2', N'SC2')
INSERT [dbo].[Relation] ([ChartID], [SourceCode]) VALUES (N'2', N'SC4')
INSERT [dbo].[Relation] ([ChartID], [SourceCode]) VALUES (N'2', N'SC5')
INSERT [dbo].[Relation] ([ChartID], [SourceCode]) VALUES (N'3', N'SC1')
INSERT [dbo].[Relation] ([ChartID], [SourceCode]) VALUES (N'3', N'SC2')
INSERT [dbo].[Relation] ([ChartID], [SourceCode]) VALUES (N'3', N'SC3')
CREATE TABLE [dbo].[Chart](
[ChartID] [varchar](50) NULL,
[Chart_Type] [varchar](50) NULL,
[ChartName] [varchar](50) NULL,
[Year] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
INSERT [dbo].[Chart] ([ChartID], [Chart_Type], [ChartName], [Year]) VALUES (N'1', N'SRC', N'CHART-A', 2011)
INSERT [dbo].[Chart] ([ChartID], [Chart_Type], [ChartName], [Year]) VALUES (N'2', N'SRC', N'CHART-B', 2011)
INSERT [dbo].[Chart] ([ChartID], [Chart_Type], [ChartName], [Year]) VALUES (N'3', N'SRC', N'CHART-C', 2011)
Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com
December 19, 2011 at 8:26 am
sami.sqldba (12/19/2011)
Please can anyone say which would be the best possible join can be used to get the task.
You should be able to answer this for yourself. Ask yourself the following questions:
1) What does an INNER JOIN do when there is no match?
2) What does a LEFT OUTER JOIN do with the right table fields when there is no match?
3) What does a RIGHT OUTER JOIN do with the left table fields when there is no match?
4) What does a FULL OUTER JOIN do with the fields from one table when there is no match to the other table?
5) What does a CROSS JOIN do?
Look specifically at ChartA and SC2 and the fields for Year and Description and see what would happen with each of the joins.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 20, 2011 at 9:44 am
Sami,
I'm a little bit confused about the field names and the content of your sample code, but I think that this would give you a near result to your target:
select c.Year, c.ChartName, c.Chart_Type, s.Description,
case when c.Chart_Type = s.SourceCode then 1 else 0 end
from Chart c
, SourceCode s
where c.Year = 2011
order by c.ChartName, c.Chart_Type
Best regards.
December 20, 2011 at 11:30 am
Narud (12/20/2011)
Sami,I'm a little bit confused about the field names and the content of your sample code, but I think that this would give you a near result to your target:
select c.Year, c.ChartName, c.Chart_Type, s.Description,case when c.Chart_Type = s.SourceCode then 1 else 0 end
from Chart c
, SourceCode s
where c.Year = 2011
order by c.ChartName, c.Chart_Type
Best regards.
This join syntax has been deprecated. Please use explicit joins.
The last column requires information from all three tables. Since your query only includes two of the three tables, you're unable to get the correct information for that last column.
Maybe it's just because I've been working with SQL for so long, but this problem seems very basic to me. I don't see where the issue lies, particularly since the OP never included any attempts at solving it.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply