Professional Web Applications Themes

Join sp between Primary and controls? - ASP Database

I have mastered creating simple Select procedure in sql server, but now need to create a query that returns values for three parameters: u_id(UserID), BrokerID, SourceID If the parameters are null it should return all rows; if value present it should return the target rows. This seems to indicate a Join Query with parameters...could someone offer advice on how to achieve this? Many thanks Jason Primary Table Get User Create Procedure sp_GetUser u_id int As set nocount on select * from t_user where u_id=u_id return GO Control Table 2: Get Source CREATE Procedure spr_GetSource SourceID int As set nocount on ...

  1. #1

    Default Join sp between Primary and controls?


    I have mastered creating simple Select procedure in sql server, but now need
    to create a query that returns values for three parameters: u_id(UserID),
    BrokerID, SourceID

    If the parameters are null it should return all rows; if value present it
    should return the target rows. This seems to indicate a Join Query with
    parameters...could someone offer advice on how to achieve this?

    Many thanks
    Jason



    Primary Table Get User
    Create Procedure sp_GetUser
    u_id int
    As
    set nocount on
    select * from t_user where u_id=u_id
    return

    GO




    Control Table 2: Get Source
    CREATE Procedure spr_GetSource
    SourceID int
    As
    set nocount on
    select * from Source where SourceID=SourceID
    return
    GO



    Control Table 2: Get Broker
    CREATE Procedure spr_GetBroker
    Brokerid int
    As
    set nocount on
    select * from Broker where BrokerID=BrokerID
    return

    GO


    Guest

  2. #2

    Default Re: Join sp between Primary and controls?

    Can you show us the table structure, some sample data, and desired results
    given different parameters?

    [url]http://www.aspfaq.com/5006[/url]

    I like solving database problems, not word problems. ;-)

    --
    [url]http://www.aspfaq.com/[/url]
    (Reverse address to reply.)




    <jasoncatamaranco.com> wrote in message
    news:#0#d3Q9fEHA.3632TK2MSFTNGP11.phx.gbl...
    >
    > I have mastered creating simple Select procedure in sql server, but now
    need
    > to create a query that returns values for three parameters: u_id(UserID),
    > BrokerID, SourceID
    >
    > If the parameters are null it should return all rows; if value present it
    > should return the target rows. This seems to indicate a Join Query with
    > parameters...could someone offer advice on how to achieve this?
    >
    > Many thanks
    > Jason
    >
    >
    >
    > Primary Table Get User
    > Create Procedure sp_GetUser
    > u_id int
    > As
    > set nocount on
    > select * from t_user where u_id=u_id
    > return
    >
    > GO
    >
    >
    >
    >
    > Control Table 2: Get Source
    > CREATE Procedure spr_GetSource
    > SourceID int
    > As
    > set nocount on
    > select * from Source where SourceID=SourceID
    > return
    > GO
    >
    >
    >
    > Control Table 2: Get Broker
    > CREATE Procedure spr_GetBroker
    > Brokerid int
    > As
    > set nocount on
    > select * from Broker where BrokerID=BrokerID
    > return
    >
    > GO
    >
    >

    Aaron [SQL Server MVP] Guest

  3. #3

    Default Re: Join sp between Primary and controls?

    Got it....read article, so here is:

    Goal: My registration page presents a user form with some of the fields
    being database looks (see further below). On post, I create a new user and
    deposit the lookup values
    into the main table t_user. However, upon doing this I wish to now retrieve
    the 'varchar' values for the lookup tables based on the numeric IDs selected
    by the user in the form.

    See example:
    [url]http://69.2.200.70/dev/1/join.asp?Cookiechk=Disabled&u_FromURL=http://69.2.200.70/dev/1/link.asp&u_CurrentURL=/dev/1/doc3.asp[/url]

    I thought perhaps a composite query would help me effeciently streamline my
    asp code and allow me to do this in on go. I also hope to reuse the same
    query later in the application
    thus it needs to deal with null values.

    A weakness in the in my table relationships may relate to the fact that I
    look up tables (Nation) and (State) perhaps I should not have enforced
    referential integrity on these tables and simply hard-coded
    them into the web page which would simply future queries. Was I clear
    enough...let me know:

    Primary table:
    ---------------

    CREATE TABLE [t_user] (
    [u_id] [int] IDENTITY (1, 1) NOT NULL ,
    [u_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [u_password] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [u_firstname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
    ,
    [u_lastname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [u_email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [u_logcount] [int] NOT NULL CONSTRAINT [DF_t_user_u_logcount] DEFAULT (1),
    [u_createDate] [datetime] NOT NULL CONSTRAINT [DF_t_user_u_createDate]
    DEFAULT (getdate()),
    [u_lastDate] [datetime] NOT NULL CONSTRAINT [DF_t_user_u_lastDate] DEFAULT
    (getdate()),
    [u_SecurityLevel] [int] NULL ,
    [u_SourceID] [int] NULL ,
    [u_YachtPlacementID] [int] NULL ,
    [u_NationID] [int] NULL ,
    [u_StateID] [int] NULL ,
    [u_BrokerID] [int] NULL ,
    [u_ExpiryDate] [datetime] NULL ,
    [u_Comment] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [u_CurrentURL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [u_FromURL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [u_VisitorIP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [u_AcceptCookies] [int] NULL ,
    [u_AcceptEmail] [int] NULL ,
    [u_EmailOK] [int] NULL ,
    [u_Browser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [u_OS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [u_TimeFrame] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [u_IDStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_t_user_name] PRIMARY KEY NONCLUSTERED
    (
    [u_id]
    ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
    CONSTRAINT [IX_t_user_name] UNIQUE NONCLUSTERED
    (
    [u_name]
    ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
    CONSTRAINT [FK_t_user_Broker] FOREIGN KEY
    (
    [u_BrokerID]
    ) REFERENCES [Broker] (
    [BrokerID]
    ) ON UPDATE CASCADE ,
    CONSTRAINT [FK_t_user_Nation] FOREIGN KEY
    (
    [u_NationID]
    ) REFERENCES [Nation] (
    [NationID]
    ) ON UPDATE CASCADE ,
    CONSTRAINT [FK_t_user_Source] FOREIGN KEY
    (
    [u_SourceID]
    ) REFERENCES [Source] (
    [SourceID]
    ) ON UPDATE CASCADE ,
    CONSTRAINT [FK_t_user_State] FOREIGN KEY
    (
    [u_StateID]
    ) REFERENCES [State] (
    [StateID]
    ) ON UPDATE CASCADE ,
    CONSTRAINT [FK_t_user_YachtPlacement] FOREIGN KEY
    (
    [u_YachtPlacementID]
    ) REFERENCES [YachtPlacement] (
    [YppID]
    ) ON UPDATE CASCADE
    ) ON [PRIMARY]
    GO

    Lookup Table (1)
    -----------------

    CREATE TABLE [Source] (
    [SourceID] [int] IDENTITY (1, 1) NOT NULL ,
    [Source] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_Source] PRIMARY KEY CLUSTERED
    (
    [SourceID]
    ) WITH FILLFACTOR = 90 ON [PRIMARY]
    ) ON [PRIMARY]
    GO


    Lookup Table (2)
    -----------------

    CREATE TABLE [Broker] (
    [BrokerID] [int] IDENTITY (1, 1) NOT NULL ,
    [Broker] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [BrokerURL] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_Broker] PRIMARY KEY CLUSTERED
    (
    [BrokerID]
    ) WITH FILLFACTOR = 90 ON [PRIMARY]
    ) ON [PRIMARY]
    GO


    lookup Table (3)
    CREATE TABLE [YachtPlacement] (
    [YppID] [int] IDENTITY (1, 1) NOT NULL ,
    [YachtPlacement] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_YachtPlacementProgram] PRIMARY KEY CLUSTERED
    (
    [YppID]
    ) WITH FILLFACTOR = 90 ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    Lookup Table (4)
    ------------------
    CREATE TABLE [Nation] (
    [NationID] [int] IDENTITY (1, 1) NOT NULL ,
    [Nation] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_Nation] PRIMARY KEY CLUSTERED
    (
    [NationID]
    ) WITH FILLFACTOR = 90 ON [PRIMARY]
    ) ON [PRIMARY]
    GO


    Lookup Table (5)
    --------------------

    CREATE TABLE [State] (
    [StateID] [int] IDENTITY (1, 1) NOT NULL ,
    [State] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_State] PRIMARY KEY CLUSTERED
    (
    [StateID]
    ) WITH FILLFACTOR = 90 ON [PRIMARY]
    ) ON [PRIMARY]
    GO







    "Aaron [SQL Server MVP]" <ten.xocdnartreb.noraa> wrote in message
    news:u%23IIlT9fEHA.1188TK2MSFTNGP11.phx.gbl...
    > Can you show us the table structure, some sample data, and desired results
    > given different parameters?
    >
    > [url]http://www.aspfaq.com/5006[/url]
    >
    > I like solving database problems, not word problems. ;-)
    >
    > --
    > [url]http://www.aspfaq.com/[/url]
    > (Reverse address to reply.)
    >
    >
    >
    >
    > <jasoncatamaranco.com> wrote in message
    > news:#0#d3Q9fEHA.3632TK2MSFTNGP11.phx.gbl...
    > >
    > > I have mastered creating simple Select procedure in sql server, but now
    > need
    > > to create a query that returns values for three parameters:
    u_id(UserID),
    > > BrokerID, SourceID
    > >
    > > If the parameters are null it should return all rows; if value present
    it
    > > should return the target rows. This seems to indicate a Join Query
    with
    > > parameters...could someone offer advice on how to achieve this?
    > >
    > > Many thanks
    > > Jason
    > >
    > >
    > >
    > > Primary Table Get User
    > > Create Procedure sp_GetUser
    > > u_id int
    > > As
    > > set nocount on
    > > select * from t_user where u_id=u_id
    > > return
    > >
    > > GO
    > >
    > >
    > >
    > >
    > > Control Table 2: Get Source
    > > CREATE Procedure spr_GetSource
    > > SourceID int
    > > As
    > > set nocount on
    > > select * from Source where SourceID=SourceID
    > > return
    > > GO
    > >
    > >
    > >
    > > Control Table 2: Get Broker
    > > CREATE Procedure spr_GetBroker
    > > Brokerid int
    > > As
    > > set nocount on
    > > select * from Broker where BrokerID=BrokerID
    > > return
    > >
    > > GO
    > >
    > >
    >
    >

    Guest

  4. #4

    Default Re: Join sp between Primary and controls?

    Ok...experimented and came up with initial parametized Join:


    CREATE Procedure spr_GetUserSelections
    u_ID int

    As
    set nocount on
    Select t_user.u_name, Source.Source FROM
    t_user INNER JOIN Source ON
    t_user.u_SourceID = Source.SourceID
    WHERE t_user.u_ID=u_ID

    return
    GO

    It tested successfully.

    If I did this correctly it should:

    1. Allow me to pass the User ID to table: t_user
    2. Return Username 't_user' from table t_user and 'Source' from the lookup
    table 'Source'.

    Now
    -----

    I need to add in additional lookup tables to the query (see previous
    thread).....thus, the join becomes more complex.

    ?
    ---

    Is there a way for me to test returned rows by giving the SP a dummy
    parameter in the lyzer...this is probably a very stupid question
    and I know when you tell me the answer I will kick myself?

    Thanks
    Jaso0n

    <jasoncatamaranco.com> wrote in message
    news:u$%23bRs%23fEHA.3944tk2msftngp13.phx.gbl...
    > Got it....read article, so here is:
    >
    > Goal: My registration page presents a user form with some of the fields
    > being database looks (see further below). On post, I create a new user and
    > deposit the lookup values
    > into the main table t_user. However, upon doing this I wish to now
    retrieve
    > the 'varchar' values for the lookup tables based on the numeric IDs
    selected
    > by the user in the form.
    >
    > See example:
    >
    [url]http://69.2.200.70/dev/1/join.asp?Cookiechk=Disabled&u_FromURL=http://69.2.200.70/dev/1/link.asp&u_CurrentURL=/dev/1/doc3.asp[/url]
    >
    > I thought perhaps a composite query would help me effeciently streamline
    my
    > asp code and allow me to do this in on go. I also hope to reuse the same
    > query later in the application
    > thus it needs to deal with null values.
    >
    > A weakness in the in my table relationships may relate to the fact that I
    > look up tables (Nation) and (State) perhaps I should not have enforced
    > referential integrity on these tables and simply hard-coded
    > them into the web page which would simply future queries. Was I clear
    > enough...let me know:
    >
    > Primary table:
    > ---------------
    >
    > CREATE TABLE [t_user] (
    > [u_id] [int] IDENTITY (1, 1) NOT NULL ,
    > [u_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    > [u_password] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > [u_firstname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
    NULL
    > ,
    > [u_lastname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
    ,
    > [u_email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    > [u_logcount] [int] NOT NULL CONSTRAINT [DF_t_user_u_logcount] DEFAULT
    (1),
    > [u_createDate] [datetime] NOT NULL CONSTRAINT [DF_t_user_u_createDate]
    > DEFAULT (getdate()),
    > [u_lastDate] [datetime] NOT NULL CONSTRAINT [DF_t_user_u_lastDate]
    DEFAULT
    > (getdate()),
    > [u_SecurityLevel] [int] NULL ,
    > [u_SourceID] [int] NULL ,
    > [u_YachtPlacementID] [int] NULL ,
    > [u_NationID] [int] NULL ,
    > [u_StateID] [int] NULL ,
    > [u_BrokerID] [int] NULL ,
    > [u_ExpiryDate] [datetime] NULL ,
    > [u_Comment] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > [u_CurrentURL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ,
    > [u_FromURL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > [u_VisitorIP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > [u_AcceptCookies] [int] NULL ,
    > [u_AcceptEmail] [int] NULL ,
    > [u_EmailOK] [int] NULL ,
    > [u_Browser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > [u_OS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > [u_TimeFrame] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > [u_IDStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > CONSTRAINT [PK_t_user_name] PRIMARY KEY NONCLUSTERED
    > (
    > [u_id]
    > ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
    > CONSTRAINT [IX_t_user_name] UNIQUE NONCLUSTERED
    > (
    > [u_name]
    > ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
    > CONSTRAINT [FK_t_user_Broker] FOREIGN KEY
    > (
    > [u_BrokerID]
    > ) REFERENCES [Broker] (
    > [BrokerID]
    > ) ON UPDATE CASCADE ,
    > CONSTRAINT [FK_t_user_Nation] FOREIGN KEY
    > (
    > [u_NationID]
    > ) REFERENCES [Nation] (
    > [NationID]
    > ) ON UPDATE CASCADE ,
    > CONSTRAINT [FK_t_user_Source] FOREIGN KEY
    > (
    > [u_SourceID]
    > ) REFERENCES [Source] (
    > [SourceID]
    > ) ON UPDATE CASCADE ,
    > CONSTRAINT [FK_t_user_State] FOREIGN KEY
    > (
    > [u_StateID]
    > ) REFERENCES [State] (
    > [StateID]
    > ) ON UPDATE CASCADE ,
    > CONSTRAINT [FK_t_user_YachtPlacement] FOREIGN KEY
    > (
    > [u_YachtPlacementID]
    > ) REFERENCES [YachtPlacement] (
    > [YppID]
    > ) ON UPDATE CASCADE
    > ) ON [PRIMARY]
    > GO
    >
    > Lookup Table (1)
    > -----------------
    >
    > CREATE TABLE [Source] (
    > [SourceID] [int] IDENTITY (1, 1) NOT NULL ,
    > [Source] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > CONSTRAINT [PK_Source] PRIMARY KEY CLUSTERED
    > (
    > [SourceID]
    > ) WITH FILLFACTOR = 90 ON [PRIMARY]
    > ) ON [PRIMARY]
    > GO
    >
    >
    > Lookup Table (2)
    > -----------------
    >
    > CREATE TABLE [Broker] (
    > [BrokerID] [int] IDENTITY (1, 1) NOT NULL ,
    > [Broker] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > [BrokerURL] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > CONSTRAINT [PK_Broker] PRIMARY KEY CLUSTERED
    > (
    > [BrokerID]
    > ) WITH FILLFACTOR = 90 ON [PRIMARY]
    > ) ON [PRIMARY]
    > GO
    >
    >
    > lookup Table (3)
    > CREATE TABLE [YachtPlacement] (
    > [YppID] [int] IDENTITY (1, 1) NOT NULL ,
    > [YachtPlacement] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ,
    > CONSTRAINT [PK_YachtPlacementProgram] PRIMARY KEY CLUSTERED
    > (
    > [YppID]
    > ) WITH FILLFACTOR = 90 ON [PRIMARY]
    > ) ON [PRIMARY]
    > GO
    >
    > Lookup Table (4)
    > ------------------
    > CREATE TABLE [Nation] (
    > [NationID] [int] IDENTITY (1, 1) NOT NULL ,
    > [Nation] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > CONSTRAINT [PK_Nation] PRIMARY KEY CLUSTERED
    > (
    > [NationID]
    > ) WITH FILLFACTOR = 90 ON [PRIMARY]
    > ) ON [PRIMARY]
    > GO
    >
    >
    > Lookup Table (5)
    > --------------------
    >
    > CREATE TABLE [State] (
    > [StateID] [int] IDENTITY (1, 1) NOT NULL ,
    > [State] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > CONSTRAINT [PK_State] PRIMARY KEY CLUSTERED
    > (
    > [StateID]
    > ) WITH FILLFACTOR = 90 ON [PRIMARY]
    > ) ON [PRIMARY]
    > GO
    >
    >
    >
    >
    >
    >
    >
    > "Aaron [SQL Server MVP]" <ten.xocdnartreb.noraa> wrote in message
    > news:u%23IIlT9fEHA.1188TK2MSFTNGP11.phx.gbl...
    > > Can you show us the table structure, some sample data, and desired
    results
    > > given different parameters?
    > >
    > > [url]http://www.aspfaq.com/5006[/url]
    > >
    > > I like solving database problems, not word problems. ;-)
    > >
    > > --
    > > [url]http://www.aspfaq.com/[/url]
    > > (Reverse address to reply.)
    > >
    > >
    > >
    > >
    > > <jasoncatamaranco.com> wrote in message
    > > news:#0#d3Q9fEHA.3632TK2MSFTNGP11.phx.gbl...
    > > >
    > > > I have mastered creating simple Select procedure in sql server, but
    now
    > > need
    > > > to create a query that returns values for three parameters:
    > u_id(UserID),
    > > > BrokerID, SourceID
    > > >
    > > > If the parameters are null it should return all rows; if value present
    > it
    > > > should return the target rows. This seems to indicate a Join Query
    > with
    > > > parameters...could someone offer advice on how to achieve this?
    > > >
    > > > Many thanks
    > > > Jason
    > > >
    > > >
    > > >
    > > > Primary Table Get User
    > > > Create Procedure sp_GetUser
    > > > u_id int
    > > > As
    > > > set nocount on
    > > > select * from t_user where u_id=u_id
    > > > return
    > > >
    > > > GO
    > > >
    > > >
    > > >
    > > >
    > > > Control Table 2: Get Source
    > > > CREATE Procedure spr_GetSource
    > > > SourceID int
    > > > As
    > > > set nocount on
    > > > select * from Source where SourceID=SourceID
    > > > return
    > > > GO
    > > >
    > > >
    > > >
    > > > Control Table 2: Get Broker
    > > > CREATE Procedure spr_GetBroker
    > > > Brokerid int
    > > > As
    > > > set nocount on
    > > > select * from Broker where BrokerID=BrokerID
    > > > return
    > > >
    > > > GO
    > > >
    > > >
    > >
    > >
    >
    >

    Guest

  5. #5

    Default Re: Join sp between Primary and controls?

    I tried this:


    CREATE Procedure spr_GetUserSelections
    u_ID int

    As
    set nocount on
    Select t_user.u_name, Source.Source, YachtPlacement.YachtPlacement
    FROM t_user
    INNER JOIN Source ON
    t_user.u_SourceID = Source.SourceID
    INNER JOIN YachtPlacement ON
    t_user.u_YachPlacementID = YachtPlacement.YppID
    WHERE t_user.u_ID=u_ID

    return

    GO

    But I cannot figure out how to get that extra inner join in there....I am
    trying to return values from the lookup tables: Source, YachtPlacement withn
    t_user

    - Jason
    <jasoncatamaranco.com> wrote in message
    news:OorwxzHgEHA.1972TK2MSFTNGP09.phx.gbl...
    > Ok...experimented and came up with initial parametized Join:
    >
    >
    > CREATE Procedure spr_GetUserSelections
    > u_ID int
    >
    > As
    > set nocount on
    > Select t_user.u_name, Source.Source FROM
    > t_user INNER JOIN Source ON
    > t_user.u_SourceID = Source.SourceID
    > WHERE t_user.u_ID=u_ID
    >
    > return
    > GO
    >
    > It tested successfully.
    >
    > If I did this correctly it should:
    >
    > 1. Allow me to pass the User ID to table: t_user
    > 2. Return Username 't_user' from table t_user and 'Source' from the
    lookup
    > table 'Source'.
    >
    > Now
    > -----
    >
    > I need to add in additional lookup tables to the query (see previous
    > thread).....thus, the join becomes more complex.
    >
    > ?
    > ---
    >
    > Is there a way for me to test returned rows by giving the SP a dummy
    > parameter in the lyzer...this is probably a very stupid question
    > and I know when you tell me the answer I will kick myself?
    >
    > Thanks
    > Jaso0n
    >
    > <jasoncatamaranco.com> wrote in message
    > news:u$%23bRs%23fEHA.3944tk2msftngp13.phx.gbl...
    > > Got it....read article, so here is:
    > >
    > > Goal: My registration page presents a user form with some of the fields
    > > being database looks (see further below). On post, I create a new user
    and
    > > deposit the lookup values
    > > into the main table t_user. However, upon doing this I wish to now
    > retrieve
    > > the 'varchar' values for the lookup tables based on the numeric IDs
    > selected
    > > by the user in the form.
    > >
    > > See example:
    > >
    >
    [url]http://69.2.200.70/dev/1/join.asp?Cookiechk=Disabled&u_FromURL=http://69.2.200.70/dev/1/link.asp&u_CurrentURL=/dev/1/doc3.asp[/url]
    > >
    > > I thought perhaps a composite query would help me effeciently streamline
    > my
    > > asp code and allow me to do this in on go. I also hope to reuse the same
    > > query later in the application
    > > thus it needs to deal with null values.
    > >
    > > A weakness in the in my table relationships may relate to the fact that
    I
    > > look up tables (Nation) and (State) perhaps I should not have enforced
    > > referential integrity on these tables and simply hard-coded
    > > them into the web page which would simply future queries. Was I clear
    > > enough...let me know:
    > >
    > > Primary table:
    > > ---------------
    > >
    > > CREATE TABLE [t_user] (
    > > [u_id] [int] IDENTITY (1, 1) NOT NULL ,
    > > [u_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    > > [u_password] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > > [u_firstname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
    > NULL
    > > ,
    > > [u_lastname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
    NULL
    > ,
    > > [u_email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
    ,
    > > [u_logcount] [int] NOT NULL CONSTRAINT [DF_t_user_u_logcount] DEFAULT
    > (1),
    > > [u_createDate] [datetime] NOT NULL CONSTRAINT [DF_t_user_u_createDate]
    > > DEFAULT (getdate()),
    > > [u_lastDate] [datetime] NOT NULL CONSTRAINT [DF_t_user_u_lastDate]
    > DEFAULT
    > > (getdate()),
    > > [u_SecurityLevel] [int] NULL ,
    > > [u_SourceID] [int] NULL ,
    > > [u_YachtPlacementID] [int] NULL ,
    > > [u_NationID] [int] NULL ,
    > > [u_StateID] [int] NULL ,
    > > [u_BrokerID] [int] NULL ,
    > > [u_ExpiryDate] [datetime] NULL ,
    > > [u_Comment] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > > [u_CurrentURL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
    NULL
    > ,
    > > [u_FromURL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > > [u_VisitorIP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ,
    > > [u_AcceptCookies] [int] NULL ,
    > > [u_AcceptEmail] [int] NULL ,
    > > [u_EmailOK] [int] NULL ,
    > > [u_Browser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > > [u_OS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > > [u_TimeFrame] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ,
    > > [u_IDStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > > CONSTRAINT [PK_t_user_name] PRIMARY KEY NONCLUSTERED
    > > (
    > > [u_id]
    > > ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
    > > CONSTRAINT [IX_t_user_name] UNIQUE NONCLUSTERED
    > > (
    > > [u_name]
    > > ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
    > > CONSTRAINT [FK_t_user_Broker] FOREIGN KEY
    > > (
    > > [u_BrokerID]
    > > ) REFERENCES [Broker] (
    > > [BrokerID]
    > > ) ON UPDATE CASCADE ,
    > > CONSTRAINT [FK_t_user_Nation] FOREIGN KEY
    > > (
    > > [u_NationID]
    > > ) REFERENCES [Nation] (
    > > [NationID]
    > > ) ON UPDATE CASCADE ,
    > > CONSTRAINT [FK_t_user_Source] FOREIGN KEY
    > > (
    > > [u_SourceID]
    > > ) REFERENCES [Source] (
    > > [SourceID]
    > > ) ON UPDATE CASCADE ,
    > > CONSTRAINT [FK_t_user_State] FOREIGN KEY
    > > (
    > > [u_StateID]
    > > ) REFERENCES [State] (
    > > [StateID]
    > > ) ON UPDATE CASCADE ,
    > > CONSTRAINT [FK_t_user_YachtPlacement] FOREIGN KEY
    > > (
    > > [u_YachtPlacementID]
    > > ) REFERENCES [YachtPlacement] (
    > > [YppID]
    > > ) ON UPDATE CASCADE
    > > ) ON [PRIMARY]
    > > GO
    > >
    > > Lookup Table (1)
    > > -----------------
    > >
    > > CREATE TABLE [Source] (
    > > [SourceID] [int] IDENTITY (1, 1) NOT NULL ,
    > > [Source] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > > CONSTRAINT [PK_Source] PRIMARY KEY CLUSTERED
    > > (
    > > [SourceID]
    > > ) WITH FILLFACTOR = 90 ON [PRIMARY]
    > > ) ON [PRIMARY]
    > > GO
    > >
    > >
    > > Lookup Table (2)
    > > -----------------
    > >
    > > CREATE TABLE [Broker] (
    > > [BrokerID] [int] IDENTITY (1, 1) NOT NULL ,
    > > [Broker] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > > [BrokerURL] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > > CONSTRAINT [PK_Broker] PRIMARY KEY CLUSTERED
    > > (
    > > [BrokerID]
    > > ) WITH FILLFACTOR = 90 ON [PRIMARY]
    > > ) ON [PRIMARY]
    > > GO
    > >
    > >
    > > lookup Table (3)
    > > CREATE TABLE [YachtPlacement] (
    > > [YppID] [int] IDENTITY (1, 1) NOT NULL ,
    > > [YachtPlacement] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS
    NULL
    > ,
    > > CONSTRAINT [PK_YachtPlacementProgram] PRIMARY KEY CLUSTERED
    > > (
    > > [YppID]
    > > ) WITH FILLFACTOR = 90 ON [PRIMARY]
    > > ) ON [PRIMARY]
    > > GO
    > >
    > > Lookup Table (4)
    > > ------------------
    > > CREATE TABLE [Nation] (
    > > [NationID] [int] IDENTITY (1, 1) NOT NULL ,
    > > [Nation] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > > CONSTRAINT [PK_Nation] PRIMARY KEY CLUSTERED
    > > (
    > > [NationID]
    > > ) WITH FILLFACTOR = 90 ON [PRIMARY]
    > > ) ON [PRIMARY]
    > > GO
    > >
    > >
    > > Lookup Table (5)
    > > --------------------
    > >
    > > CREATE TABLE [State] (
    > > [StateID] [int] IDENTITY (1, 1) NOT NULL ,
    > > [State] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    > > CONSTRAINT [PK_State] PRIMARY KEY CLUSTERED
    > > (
    > > [StateID]
    > > ) WITH FILLFACTOR = 90 ON [PRIMARY]
    > > ) ON [PRIMARY]
    > > GO
    > >
    > >
    > >
    > >
    > >
    > >
    > >
    > > "Aaron [SQL Server MVP]" <ten.xocdnartreb.noraa> wrote in message
    > > news:u%23IIlT9fEHA.1188TK2MSFTNGP11.phx.gbl...
    > > > Can you show us the table structure, some sample data, and desired
    > results
    > > > given different parameters?
    > > >
    > > > [url]http://www.aspfaq.com/5006[/url]
    > > >
    > > > I like solving database problems, not word problems. ;-)
    > > >
    > > > --
    > > > [url]http://www.aspfaq.com/[/url]
    > > > (Reverse address to reply.)
    > > >
    > > >
    > > >
    > > >
    > > > <jasoncatamaranco.com> wrote in message
    > > > news:#0#d3Q9fEHA.3632TK2MSFTNGP11.phx.gbl...
    > > > >
    > > > > I have mastered creating simple Select procedure in sql server, but
    > now
    > > > need
    > > > > to create a query that returns values for three parameters:
    > > u_id(UserID),
    > > > > BrokerID, SourceID
    > > > >
    > > > > If the parameters are null it should return all rows; if value
    present
    > > it
    > > > > should return the target rows. This seems to indicate a Join Query
    > > with
    > > > > parameters...could someone offer advice on how to achieve this?
    > > > >
    > > > > Many thanks
    > > > > Jason
    > > > >
    > > > >
    > > > >
    > > > > Primary Table Get User
    > > > > Create Procedure sp_GetUser
    > > > > u_id int
    > > > > As
    > > > > set nocount on
    > > > > select * from t_user where u_id=u_id
    > > > > return
    > > > >
    > > > > GO
    > > > >
    > > > >
    > > > >
    > > > >
    > > > > Control Table 2: Get Source
    > > > > CREATE Procedure spr_GetSource
    > > > > SourceID int
    > > > > As
    > > > > set nocount on
    > > > > select * from Source where SourceID=SourceID
    > > > > return
    > > > > GO
    > > > >
    > > > >
    > > > >
    > > > > Control Table 2: Get Broker
    > > > > CREATE Procedure spr_GetBroker
    > > > > Brokerid int
    > > > > As
    > > > > set nocount on
    > > > > select * from Broker where BrokerID=BrokerID
    > > > > return
    > > > >
    > > > > GO
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Guest

  6. #6

    Default Re: Join sp between Primary and controls?

    > But I cannot figure out how to get that extra inner join in there

    What "extra inner join"? Your syntax looks correct, can you tell us the
    actual problem? Are you getting an error message? If so, what is it? Are
    you getting incorrect results? If so, what are they, and what do you think
    they should be? It's great to see your stored procedure code over and over
    again, but some other information might be useful also.

    (In addition, could you trim your replies a bit?)

    --
    [url]http://www.aspfaq.com/[/url]
    (Reverse address to reply.)


    Aaron [SQL Server MVP] Guest

  7. #7

    Default Re: Join sp between Primary and controls?

    Sorry, I fought with it and realised a comma delimiter was the
    problem....here is the full query which now works...I'll be more careful
    about posting without more thorough testing:

    CREATE Procedure spr_GetUserSelections
    u_ID int

    As
    set nocount on
    Select *
    FROM t_user
    INNER JOIN Source ON
    t_user.u_SourceID = Source.SourceID
    INNER JOIN YachtPlacement ON
    t_user.u_YachtPlacementID = YachtPlacement.YppID
    INNER JOIN Broker ON
    t_user.u_BrokerID = Broker.BrokerID
    INNER JOIN State ON
    t_user.u_StateID = State.StateID
    INNER JOIN Nation ON
    t_user.u_NationID = Nation.NationID
    WHERE t_user.u_ID=u


    Guest

Similar Threads

  1. Is left-join faster then inner join?
    By howachen@gmail.com in forum MySQL
    Replies: 5
    Last Post: March 5th, 07:11 AM
  2. Primary Scratch & Windows Primary Paging file?
    By Tommy Oberst in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 14
    Last Post: April 15th, 10:26 PM
  3. Replies: 1
    Last Post: November 25th, 03:15 PM
  4. user controls: dynamiclly added child controls dont survive post back ?
    By Wolfgang Brucker in forum ASP.NET Building Controls
    Replies: 1
    Last Post: January 14th, 09:13 PM
  5. Accessing Properties of Custom Controls child Controls
    By Moldy in forum ASP.NET Building Controls
    Replies: 8
    Last Post: September 5th, 03:39 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139