Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Jul 2007
    Posts
    50

    Unanswered: Is it possible to perform four different criterias in the same join?

    Hi all,

    I would like to know if there is a more elegant way to perform the following.

    I basically have a subscription model to build out and I can see a way of doing it, using two different queries within a Stored Proc and merging their output into one, but would like to know if there is a way to encapulate this all into one query.

    I have the following tables for my base data.

    tbl_main.
    Contains
    clm_ID (Identity Field)
    clm_source_thing_one int

    It has a child table.

    tbl_affected_thing_one
    Contains

    atone_ID (Identity Field) int
    atone_main_id (Relationship to tbl_main.clm_ID) int
    atone_affected_thing int

    So the main table contains the source thing (for example Regent's street)
    The child table contains the affected thing (for example Oxford street and Regent street is closed).
    This is a one to many relationship as there could be many roads affected.

    Now I need to be able to subscribe to a 'street' and have my subscription returned if the street I'm subscribed to is either in the main table as source or in the child table as affected.
    Due to our application model, I cannot simple copy every source thing into the affected thing.


    Now I can do this using two join queries in a stored proc and outputting their merged outputs, but is there a way to do this in one query.

    This is just an example, I have another two 'things' that need to be added to the main table and their own separate child tables.

    I know that this is a little confusion, but it would be nice to get this into one query.

    Many Thanks,

    Paul.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It would be less confusion if you would supplying the sql code for the queries you joining are wanting to.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2007
    Posts
    50
    Hi,

    Here is one of the joins so far.

    Code:
    SELECT     tst_SUser.[SUser_ID], tst_SUser.SUSER_UserID
    FROM       tst_SUser
    INNER JOIN tst_User_Sub ON tst_SUser.[SUser_ID] = tst_User_Sub.USUB_UserId
    INNER JOIN tst_sub_div ON tst_sub_div.SSDIV_UserId = tst_User_Sub.USUB_UserId
    INNER JOIN tst_sub_pri ON tst_sub_pri.SSPRI_UserId = tst_User_Sub.USUB_UserId
    INNER JOIN tst_sub_reg ON tst_sub_reg.SSREG_UserId = tst_User_Sub.USUB_UserId
    INNER JOIN tst_Main  ON tst_Main.main_Source_Reg = tst_sub_reg.SSREG_Region 
    AND tst_Main.main_source_Div = tst_sub_div.SSDIV_Division 
    AND tst_sub_pri.SSPRI_Pri = tst_Main.main_curr_pri
    WHERE tst_Main.main_id = 1

    The second query would need to swap the tst_Main.main_Source_Reg column for the tst_AREG.AREG_Region one..

    Then the same for the tst_Main.main_source_Div column too, so four queries would be need to get all subscription details.

    Cheers,

    Paul.

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    still doesn't make sense, to me anyway. Do you mean that in the four queries you have a different table as the last table being joined to? Or do you mean you are joining on a different column of your tst_Main table.
    Dave

  5. #5
    Join Date
    Jun 2005
    Posts
    319
    reformatted your query so my eyes don't bleed..

    Code:
    SELECT     
    	  U.SUser_ID
    	, U.SUSER_UserID
    FROM       
    	tst_SUser U
    JOIN tst_User_Sub S
    	ON 	U.USUB_UserId = S.SUser_ID
    JOIN tst_sub_div D
    	ON 	U.USUB_UserId = D.SSDIV_UserId 
    JOIN tst_sub_pri P
    	ON 	U.USUB_UserId = P.SSPRI_UserId 
    JOIN tst_sub_reg R
    	ON 	U.USUB_UserId = R.SSREG_UserId 
    JOIN tst_Main  M
    	ON 	R.SSREG_Region = M.main_Source_Reg 
    	AND 	D.SSDIV_Division  = M.main_source_Div 
    	AND 	P.SSPRI_Pri = M.main_curr_pri
    	AND	M.main_id = 1
    I still have no idea what you are asking.

    I think you need separate queries and then union them all together.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Gagnon View Post
    reformatted your query so my eyes don't bleed..
    Your eyes got problems. His query was formatted very nicely as it was.
    Yours, on the other hand, makes me want to vomit my intestines our of my nose.

    Quote Originally Posted by MrRalphMan View Post
    The second query would need to swap the tst_Main.main_Source_Reg column for the tst_AREG.AREG_Region one..
    RalphMan, there is no tst_AREG data source in the query you posted, so how are we expected to show you how to swap it in?
    I had a very specific request. Supply the code for the queries you want to join. If you can't satisfy that request from someone that is willing to provide free help to you, then I can't be of further assistance.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jun 2005
    Posts
    319
    Quote Originally Posted by blindman View Post
    Your eyes got problems. His query was formatted very nicely as it was.
    Yours, on the other hand, makes me want to vomit my intestines our of my nose.
    unpossible

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    Your eyes got
    	problems
    .
    His query was
    	formatted very nicely as it was
    .
    Yours
    , on the other hand
    , makes me want to 
    	vomit my intestines out of my nose
    .
    Better?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jun 2005
    Posts
    319
    Youarerightitismucheasiertoreadlargeblocksoftextco mpactedcloselytogetherandignoretablealiases

  10. #10
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    nah... i like gagnon's better. makes the join condition stand out better. Which if you know the structures, you can tell at a glance if the writer is joining on the whole key and whatever other conditions. But, I am kind of anal bout those kinds of things. I have problem with people writing explicit join syntax, then putting additional conditions on one of those tables in the WHERE clause too, as I should be able to look at the join and see all conditions for that table. Like:

    Code:
    select some_cols
       from my_table mt
    inner join your_table yt
       on mt.join_col = yt.join_col
    inner join his_table ht
       on mt.join_col = ht.join_col
    where mt.this_col = ?
      and yt.that_col = ?
      and ht.other_col =?
    Should be:
    Code:
    select some_cols
       from my_table mt
    inner join your_table yt
       on mt.join_col = yt.join_col
      and yt.that_col = ?
    inner join his_table ht
       on mt.join_col = ht.join_col
      and ht.other_col =?
    where mt.this_col = ?
    Another reason for this is with Left outer joins the placement of that AND condition means a world of difference in both query output and query response time.
    Dave

  11. #11
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    also, with you being blind, what does it matter what it looks like????

  12. #12
    Join Date
    Jul 2007
    Posts
    50
    Quote Originally Posted by blindman View Post
    Your eyes got problems. His query was formatted very nicely as it was.
    Yours, on the other hand, makes me want to vomit my intestines our of my nose.


    RalphMan, there is no tst_AREG data source in the query you posted, so how are we expected to show you how to swap it in?
    I had a very specific request. Supply the code for the queries you want to join. If you can't satisfy that request from someone that is willing to provide free help to you, then I can't be of further assistance.

    Oops, sorry for turning this into a bun fight, I'll try to explain better what I am trying to do.

    I have four tables as my main table structure.

    Code:
    USE [MYDatabase]
    GO
    /****** Object:  Table [dbo].[tst_Main]    Script Date: 12/09/2010 07:10:56 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tst_Main](
    	[main_id] [int] IDENTITY(1,1) NOT NULL,
    	[main_name] [varchar](50) NOT NULL,
    	[main_source_Div] [int] NOT NULL,
    	[main_Source_Reg] [int] NOT NULL,
    	[main_curr_pri] [int] NOT NULL,
    	[main_source_system_name] [varchar](50) NOT NULL,
    	[main_source_system_type] [varchar](50) NOT NULL,
    	[main_source_system_id] [int] NOT NULL,
     CONSTRAINT [PK_tst_Main] PRIMARY KEY CLUSTERED 
    (
    	[main_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
    /****** Object:  Table [dbo].[tst_ADIV]    Script Date: 12/09/2010 07:10:56 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[tst_ADIV](
    	[ADIV_id] [int] IDENTITY(1,1) NOT NULL,
    	[ADIV_main_id] [int] NOT NULL,
    	[ADIV_Div_ID] [int] NOT NULL,
     CONSTRAINT [PK_tst_ADIV] PRIMARY KEY CLUSTERED 
    (
    	[ADIV_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
    /****** Object:  Table [dbo].[tst_ASYS]    Script Date: 12/09/2010 07:10:56 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tst_ASYS](
    	[ASYS_ID] [int] IDENTITY(1,1) NOT NULL,
    	[ASYS_main_id] [int] NOT NULL,
    	[ASYS_System_Name] [varchar](50) NOT NULL,
    	[ASYS_system_type] [varchar](50) NOT NULL,
    	[ASYS_system_id] [int] NOT NULL,
     CONSTRAINT [PK_tst_ASYS] PRIMARY KEY CLUSTERED 
    (
    	[ASYS_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
    /****** Object:  Table [dbo].[tst_AREG]    Script Date: 12/09/2010 07:10:56 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[tst_AREG](
    	[AREG_ID] [int] IDENTITY(1,1) NOT NULL,
    	[AREG_main_id] [int] NOT NULL,
    	[AREG_Region] [int] NOT NULL,
     CONSTRAINT [PK_tst_AREG] PRIMARY KEY CLUSTERED 
    (
    	[AREG_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
    /****** Object:  ForeignKey [FK_tst_ADIV_tst_Main]    Script Date: 12/09/2010 07:10:56 ******/
    ALTER TABLE [dbo].[tst_ADIV]  WITH CHECK ADD  CONSTRAINT [FK_tst_ADIV_tst_Main] FOREIGN KEY([ADIV_main_id])
    REFERENCES [dbo].[tst_Main] ([main_id])
    GO
    ALTER TABLE [dbo].[tst_ADIV] CHECK CONSTRAINT [FK_tst_ADIV_tst_Main]
    GO
    /****** Object:  ForeignKey [FK_tst_AREG_tst_Main]    Script Date: 12/09/2010 07:10:56 ******/
    ALTER TABLE [dbo].[tst_AREG]  WITH CHECK ADD  CONSTRAINT [FK_tst_AREG_tst_Main] FOREIGN KEY([AREG_main_id])
    REFERENCES [dbo].[tst_Main] ([main_id])
    GO
    ALTER TABLE [dbo].[tst_AREG] CHECK CONSTRAINT [FK_tst_AREG_tst_Main]
    GO
    /****** Object:  ForeignKey [FK_tst_ASYS_tst_Main]    Script Date: 12/09/2010 07:10:56 ******/
    ALTER TABLE [dbo].[tst_ASYS]  WITH CHECK ADD  CONSTRAINT [FK_tst_ASYS_tst_Main] FOREIGN KEY([ASYS_main_id])
    REFERENCES [dbo].[tst_Main] ([main_id])
    GO
    ALTER TABLE [dbo].[tst_ASYS] CHECK CONSTRAINT [FK_tst_ASYS_tst_Main]
    GO
    I also have another six tables to store subscription data for these tables.

    Code:
    USE [MYDatabase]
    GO
    /****** Object:  Table [dbo].[tst_SUser]    Script Date: 12/09/2010 07:14:21 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tst_SUser](
    	[SUser_ID] [int] IDENTITY(1,1) NOT NULL,
    	[SUser_userid] [varchar](50) NOT NULL,
     CONSTRAINT [PK_tst_SUser] PRIMARY KEY CLUSTERED 
    (
    	[SUser_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
    /****** Object:  Table [dbo].[tst_User_Sub]    Script Date: 12/09/2010 07:14:21 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[tst_User_Sub](
    	[USUB_ID] [int] IDENTITY(1,1) NOT NULL,
    	[USUB_UserId] [int] NOT NULL,
     CONSTRAINT [PK_tst_User_Sub] PRIMARY KEY CLUSTERED 
    (
    	[USUB_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
    /****** Object:  Table [dbo].[tst_sub_reg]    Script Date: 12/09/2010 07:14:21 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[tst_sub_reg](
    	[SSREG_ID] [int] IDENTITY(1,1) NOT NULL,
    	[SSREG_UserID] [int] NOT NULL,
    	[SSREG_Region] [int] NOT NULL,
     CONSTRAINT [PK_tst_sub_reg] PRIMARY KEY CLUSTERED 
    (
    	[SSREG_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
    /****** Object:  Table [dbo].[tst_sub_div]    Script Date: 12/09/2010 07:14:21 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[tst_sub_div](
    	[SSDIV_ID] [int] IDENTITY(1,1) NOT NULL,
    	[SSDIV_UserId] [int] NOT NULL,
    	[SSDIV_Division] [int] NOT NULL,
     CONSTRAINT [PK_tst_sub_div] PRIMARY KEY CLUSTERED 
    (
    	[SSDIV_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
    /****** Object:  Table [dbo].[tst_sub_pri]    Script Date: 12/09/2010 07:14:21 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[tst_sub_pri](
    	[SSPRI_ID] [int] IDENTITY(1,1) NOT NULL,
    	[SSPRI_UserId] [int] NOT NULL,
    	[SSPRI_Pri] [int] NOT NULL,
     CONSTRAINT [PK_tst_sub_pri] PRIMARY KEY CLUSTERED 
    (
    	[SSPRI_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
    /****** Object:  Table [dbo].[tst_sub_API]    Script Date: 12/09/2010 07:14:21 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[tst_sub_API](
    	[SSAPI_ID] [int] IDENTITY(1,1) NOT NULL,
    	[SSAPI_UserId] [int] NOT NULL,
    	[SSAPI_AIID] [int] NOT NULL,
     CONSTRAINT [PK_tst_sub_API] PRIMARY KEY CLUSTERED 
    (
    	[SSAPI_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
    /****** Object:  ForeignKey [FK_tst_User_Sub_tst_SUser]    Script Date: 12/09/2010 07:14:21 ******/
    ALTER TABLE [dbo].[tst_User_Sub]  WITH CHECK ADD  CONSTRAINT [FK_tst_User_Sub_tst_SUser] FOREIGN KEY([USUB_UserId])
    REFERENCES [dbo].[tst_SUser] ([SUser_ID])
    GO
    ALTER TABLE [dbo].[tst_User_Sub] CHECK CONSTRAINT [FK_tst_User_Sub_tst_SUser]
    GO
    /****** Object:  ForeignKey [FK_tst_User_Sub_tst_User_Sub]    Script Date: 12/09/2010 07:14:21 ******/
    ALTER TABLE [dbo].[tst_User_Sub]  WITH CHECK ADD  CONSTRAINT [FK_tst_User_Sub_tst_User_Sub] FOREIGN KEY([USUB_ID])
    REFERENCES [dbo].[tst_User_Sub] ([USUB_ID])
    GO
    ALTER TABLE [dbo].[tst_User_Sub] CHECK CONSTRAINT [FK_tst_User_Sub_tst_User_Sub]
    GO
    /****** Object:  ForeignKey [FK_tst_sub_reg_tst_User_Sub]    Script Date: 12/09/2010 07:14:21 ******/
    ALTER TABLE [dbo].[tst_sub_reg]  WITH CHECK ADD  CONSTRAINT [FK_tst_sub_reg_tst_User_Sub] FOREIGN KEY([SSREG_UserID])
    REFERENCES [dbo].[tst_User_Sub] ([USUB_ID])
    GO
    ALTER TABLE [dbo].[tst_sub_reg] CHECK CONSTRAINT [FK_tst_sub_reg_tst_User_Sub]
    GO
    /****** Object:  ForeignKey [FK_tst_sub_pri_tst_User_Sub]    Script Date: 12/09/2010 07:14:21 ******/
    ALTER TABLE [dbo].[tst_sub_pri]  WITH CHECK ADD  CONSTRAINT [FK_tst_sub_pri_tst_User_Sub] FOREIGN KEY([SSPRI_UserId])
    REFERENCES [dbo].[tst_User_Sub] ([USUB_ID])
    GO
    ALTER TABLE [dbo].[tst_sub_pri] CHECK CONSTRAINT [FK_tst_sub_pri_tst_User_Sub]
    GO
    /****** Object:  ForeignKey [FK_tst_sub_div_tst_User_Sub]    Script Date: 12/09/2010 07:14:21 ******/
    ALTER TABLE [dbo].[tst_sub_div]  WITH CHECK ADD  CONSTRAINT [FK_tst_sub_div_tst_User_Sub] FOREIGN KEY([SSDIV_UserId])
    REFERENCES [dbo].[tst_User_Sub] ([USUB_ID])
    GO
    ALTER TABLE [dbo].[tst_sub_div] CHECK CONSTRAINT [FK_tst_sub_div_tst_User_Sub]
    GO
    /****** Object:  ForeignKey [FK_tst_sub_API_tst_User_Sub]    Script Date: 12/09/2010 07:14:21 ******/
    ALTER TABLE [dbo].[tst_sub_API]  WITH CHECK ADD  CONSTRAINT [FK_tst_sub_API_tst_User_Sub] FOREIGN KEY([SSAPI_UserId])
    REFERENCES [dbo].[tst_User_Sub] ([USUB_ID])
    GO
    ALTER TABLE [dbo].[tst_sub_API] CHECK CONSTRAINT [FK_tst_sub_API_tst_User_Sub]
    GO
    TBC

  13. #13
    Join Date
    Jul 2007
    Posts
    50
    Now the first four tables store details of the following:-

    Source Division - tbl_main.main_source_Div
    Source Region - tbl_main.main_source_Reg
    Affected Division - tbl_ADIV.SSDIV_Division
    Affected Region - tbl_AREG.SSREG_Region

    I will have to join to the tbl_API table as well, but this criteria will only be on the Priority and the SSAPI_AIID column, so ignore that for now.

    I have one join that currently works

    Code:
    DECLARE @incidentid int 
    set @incidentid = 1
    
    --Source Div/Source Region/Priority
    SELECT     distinct tst_SUser.[SUser_ID], tst_SUser.SUSER_UserID
    FROM       tst_SUser
    INNER JOIN tst_User_Sub ON tst_SUser.[SUser_ID] = tst_User_Sub.USUB_UserId
    INNER JOIN tst_sub_div ON tst_sub_div.SSDIV_UserId = tst_User_Sub.USUB_ID
    INNER JOIN tst_sub_pri ON tst_sub_pri.SSPRI_UserId = tst_User_Sub.USUB_ID
    INNER JOIN tst_sub_reg ON tst_sub_reg.SSREG_UserId = tst_User_Sub.USUB_ID
    INNER JOIN tst_Main  ON tst_Main.main_Source_Reg = tst_sub_reg.SSREG_Region 
    AND tst_Main.main_source_Div = tst_sub_div.SSDIV_Division 
    AND tst_sub_pri.SSPRI_Pri = tst_Main.main_curr_pri
    WHERE tst_Main.main_id = @incidentid
    
    --Source Div/Affected Region/Priority
    SELECT     distinct tst_SUser.[SUser_ID], tst_SUser.SUSER_UserID
    FROM       tst_SUser
    INNER JOIN tst_User_Sub ON tst_SUser.[SUser_ID] = tst_User_Sub.USUB_UserId
    INNER JOIN tst_sub_div ON tst_sub_div.SSDIV_UserId = tst_User_Sub.USUB_ID
    INNER JOIN tst_sub_pri ON tst_sub_pri.SSPRI_UserId = tst_User_Sub.USUB_ID
    INNER JOIN tst_sub_reg ON tst_sub_reg.SSREG_UserId = tst_User_Sub.USUB_ID
    INNER JOIN tst_Main  ON --tst_Main.main_Source_Reg = tst_sub_reg.SSREG_Region 
    --AND 
    tst_Main.main_source_Div = tst_sub_div.SSDIV_Division 
    AND tst_sub_pri.SSPRI_Pri = tst_Main.main_curr_pri
    INNER JOIN tst_AREG ON tst_AREG.AREG_main_id = tst_Main.main_id 
    AND tst_AREG.AREG_Region = tst_sub_reg.SSREG_Region
    WHERE tst_Main.main_id = @incidentid
    
    --Affected Div/Source Region/Priority
    SELECT     distinct tst_SUser.[SUser_ID], tst_SUser.SUSER_UserID
    FROM       tst_SUser
    INNER JOIN tst_User_Sub ON tst_SUser.[SUser_ID] = tst_User_Sub.USUB_UserId
    INNER JOIN tst_sub_div ON tst_sub_div.SSDIV_UserId = tst_User_Sub.USUB_ID
    INNER JOIN tst_sub_pri ON tst_sub_pri.SSPRI_UserId = tst_User_Sub.USUB_ID
    INNER JOIN tst_sub_reg ON tst_sub_reg.SSREG_UserId = tst_User_Sub.USUB_ID
    INNER JOIN tst_Main  ON tst_Main.main_Source_Reg = tst_sub_reg.SSREG_Region 
    --AND 
    --tst_Main.main_source_Div = tst_sub_div.SSDIV_Division 
    AND tst_sub_pri.SSPRI_Pri = tst_Main.main_curr_pri
    INNER JOIN tst_ADIV ON tst_ADIV.ADIV_main_id = tst_Main.main_id 
    AND tst_ADIV.ADIV_Div_ID = tst_sub_div.SSDIV_Division
    WHERE tst_Main.main_id = @incidentid
    
    --Affected Div/Affected Region/Priority
    SELECT     distinct tst_SUser.[SUser_ID], tst_SUser.SUSER_UserID
    FROM       tst_SUser
    INNER JOIN tst_User_Sub ON tst_SUser.[SUser_ID] = tst_User_Sub.USUB_UserId
    INNER JOIN tst_sub_div ON tst_sub_div.SSDIV_UserId = tst_User_Sub.USUB_ID
    INNER JOIN tst_sub_pri ON tst_sub_pri.SSPRI_UserId = tst_User_Sub.USUB_ID
    INNER JOIN tst_sub_reg ON tst_sub_reg.SSREG_UserId = tst_User_Sub.USUB_ID
    INNER JOIN tst_Main  ON tst_sub_pri.SSPRI_Pri = tst_Main.main_curr_pri
    INNER JOIN tst_AREG ON tst_AREG.AREG_main_id = tst_Main.main_id 
    AND tst_AREG.AREG_Region = tst_sub_reg.SSREG_Region
    INNER JOIN tst_ADIV ON tst_ADIV.ADIV_main_id = tst_Main.main_id 
    AND tst_ADIV.ADIV_Div_ID = tst_sub_div.SSDIV_Division
    WHERE tst_Main.main_id = @incidentid
    Sorry for not giving you much detail yesterday, but I hadn't even finished the queries at that point. I tried, and failed, to explain how this worked, but the above hopefully should explain what I was whittering on about.

    Paul.
    Last edited by MrRalphMan; 12-09-10 at 06:44. Reason: The code I posted was wrong....

  14. #14
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Okay, that's a bit better on the info side. Of those 4 queries will there always be data for one or more of the cases, or would it be more of a case that there will be data returned for one of the four? Reason, I ask, is that if the first query always returns data, then you could use left outer joins for the others to get the required data.
    Dave

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Gagnon View Post
    Youarerightitismucheasiertoreadlargeblocksoftextco mpactedcloselytogetherandignoretablealiases
    I
    '
    m

    p
    r
    e
    t
    t
    y

    s
    u
    r
    e

    t
    h
    a
    t
    '
    s

    n
    o
    t

    w
    h
    a
    t

    h
    i
    s

    c
    o
    d
    e

    l
    o
    o
    k
    e
    d

    l
    i
    k
    e
    .
    .
    .
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •