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:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url] -
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),>
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,>
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,>
here is some sample data from tbl3215
8 20060109 2 Laptops/2 Printers Two Dell Latitude D610 Laptops\r\nTwo HP 2600 Laserjet Printers Required to support essential needs in support of OPS. 10/13/2005 12:00:00 AM 11/1/2005 12:00:00 AM 745 SAS/379 EOG\r\n Sharod Allen\r\nXXX-5716 2 0 99 Approved 1 12/11/2005 12:00:00 AM C here is some from tblassignment
49 20050017 6 3/14/2005 12:00:00 AM 4/5/2005 12:00:00 AM 1430 0 here is some from tblfltwrkcntr
8 25 SCM SCMPO myemail@youremail.com 0 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
9 29 SCM SCMC myemail@youremail.com 0 here is an SCB
10 30 SCB SCBN youremail@sqlisconfusing.com 0 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:
4 4 SCB SCB myemail@youremail.com 0 5 21 SCB SCBH myemail@youremail.com 0 6 22 SCB SCBM myemail@youremail.com 0 7 24 SCM SCMR myemail@youremail.com 0
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply