3 queries needs to be joined - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

3 queries needs to be joined

Author

Topic

dsnaveen
Starting Member

2 Posts

Posted - 2009-12-28 : 06:12:03

Hi,

Iam have a requirement to join all the 3 below SQLs.

(1) SELECT MAX(DEF_DAT.F_NAME)
FROM DEF_DAT
JOIN DEF_GRP ON DEF_DAT.F_DFGP = DEF_GRP.F_DFGP
JOIN SGRP_TST ON SGRP_TST.F_DEF = DEF_DAT.F_DEF
JOIN SGRP_INF ON SGRP_TST.F_SGRP = SGRP_INF.F_SGRP
JOIN TEST_DAT ON SGRP_TST.F_TEST = TEST_DAT.F_TEST
JOIN TEST_GRP ON TEST_DAT.F_TSGP = TEST_GRP.F_TSGP
WHERE DEF_GRP.F_NAME = 'Primary Reason for Hold'
AND TEST_GRP.F_NAME = '*OEE'
GROUP BY DEF_DAT.F_NAME

(2) SELECT JOB_GRP.F_JBGP
FROM
JOB_DAT LEFT JOIN JOB_GRP ON JOB_DAT.F_JBGP = JOB_GRP.F_JBGP
GROUP BY JOB_GRP.F_JBGP

(3) SELECT JOB_DAT.F_JOB
FROM
SGRP_INF LEFT JOIN JOB_DAT ON SGRP_INF.F_JOB = JOB_DAT.F_JOB
GROUP BY JOB_DAT.F_JOB

If i run seperately all 3 queries, it runs fine, I need to run it in a single SELECT, Can you please help.

Thank you,
Naveen

divyaram
Posting Yak Master

180 Posts

Posted - 2009-12-28 : 06:17:52

naveen can provide the table structure of tables used for all these 3 queries

Regards,
Divya

dsnaveen
Starting Member

2 Posts

Posted - 2009-12-28 : 06:36:19

Hi,

Thank you for the reply, please find attached the table structure.

CREATE TABLE [dbo].[DEF_DAT](
[F_DEF] [int] NOT NULL,
[F_CRTM] [int] NULL,
[F_EDTM] [int] NULL,
[F_DFGP] [int] NOT NULL,
[F_NAME] [varchar](32) NOT NULL,
[F_TEXT] [varchar](64) NULL,
[F_ABBR] [varchar](8) NULL,
[F_FCTR] [real] NULL,
[F_USER] [int] NULL,
[F_DSBL] [int] NULL,
[F_RFC] [int] NULL,
[F_VAL] [float] NULL,
CONSTRAINT [DEF_DAT_PK] PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[DEF_GRP](
[F_DFGP] [int] NOT NULL,
[F_CRTM] [int] NULL,
[F_EDTM] [int] NULL,
[F_NAME] [varchar](32) NOT NULL,
[F_TEXT] [varchar](64) NULL,
[F_ABBR] [varchar](8) NULL,
[F_FCTR] [real] NULL,
[F_USER] [int] NULL,
[F_DSBL] [int] NULL,
[F_RFC] [int] NULL,
CONSTRAINT [DEF_GRP_PK] PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[TEST_DAT](
[F_TEST] [int] NOT NULL,
[F_CRTM] [int] NULL,
[F_EDTM] [int] NULL,
[F_TSGP] [int] NOT NULL,
[F_TYPE] [int] NULL,
[F_NAME] [varchar](32) NOT NULL,
[F_TEXT] [varchar](64) NULL,
[F_ABBR] [varchar](8) NULL,
[F_MTYP] [int] NULL,
[F_UTYP] [int] NULL,
[F_DFGP] [int] NULL,
[F_FCTR] [real] NULL,
[F_USER] [int] NULL,
[F_DSBL] [int] NULL,
[F_RFC] [int] NULL,
CONSTRAINT [TEST_DAT_PK] PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[TEST_GRP](
[F_TSGP] [int] NOT NULL,
[F_CRTM] [int] NULL,
[F_EDTM] [int] NULL,
[F_NAME] [varchar](32) NOT NULL,
[F_TEXT] [varchar](64) NULL,
[F_ABBR] [varchar](8) NULL,
[F_FCTR] [real] NULL,
[F_USER] [int] NULL,
[F_DSBL] [int] NULL,
[F_RFC] [int] NULL,
CONSTRAINT [TEST_GRP_PK] PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[SGRP_TST](
[F_SGRP] [int] NOT NULL,
[F_TEST] [int] NOT NULL,
[F_TSNO] [int] NOT NULL,
[F_SBNO] [int] NOT NULL,
[F_DEF] [int] NULL,
[F_VAL] [real] NULL,
[F_SN] [int] NULL,
[F_GAGE] [int] NULL,
[F_FLAG] [int] NULL,
[F_USER] [int] NULL,
[F_DSBL] [int] NULL,
[F_EDTM] [int] NULL,
[F_RFC] [int] NULL,
CONSTRAINT [SGRP_TST_PK] PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[JOB_GRP](
[F_JBGP] [int] NOT NULL,
[F_CRTM] [int] NULL,
[F_EDTM] [int] NULL,
[F_NAME] [varchar](32) NOT NULL,
[F_TEXT] [varchar](64) NULL,
[F_ABBR] [varchar](8) NULL,
[F_FCTR] [real] NULL,
[F_USER] [int] NULL,
[F_DSBL] [int] NULL,
[F_RFC] [int] NULL,
CONSTRAINT [JOB_GRP_PK] PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[JOB_DAT](
[F_JOB] [int] NOT NULL,
[F_CRTM] [int] NULL,
[F_EDTM] [int] NULL,
[F_JBGP] [int] NOT NULL,
[F_NAME] [varchar](32) NOT NULL,
[F_TEXT] [varchar](64) NULL,
[F_ABBR] [varchar](8) NULL,
[F_STTM] [int] NULL,
[F_FNTM] [int] NULL,
[F_STAT] [int] NULL,
[F_FCTR] [real] NULL,
[F_USER] [int] NULL,
[F_DSBL] [int] NULL,
[F_RFC] [int] NULL,
[F_SITE] [int] NULL,
[F_HOLD] [int] NULL,
CONSTRAINT [JOB_DAT_PK] PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[SGRP_INF](
[F_SGRP] [int] NOT NULL,
[F_CRTM] [int] NULL,
[F_EDTM] [int] NULL,
[F_PART] [int] NOT NULL,
[F_PRCS] [int] NOT NULL,
[F_WKNO] [int] NULL,
[F_JOB] [int] NULL,
[F_LOT] [int] NULL,
[F_SPLT] [int] NULL,
[F_EMPL] [int] NULL,
[F_SGTM] [int] NULL,
[F_SGSZ] [int] NULL,
[F_FLAG] [int] NULL,
[F_USER] [int] NULL,
[F_DSBL] [int] NULL,
[F_RFC] [int] NULL,
[F_PTRV] [int] NULL,
CONSTRAINT [SGRP_INF_PK] PRIMARY KEY CLUSTERED

divyaram
Posting Yak Master

180 Posts

Posted - 2009-12-28 : 07:08:44

select max(DEF_DAT.F_NAME ),JOB_GRP.F_JBGP ,JOB_DAT.F_JOB
from DEF_DAT with (nolock)
INNER JOIN [dbo].[JOB_GRP] WITH (NOLOCK) ON [JOB_GRP].[F_USER]=[DEF_DAT].[F_USER]
INNER JOIN [dbo].[JOB_DAT] WITH (NOLOCK) ON [JOB_DAT].[F_CRTM]= [DEF_DAT].[F_CRTM]
INNER JOIN [dbo].[DEF_GRP] WITH (NOLOCK) ON [DEF_DAT].[F_EDTM] = [DEF_GRP].[F_EDTM]
INNER JOIN [DBO].[TEST_GRP] WITH (NOLOCK) ON [DEF_DAT].[F_NAME] = [TEST_GRP].[F_NAME]
WHERE
DEF_GRP.F_NAME = 'Primary Reason for Hold'
AND TEST_GRP.F_NAME = '*OEE'
GROUP BY DEF_DAT.F_NAME

Regards,
Divya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources