October 26, 2014 at 1:31 am
Hi,
Sorry I know this is going to be an easy fix but I'm just starting out with SQL scripting.
I have the following query which has inner joins and is fine on the first join however when I enter the second join the results are wrong
scripting ok up to this point:
SELECT
a.CLIENT_ID
,a.SITE_ID
,a.AUDIT_DATE
,b.VARIABLE_NAME
,a.TOTAL
FROMMMA_KPIVAR_DAT AS a
INNER JOIN MMA_KPIVAR_MST AS b
ON a.VARIABLE_ID=b.VARIABLE_ID
WHERE a.CLIENT_ID=17 AND a.SITE_ID=57
--THIS PRODUCES RESULTS AS EXPECTED--
However when I add the next join:
SELECT
a.CLIENT_ID
,a.SITE_ID
,a.AUDIT_DATE
,b.VARIABLE_NAME
,a.TOTAL
,c.SITE_NAME
FROMMMA_KPIVAR_DAT AS a
INNER JOIN MMA_KPIVAR_MST AS b
ON a.VARIABLE_ID=b.VARIABLE_ID
INNER JOIN MMA_SITE_MST AS c
ON a.SITE_ID=c.SITE_ID
WHERE a.CLIENT_ID=17 AND a.SITE_ID=57
I get instead of getting the site name I get multiple sites with the same results and the same date??
I think it is something to do with the date as the data for all rows is duplicated for all sites but is the correct data for CLIENT_ID=17 & SITE_ID=57
I have tried different joins e.g. started with MMA_SITE_MST and built it that way only to return the same results
Any help would be much appreciated
Thanks
J
October 26, 2014 at 4:45 am
Quick question, can you post the DDL (create script) for those three tables?
😎
October 26, 2014 at 5:28 am
Would do if I new how,
Sorry no idea what you are after
J.
October 26, 2014 at 6:02 am
No worries, there are several ways of doing this, have a quick look here for Generate Scripts (SQL Server Management Studio).
😎
October 26, 2014 at 6:10 am
Hope this is what you need 🙂
scripting for each of the tables:
USE [MMAUDIT]
GO
/****** Object: Table [dbo].[MMA_SITE_MST] Script Date: 10/26/2014 12:07:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MMA_SITE_MST](
[CLIENT_ID] [int] NOT NULL,
[SITE_ID] [int] NOT NULL,
[SITE_NAME] [varchar](50) NOT NULL,
[IN_USE] [int] NULL CONSTRAINT [DF_MMA_SITE_MST_IN_USE] DEFAULT ((1)),
[SITE_NO] [varchar](max) NULL,
[TO_INTERNALEMAILS] [varchar](max) NULL,
[CC_INTERNALEMAILS] [varchar](max) NULL,
[TO_EXTERNALEMAILS] [varchar](max) NULL,
[CC_EXTERNALEMAILS] [varchar](max) NULL,
CONSTRAINT [PK_MMA_SITE_MST] PRIMARY KEY CLUSTERED
(
[CLIENT_ID] ASC,
[SITE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [MMAUDIT]
GO
/****** Object: Table [dbo].[MMA_KPIVAR_DAT] Script Date: 10/26/2014 12:06:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MMA_KPIVAR_DAT](
[CLIENT_ID] [int] NOT NULL,
[SITE_ID] [int] NOT NULL,
[AUDIT_DATE] [date] NOT NULL,
[GRAPH_ID] [int] NOT NULL,
[CHART_ID] [int] NOT NULL,
[VARIABLE_ID] [int] NOT NULL,
[WK1] [real] NOT NULL,
[WK2] [real] NOT NULL,
[WK3] [real] NOT NULL,
[WK4] [real] NOT NULL,
[TOTAL] [real] NOT NULL,
CONSTRAINT [PK_MMA_KPIVAR_DAT] PRIMARY KEY CLUSTERED
(
[CLIENT_ID] ASC,
[SITE_ID] ASC,
[AUDIT_DATE] ASC,
[GRAPH_ID] ASC,
[CHART_ID] ASC,
[VARIABLE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [MMAUDIT]
GO
/****** Object: Table [dbo].[MMA_KPIVAR_MST] Script Date: 10/26/2014 12:07:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MMA_KPIVAR_MST](
[CLIENT_ID] [int] NOT NULL,
[GRAPH_ID] [int] NOT NULL,
[CHART_ID] [int] NOT NULL,
[VARIABLE_ID] [int] NOT NULL,
[VARIABLE_NAME] [varchar](100) NOT NULL,
[IN_USE] [int] NOT NULL,
CONSTRAINT [PK_MMA_KPIVAR_MST] PRIMARY KEY CLUSTERED
(
[CLIENT_ID] ASC,
[GRAPH_ID] ASC,
[CHART_ID] ASC,
[VARIABLE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
let me know what you think
October 26, 2014 at 8:05 am
Good job, the scripts do explain what is happening
😎
The table dbo.MMA_KPIVAR_DAT can have many instances of the same VARIABLE_ID, CLIENT_ID and SITE_ID. The table dbo.MMA_KPIVAR_MST can have many instances of the same CLIENT_ID and VARIABLE_ID. It is therefore evident that if the querySELECT
a.CLIENT_ID
,a.SITE_ID
,a.AUDIT_DATE
,b.VARIABLE_NAME
,a.TOTAL
FROM MMA_KPIVAR_DAT a
INNER JOIN MMA_KPIVAR_MST b
ON a.VARIABLE_ID = b.VARIABLE_ID
WHERE a.CLIENT_ID = 17
AND a.SITE_ID = 57brings back expected results it is caused by coincidence and/or sparsity in the data set. To fully qualify the join, the common key columns must be usedSELECT
a.CLIENT_ID
,a.SITE_ID
,a.AUDIT_DATE
,b.VARIABLE_NAME
,a.TOTAL
FROM MMA_KPIVAR_DAT a
INNER JOIN MMA_KPIVAR_MST b
ON a.CLIENT_ID = b.CLIENT_ID
AND a.GRAPH_ID = b.GRAPH_ID
AND a.CHART_ID = b.CHART_ID
AND a.VARIABLE_ID = b.VARIABLE_ID
WHERE a.CLIENT_ID = 17
AND a.SITE_ID = 57 +--------------------+ +--------------------+
| dbo.MMA_KPIVAR_DAT | | dbo.MMA_KPIVAR_MST |
+--------------------+ +--------------------+
| *CLIENT_ID |----------------| *CLIENT_ID |
| *SITE_ID | ,----------| *GRAPH_ID |
| *AUDIT_DATE | | ,--------| *CHART_ID |
| *GRAPH_ID |-----' | ,------| *VARIABLE_ID |
| *CHART_ID |-------' | | VARIABLE_NAME |
| *VARIABLE_ID |---------' | IN_USE |
| WK1 | +--------------------+
| WK2 |
| WK3 |
| WK4 |
| TOTAL |
+--------------------+
Same applies when adding the table dbo.MMA_SITE_MST, which can have multiple instances of the same CLIENT_ID and SITE_ID, both columns must be used in the joinSELECT
a.CLIENT_ID
,a.SITE_ID
,a.AUDIT_DATE
,b.VARIABLE_NAME
,a.TOTAL
,c.SITE_NAME
FROM MMA_KPIVAR_DAT a
INNER JOIN MMA_KPIVAR_MST b
ON a.CLIENT_ID = b.CLIENT_ID
AND a.GRAPH_ID = b.GRAPH_ID
AND a.CHART_ID = b.CHART_ID
AND a.VARIABLE_ID = b.VARIABLE_ID
INNER JOIN MMA_SITE_MST c
ON a.CLIENT_ID = c.CLIENT_ID
AND a.SITE_ID = c.SITE_ID
WHERE a.CLIENT_ID = 17
AND a.SITE_ID = 57
+--------------------+ +--------------------+ +--------------------+
| dbo.MMA_SITE_MST | | dbo.MMA_KPIVAR_DAT | | dbo.MMA_KPIVAR_MST |
+--------------------+ +--------------------+ +--------------------+
| *CLIENT_ID |---------------| *CLIENT_ID |----------------| *CLIENT_ID |
| *SITE_ID |---------------| *SITE_ID | ,----------| *GRAPH_ID |
| SITE_NAME | | *AUDIT_DATE | | ,--------| *CHART_ID |
| IN_USE | | *GRAPH_ID |-----' | ,------| *VARIABLE_ID |
| SITE_NO | | *CHART_ID |-------' | | VARIABLE_NAME |
| TO_INTERNALEMAILS | | *VARIABLE_ID |---------' | IN_USE |
| CC_INTERNALEMAILS | | WK1 | +--------------------+
| TO_EXTERNALEMAILS | | WK2 |
| CC_EXTERNALEMAILS | | WK3 |
+--------------------+ | WK4 |
| TOTAL |
+--------------------+
Edit: Corrected Syntax Error
October 26, 2014 at 10:55 am
Hi thanks for this,
Has an error on line 11 (ON statement)
J
October 26, 2014 at 11:06 am
Not a problem replaced with AND statement instead of ON seems to have resolved it 🙂
Thanks for the help and your time
J
October 26, 2014 at 11:49 am
j.hoube (10/26/2014)
Not a problem replaced with AND statement instead of ON seems to have resolved it 🙂Thanks for the help and your time
J
Sorry about the error, quick copy/paste without checking the code:pinch:
😎
November 6, 2014 at 3:41 am
that ERD must have taken you hours!
November 6, 2014 at 4:46 am
aaron.reese (11/6/2014)
that ERD must have taken you hours!
Less than five minutes in vi, slightly more in notepad++
😎
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply