Complex (to me) SQL Query statement help

  • Hi all i am trying to get the completed field in the following table tble3215 to return the appropriate requirement numbers that are equal to 0.  The query pulls from 3 seperate tables.  All i need it to do is on tbl3215.completed =0 return those items to me.

    here is the statement

    select distinct dbo.tbl3215.ittnumber, dbo.tbl3215.itttitle, dbo.tbl3215.daterequired from dbo.tblassignment inner join dbo.tbl3215 on dbo.tbl3215.ittnumber = dbo.tblassignment.ittnumber inner join dbo.tblfltwrkcntr on dbo.tblfltwrkcntr.fltwkcntrid = dbo.tblassignment.assfltwrkid where (itttitle is not null or ittdescription is not null) and (dbo.tblfltwrkcntr.wrkcntr = @WrkCntr)

    I have tried the following:

    select distinct dbo.tbl3215.ittnumber, dbo.tbl3215.itttitle, dbo.tbl3215.daterequired from dbo.tblassignment inner join dbo.tbl3215 on dbo.tbl3215.ittnumber = dbo.tblassignment.ittnumber inner join dbo.tblfltwrkcntr on dbo.tblfltwrkcntr.fltwkcntrid = dbo.tblassignment.assfltwrkid where (itttitle is not null or ittdescription is not null) and (dbo.tblfltwrkcntr.wrkcntr = @WrkCntr) and tbl3215.completed = 0

    and

    select distinct dbo.tbl3215.ittnumber, dbo.tbl3215.itttitle, dbo.tbl3215.daterequired from dbo.tblassignment inner join dbo.tbl3215 on dbo.tbl3215.ittnumber = dbo.tblassignment.ittnumber inner join dbo.tblfltwrkcntr on dbo.tblfltwrkcntr.fltwkcntrid = dbo.tblassignment.assfltwrkid where (itttitle is not null or ittdescription is not null) and (dbo.tblfltwrkcntr.wrkcntr = @WrkCntr) and (tbl3215.completed = 0)

    neither of these work, can i get an assist.  Please let me know if you need more information

    Thanks, Joe

  • Joe

    When you say they don't work, do you mean they return an error, or you don't get the result set you expect?

    What is the significance of the first statement?

    Some sample data and table DDL would help us to help you.

    John

  • Have you tried this.

     

    select distinct dbo.tbl3215.ittnumber, dbo.tbl3215.itttitle, dbo.tbl3215.daterequired from dbo.tblassignment inner join dbo.tbl3215 on dbo.tbl3215.ittnumber = dbo.tblassignment.ittnumber inner join dbo.tblfltwrkcntr on dbo.tblfltwrkcntr.fltwkcntrid = dbo.tblassignment.assfltwrkid where ((itttitle is not null or ittdescription is not null) and (dbo.tblfltwrkcntr.wrkcntr = @WrkCntr)) and (tbl3215.completed = 0)

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • OK, newbie alert...what is DDL? 

  • Joe

    Sorry - DDL is data definition language, in other words the CREATE TABLE statements for your tables.  Sample data in the form of INSERT statements is easiest for us to use as well.  There is a paragraph on this site somewhere that gives advice like this to new posters such as yourself, but I couldn't find it just now when I looked.

    John

  • John, thanks for the kind response, some folks get in a tizzy

    here you go:

    DDL from tbl3215:

    INSERT INTO [SCX_3215].[dbo].[tbl3215]

               ([id]

               ,[IttNumber]

               ,[IttTitle]

               ,[IttDescription]

               ,[IttJustification]

               ,[TechSolution]

               ,[DateReceived]

               ,[DateRequired]

               ,[Customer]

               ,[OrgID]

               ,[Funded]

               ,[HBApID]

               ,[Disapproved]

               ,[DisReason]

               ,[Completed]

               ,[CompletionDate]

               ,[Priority])

         VALUES

               (<id, int,>

               ,<IttNumber, decimal(18,0),>

               ,<IttTitle, nvarchar(max),>

               ,<IttDescription, nvarchar(max),>

               ,<IttJustification, nvarchar(max),>

               ,<TechSolution, nvarchar(max),>

               ,<DateReceived, datetime,>

               ,<DateRequired, datetime,>

               ,<Customer, nvarchar(max),>

               ,<OrgID, nvarchar(max),>

               ,<Funded, nvarchar(max),>

               ,<HBApID, nvarchar(max),>

               ,<Disapproved, nvarchar(max),>

               ,<DisReason, nvarchar(max),>

               ,<Completed, int,>

               ,<CompletionDate, datetime,>

               ,<Priority, nvarchar(max),&gt

    DDL from tblassignment:

    INSERT INTO [SCX_3215].[dbo].[tblassignment]

               ([id]

               ,[IttNumber]

               ,[AssFltWrkID]

               ,[DateAss]

               ,[DateCmplt]

               ,[RecordID]

               ,[OnHold])

         VALUES

               (<id, int,>

               ,<IttNumber, decimal(18,0),>

               ,<AssFltWrkID, varchar,>

               ,<DateAss, datetime,>

               ,<DateCmplt, datetime,>

               ,<RecordID, int,>

               ,<OnHold, int,&gt

    DDL from tblfltwrkcnter

    INSERT INTO [SCX_3215].[dbo].[tblfltwrkcntr]

               ([id]

               ,[FltWkCntrID]

               ,[Flt]

               ,[WrkCntr]

               ,[WrkCntrEmail]

               ,[Deactivated])

         VALUES

               (<id, float,>

               ,<FltWkCntrID, float,>

               ,<Flt, nvarchar(255),>

               ,<WrkCntr, nvarchar(255),>

               ,<WrkCntrEmail, nvarchar(255),>

               ,<Deactivated, float,&gt

     

    here is some sample data from tbl3215

    8200601092 Laptops/2 PrintersTwo Dell Latitude D610 Laptops\r\nTwo HP 2600 Laserjet PrintersRequired to support  essential needs in support of OPS.10/13/2005 12:00:00 AM11/1/2005 12:00:00 AM745 SAS/379 EOG\r\n Sharod Allen\r\nXXX-57162099Approved112/11/2005 12:00:00 AMC

    here is some from tblassignment

    492005001763/14/2005 12:00:00 AM4/5/2005 12:00:00 AM14300

    here is some from tblfltwrkcntr

    825SCMSCMPOmyemail@youremail.com0

    i hope this suffices?

  • Yes i tried that as well.

    there is something that i am missing and i cant quite put my finger on it. I got it to work, but it does not return all the correct information and some show up under totally different workcenters. 

    My disclaimer is this:  the database started out as an ACCESS DB, then ported to PHP, and now ported to SQL hopefully for the last port..LOL.  I feel sorry for the next person that may have to fix this! A lot of datatype incompatabilities and the like. 

    I dont have a lot of SQL experience and i seem to bang my head against my desk at least 4 times a day, but figuring it out is fun.  I do a victory dance when i get something to work!!   I have definitely learned how NOT to design a DB

  • You may want to consider shortening your nvarchar(max) columns.

    The following may work:

    SELECT

        T.ittnumber

        ,COALESCE(T.itttitle, T.ittdescription) AS itttitle

        ,T.daterequired

    FROM dbo.tbl3215 T

    WHERE T.completed = 0

        AND COALESCE(T.itttitle, T.ittdescription) IS NOT NULL

        AND EXISTS (

                SELECT *

                FROM dbo.tblassignment A

                WHERE A.ittnumber = T.ittnumber

                    AND EXISTS (

                            SELECT *

                            FROM dbo.tblfltwrkcntr W

                            WHERE W.fltwkcntrid = A.assfltwrkid

                                AND W.wrkcntr = @WrkCntr

                        )

            )

  • Hi Ken, thanks for the help.  I will try this tomorrow when i get into work.

    You mentioned shortening nvarchar(max) columns....im sure there is a good reason, to increase data access speed i presume?

    a few questions though so that i understand it.  Why the T.ittnumber instead of just ittnumber?  and what does coalesce do?

    thank you

  • ok ken, i tried that and it appears to do the same thing as this one

    select distinct dbo.tbl3215.ittnumber, dbo.tbl3215.itttitle, dbo.tbl3215.daterequired from dbo.tblassignment inner join dbo.tbl3215 on dbo.tbl3215.ittnumber = dbo.tblassignment.ittnumber inner join dbo.tblfltwrkcntr on dbo.tblfltwrkcntr.fltwkcntrid = dbo.tblassignment.assfltwrkid where (itttitle is not null or ittdescription is not null) and (dbo.tblfltwrkcntr.wrkcntr = @WrkCntr) and (tbl3215.completed = 0)

    i get the exact same result set. 

    But if i use this statement

    select distinct dbo.tbl3215.ittnumber, dbo.tbl3215.itttitle, dbo.tbl3215.daterequired from dbo.tblassignment inner join dbo.tbl3215 on dbo.tbl3215.ittnumber = dbo.tblassignment.ittnumber inner join dbo.tblfltwrkcntr on dbo.tblfltwrkcntr.fltwkcntrid = dbo.tblassignment.assfltwrkid where (itttitle is not null or ittdescription is not null) and (dbo.tblfltwrkcntr.wrkcntr = @WrkCntr)

    i get every requirement no matter the value in the tbl3215.comlpeted column.  Does the order of the AND statements matter?

  • all, i think i have identified the issue.  Now i ask for your help how to solve it.

     

    within each 3 letter designator there is a sub org if you will.  For example,

    SCB

         SCBN

         SCBM

         SCBT

    so, when i change the statement to represent an OPEN status (the completed column in tbl3215 is equal to 0) i only get SCB, when i select SCBN, SCBM, or SCBT nothing is returned. 

    To test this theory, i changed the tbl3215 = 2 (on hold status) and the same thing happened.  I verified this through the live database (the PHP one) checking open and on hold requirements and they were there.

    So now the question becomes, how to i get it to see the 4 letter disgnators instead of only pulling the 3 letter workcenter designator?

    Thank You

     

  • Maybe:

    SELECT

        T.ittnumber

        ,COALESCE(T.itttitle, T.ittdescription) AS itttitle

        ,T.daterequired

    FROM dbo.tbl3215 T

    WHERE T.completed = 0

        AND COALESCE(T.itttitle, T.ittdescription) IS NOT NULL

        AND EXISTS (

                SELECT *

                FROM dbo.tblassignment A

                WHERE A.ittnumber = T.ittnumber

                    AND EXISTS (

                            SELECT *

                            FROM dbo.tblfltwrkcntr W

                            WHERE W.fltwkcntrid = A.assfltwrkid

                                AND W.wrkcntr LIKE @WrkCntr + '%'

                        )

            )

     

  • Ken, interesting.  It partially worked but only for one workcenter.

    We have another section called SCM, with subsections

    SCM

       SCMC (this works)

       SCMPO (does Not)

       SCMP (does Not)

      SCMCI (does Not)

    SCB

       SCBM (does Not)

       SCBN (does Not)

       SCBT (does not)

     

    ..etc

    cinfused yet?  i am trying to determine the commonality of SCM to SCMC.  I will keep the thread posted. Thank You

  • Maybe you should post some correct DDL, some sensible sample data, the results you get and the results you want.

    (If you do this you may even be able to work it out yourself!)

    Something like:

    CREATE TABLE #T3215

    (

        ittnumber int NOT NULL

        ,itttitle nvarchar(30) NULL

        ,ittdescription nvarchar(30) NULL

        ,daterequired datetime NULL

        ,completed int NOT NULL

    )

    -- Look up INSERT INTO in Books Online (BOL)

    INSERT INTO #T3215 VALUES (...)

    INSERT INTO #T3215 VALUES (...)

    INSERT INTO #T3215 VALUES (...)

    -- etc

    CREATE TABLE #Assignment

    (

        ittnumber int NOT NULL

        ,assfltwrkid int NOT NULL

    )

    INSERT INTO #Assignment VALUES (...)

    INSERT INTO #Assignment VALUES (...)

    INSERT INTO #Assignment VALUES (...)

    -- etc

    CREATE TABLE #WrkCntr

    (

        assfltwrkid int NOT NULL

        ,wrkcntr varchar(10) NOT NULL

    )

    INSERT INTO #WrkCntr VALUES (...)

    INSERT INTO #WrkCntrt VALUES (...)

    INSERT INTO #WrkCntr VALUES (...)

    -- etc

    -- Your Query

    SELECT ...

    -- Show the results you get.

    ittnumber  itttitle   daterequired

    ========== ========== ============

    ...

    -- Show the results you want.

    ittnumber  itttitle   daterequired

    ========== ========== ============

    ...

  • maybe this will help

    here is the SCMC row from tblfltwrkcntr

    929SCMSCMCmyemail@youremail.com0

    here is an SCB

    1030SCBSCBNyouremail@sqlisconfusing.com0

    another thing that i found is it appears that under the columns under fltwrkcntrID are out of order and it looks like through the years that records have been deleted, added re added however there are no duplicates. 

    here is a sample of that data:

    44SCBSCBmyemail@youremail.com0
    521SCBSCBHmyemail@youremail.com0
    622SCBSCBMmyemail@youremail.com0
    724SCMSCMRmyemail@youremail.com0

     

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply