Complex (to me) SQL Query statement help
-
Maybe i thought the DDL i posted WAS correct, since afterall i did ask in the third post of this thread. I was instructed to do it as an insert statement. Maybe i HAVE tried to work it out hence the reason i came here....because in case you have not noticed i am rather new at this, so go ahead and sound the NEWB alarm...oh excuse me GRASSHOPPER alarm. Maybe most people come here to just sponge information off people, i am here to learn. Maybe thats why my questions may seem rudimentry to your JEDI-like SQL tricks. Next time i will don a flame suit...sheesh. I case you have not noticed i have been following this thread and obviously there is something that is not clicking yet.
Since i didnt post the "correct" DDL -- here you go:
tbl3215:
CREATE TABLE [dbo].[tbl3215](
[id] [int] NOT NULL,
[IttNumber] [decimal](18, 0) NOT NULL,
[IttTitle] [nvarchar](max) NULL,
[IttDescription] [nvarchar](max) NULL,
[IttJustification] [nvarchar](max) NULL,
[TechSolution] [nvarchar](max) NULL,
[DateReceived] [datetime] NULL,
[DateRequired] [datetime] NULL,
[Customer] [nvarchar](max) NULL,
[OrgID] [nvarchar](max) NULL,
[Funded] [nvarchar](max) NULL,
[HBApID] [nvarchar](max) NULL,
[Disapproved] [nvarchar](max) NULL,
[DisReason] [nvarchar](max) NULL,
[Completed] [int] NULL,
[CompletionDate] [datetime] NULL,
[Priority] [nvarchar](max) NULL
) ON [PRIMARY]
tblassignment:
CREATE TABLE [dbo].[tblassignment](
[id] [int] NOT NULL,
[IttNumber] [decimal](18, 0) NULL,
[AssFltWrkID] [varchar](max) NULL,
[DateAss] [datetime] NULL,
[DateCmplt] [datetime] NULL,
[RecordID] [int] NULL,
[OnHold] [int] NULL
) ON [PRIMARY]
tblfltwrkcntr:
CREATE TABLE [dbo].[tblfltwrkcntr]{
[id] [int] NOT NULL,
[FltWrkCntrID] [int] NOT NULL,
[flt] [nvarchar] (255) NULL,
[WrkCntr] [nvarchar] (255) NULL,
[WrkCntrEmail] [nvarchar] (255) NULL,
[Deactivated] [float] NULL,
} ON [PRIMARY]
Here was my query as well as yours, they both seem to work the same:
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 + '%'
)
)
the query i wrote:
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((tbl3215.completed=0) and (itttitle is not null or ittdescription is not null) and (dbo.tblfltwrkcntr.wrkcntr = @WrkCntr))
From which table would you like sample data from?
here is some from tblassignment
INSERT INTO tblassignment VALUES (1, '20050001.0', '1', '2005-03-07 00:00:00', '2005-03-07 00:00:00', 1382, 0);
some from tbl3215
insert into tbl3215 VALUES(
1 20050199 Turn on telephone line. Phone requires on and local commercial access. A phone is needed in rom so that she can have constant contact with the organization . 5/5/2005 12:00:00 AM 5/19/2005 12:00:00 AM 12 EACCS/\r\nMichael Shaw, \r 1 0 10 Approved 1 5/27/2005 12:00:00 AM C Some from tblfltwrkcntr
Insert into tblfltwrkcnter VALUES(
4 4 SCB SCB tryinghard@sql.com 0 5 21 SCB SCBH tryinghard@sql.com 0 6 22 SCB SCBM tryinghard@sql.com 0 7 24 SCM SCMR tryinghard@sql.com 0); The output i would like is to see the requirements displayed in a webpage. I just need help with the returning value where SCMC = all requirements from SCMP, SCBN, SCBM, SCBT..etc to be diplayed and them only. Right now i am getting . all requirements when i select SCB or SCM and when i select the specific workcenter, i get no results.
Thank You, please let me know if there is something else i need to post
-
Joe
I don't think Ken meant any offence. We do get a few chancers on here who want us to work out everything for them, as you have observed! We're not suggesting you're one of them.
You have already provided the CREATE TABLE statements - that's good. I think what would be helpful from here is if you give us enough rows of sample data for us to be able to test the queries that we write. It makes it easier for us if you provide it in valid SQL so that we can just run it and start testing. Something like this:
INSERT INTO MyTable (col1, col2, col3) VALUES (1, 2, 'a')
INSERT INTO MyTable (col1, col2, col3) VALUES (3, 4, 'b')
INSERT INTO MyTable (col1, col2, col3) VALUES (5, 6, 'b')
Or...
INSERT INTO MyTable
SELECT 1, 2, 'a' UNION
SELECT 2, 4, 'b' UNION
SELECT 5, 6, 'c'
Obviously you will need to change anything that might be confidential.
Also your expected result set might look something like this:
Result1 Result2 Result3
------- ------- -------
cat dog bird
toast butter marmalade
Thanks and good luck
John
-
Joe,
As John indicated, we do not mean any offence but in order to solve the problem we need a 'test bed'.
Your CREATE TABLE statements are now fine. (As a lot of the columns do not seem to be referenced in the query I did suggest some cut down versions in order to make creating the test data easier.)
Your INSERT statements for tbl3215 and tblfltwrkcntr do not use correct syntax and the one for tbl3215 is difficult to decipher. It would seem that the ittnumber in tblassignment and tbl3215 are different so none of the queries so far will produce results. (ie The data is no good for testing)
Good luck,
Ken
-
thanks Sir,
I have had a rough day, i think the melon on my head is growing from banging it off the desk too many times
My apologies for the snappy remark. Ok, so i will try and give you what you want.
tbl3215:
insert into tbl3215 (id, ittnumber, itttitle, ittdescription, ittjustification, techsolution, datereceived, daterequired,customer, orgid, funded, hbapid, disapproved, disreason, completed, completiondate, priority) VALUES ( 1, 20050199, Turn on telephone line., Phone requires , and local commercial access., Bldg, A phone is needed , 5/5/2005 12:00:00 AM, 5/19/2005 12:00:00 AM, 12 \r\n Shaw, \r\nXXX-5866, 1, 0, 10, Approved, 1, 5/27/2005 12:00:00 AM, C)
tblfltwrkcntr
INSERT into tblfltwrkcntr (id, fltwrkcntrid, flt, wrkcntr, wrkcntremail, deactivated) VALUES(1,1,SCX,SCX,myemail@youremail.com, 0)
tblassignment
INSERT into tblassignment(id,ittnumber,assfltwrkid,dateass,datecmplt,recordID,OnHold) VALUES(1, 20050001, 1, 3/7/2005 12:00:00 AM, 3/7/2005 12:00:00 AM, 1382, 0)
this is one row from each table.
As far as expected result goes, i will try to explain what i am after here. pardon the psuedocode-ish as that seems to be the only way that i can explain it.
display list from tblfltwrkcntr.wrkcntr
select appropriate workcenter
run query
query table based on wrkcntr
return all ittnumber that are against tblfltworkcntr.assigned
does that help?? i hope. This backend query is what is getting displayed on a webpage...from my experience so far it seems that its better to not worry about the asp until you get a working query. i learned that the hard way....not too much programming under my belt so be gentle..lol
thanks, much appreciated
-
ken, none taken and i apologize. I have not had too much luck with people explaining what they want so far.
when you say " Your INSERT statements for tbl3215 and tblfltwrkcntr do not use correct syntax and the one for tbl3215 is difficult to decipher" what do you mean?? it runs in sql so how can it not work?? is there no built in error checking? this seems to get deeper! LOL would you mind explaining a little more? would be much appreciated, i do not want to learn the wrong way and then have to unlearn....
what can help you help me?
thanks sir
-
Joe
I don't think your insert statements will work, because you have not enclosed string and date values in inverted commas. But that isn't too important - we can put those in for you. What is important is that we really need more than one row from each table. Your query joins tbl3215 with tblassignment on ittnumber, and yet the value for these is different in each table in the data you supplied. Therefore there will be no match in the join and no rows returned. Please will you give us some more data so that we can test our queries, and also the precise results you expect to achieve given that data.
Another tip I would offer is to alias your tables and format your queries to make them easily readable. Here is an example of what I mean based on your query:
SELECT DISTINCT
t.ittnumber
,t.itttitle
,t.daterequired
FROM dbo.tblassignment a
INNER JOIN dbo.tbl3215 t
ON t.ittnumber = a.ittnumber
INNER JOIN dbo.tblfltwrkcntr f
ON f.fltwkcntrid = a.assfltwrkid
WHERE
(
t.completed=0
AND (t.itttitle IS NOT NULL OR t.ittdescription IS NOT NULL)
AND f.wrkcntr = @WrkCntr
 
John
-
Still a little unsure;
I understand this:
SELECT DISTINCT
t.ittnumber
,t.itttitle
,t.daterequired
The t stands for table i presume in this case?
This is where i am not understanding: Why the tblassignment a? I thought we referenced it as t?
FROM dbo.tblassignment a
INNER JOIN dbo.tbl3215 t
ON t.ittnumber = a.ittnumber
why the dbo.tblassignment a?
-
Joe
In the FROM clause, tblassignment is aliased as a, tbl3215 as t, and tblfltwrkcntr as f. These are arbitrary aliases whose purpose is to make your code easier to read. They don't have to be just one letter; I could equally have chosen chicken, turkey and quail as a, t and f. I'm not sure where you got "table i" from.
Hope that helps
John
-
LOl, when i said "table i presume" i meant i am assuming that you are talking about the letter t standing for tables. i (I) should have stated "I Presume you mean the t in t.ittnumber stands for table"
that is all..lol
-
Joe
LOL indeed! t does stand for table, but as I explained, I could have used any other letter(s) instead.
John
-
The data in the tblassignment field is there for one reason. When a new workcenter gets assigned a requirement, so the data i posted was the first row in each table. the ittnumber can be made to match the one from tbl3215.
I will go ahead and repost some more data for you.
tblassignment:
INSERT INTO tblassignment VALUES (1, '20050001', '1', '2005-03-07 00:00:00', '2005-03-07 00:00:00', 1382, 0);
INSERT INTO tblassignment VALUES (2, '20050001', '5', '2005-03-07 00:00:00', '2005-03-07 00:00:00', 1383, 0);
INSERT INTO tblassignment VALUES (3, '20050002', '1', '2005-03-07 00:00:00', '2005-03-07 00:00:00', 1384, 0);
INSERT INTO tblassignment VALUES (4, '20050002', '4', '2005-03-07 00:00:00', '2005-03-07 00:00:00', 1385, 0);
INSERT INTO tblassignment VALUES (5, '20050002', '5', '2005-03-07 00:00:00', '2005-05-03 00:00:00', 1386, 0);
INSERT INTO tblassignment VALUES (6, '20050003', '1', '2005-03-07 00:00:00', '2005-03-07 00:00:00', 1387, 0);
INSERT INTO tblassignment VALUES (7, '20050003', '4', '2005-03-07 00:00:00', '2005-03-07 00:00:00', 1388, 0);
INSERT INTO tblassignment VALUES (8, '20050003', '5', '2005-03-07 00:00:00', '2005-04-30 00:00:00', 1389, 0);
INSERT INTO tblassignment VALUES (9, '20050004', '1', '2005-03-07 00:00:00', '2005-03-07 00:00:00', 1390, 0);
INSERT INTO tblassignment VALUES (10, '20050004', '4', '2005-03-07 00:00:00', '2005-03-07 00:00:00', 1391, 0);
INSERT INTO tblassignment VALUES (11, '20050005', '1', '2005-03-07 00:00:00', '2005-03-07 00:00:00', 1392, 0);
notice that the 3rd column (wrkcntrID) changes upon if the workcenter changes(in this case SCBN,SCMP, SCBM..etc you get the idea)
tbl3215:
INSERT INTO tbl3215 VALUES (1, '20050199.0', 'Turn on telephone line.', ' Phone requires on base, and local commercial access. Bldg Rm 4 ext ', 'A phone is needed in rom so that she can have constant contact with the organization and the .', '', '2005-05-05 00:00:00', '2005-05-19 00:00:00', '12 EACCS/JSTAR\r\nMichael Shaw, SrA\r\n437-5866', '1', '0', '10', 'Approved', '', '1', '2005-05-27 00:00:00', 'C');
INSERT INTO tbl3215 VALUES (2, '20050212.0', 'Move Phone Line', 'Move phone line from Material Superintendent office to the office in Bldg . (There are no room numbers assignd to the rooms.)', 'The material Superintendent will be moving out of his office due to a new contractor taking over that workspace. Hisw phone line needs to be moved into the office as they will be sharing office space.', '', '2005-05-09 00:00:00', '2005-05-23 00:00:00', 'Det 3, 609 ASUS, OL-B\r\nRamiro Ramirez-Sanchez, TSgt\r\nDSN (318) 437-2097', '1', '0', '10', 'Approved', '', '1', '2005-05-31 00:00:00', 'C');
INSERT INTO tbl3215 VALUES (3, '20060444.0', 'Upgrade Computer Systems', 'Reqeust new desktop computer systems with 3 Ghz processor, 512 mb memory, Win XP Pro, Office XP Pro, 40gig hard drive, integrated audio, DVD/CDRW drive, speakers, keyboard, 2-button scroll mouse, integrated ethernet and 17 inch flat screen monitors. These systems will be utilized in our site located in .', 'Current systems are outdated and unreliable. Systems run very slow which often results in critical reports and suspenses being delayed to higher headquarters on WRM issues. New systems would vastly improve system reliability and office performance.', '', '2005-12-05 00:00:00', '2006-01-04 00:00:00', 'Det 3, 609 ASUS, OL-B\r\nLarry W. Marlowe\r\nDSN: (318) 437-2185', '1', '0', '99', 'Approved', '', '1', '2005-12-09 00:00:00', 'C');
INSERT INTO tbl3215 VALUES (4, '20050832.0', 'PCMICIA Cards', 'Due to attrition of old cards due to harsh envrionment, frequency of use and mission requirements for new Block 40 KC-135R/T aircraft, we are requesting 25 PCMICIA Cards.', 'Currently each PCMICIA cards, depending on their respective navigation system. These cards are required for loading current flight data/flight plans into the . We have one PCMICIA card that is issued to each of the aircrews while in theater. These cards have information on them. The tempo of 7 days a week/24 hours a day take their toll on these cards and 6 of these cards are no longer serviceable. We are also in the process of upgrading the aircraft navigation systems, and the new system operates much more efficiently with 2 PCMICIA cards. 25 additional cards would double our current PCMICIA card requirement from 23 to 46 and allow us to keep 2 cards for operations staff and/or attrition. These new cards will remain in theater assigned to the . Call with questions.', '128MB ATA Flash Card (PC Card)\r\n\r\n379ECS/SCBH does not have funds or bench stock of ATA Storage Flash Cards available at this time. Customer must submit Form 9 for purchase of the Flash Cards. Technical solution was provided due to ATA PC Cards utilized in notebook systems, telecommunication devices and networking equipment, SimpleTech ATA PC Cards are the ideal choice for high capacity data transfer applications. They also include the following features: Commercial, extended and industrial temperature support, hot swappable, light weight, compact, rugged, and PCMCIA compliant\r\n \r\nPrice $78.94 each\r\n\r\n\r\nQuantity = 25\r\n\r\n\r\nTotal $1973.50', '2005-09-23 00:00:00', '2005-10-23 00:00:00', 'john doe\r\n437-2670', '1', '0', '79', 'Approved', '', '1', '2005-10-10 00:00:00', 'C');
INSERT INTO tbl3215 VALUES (5, '20050836.0', 'lan Drops for Weather', 'Bldg , Rm 5 needs one additional drop installed.so we can put our printer on the network.\r\nBldg 3896, Rm 6 needs telephone number added to jack 25.', 'We recently moved into this bldg and didn''t get the sufficient amount of drops installed. We want our computer to be on the server so we can print from all of our computers. Also 5333 is one of our 4 main lines for our customers. We are a tech support help desk for weather forecasters on tactical wx equipment in the .', '', '2005-09-23 00:00:00', '2005-10-23 00:00:00', 'jane doe\r\n437-5337', '1', '0', '97', 'Approved', '', '1', '2005-10-07 00:00:00', 'C');
tblfltwrkcntr:
INSERT INTO tblfltwrkcntr VALUES (7, 24, 'SCM', 'SCMR', 'mymail@yourmail.com', '0');
INSERT INTO tblfltwrkcntr VALUES (8, 25, 'SCM', 'SCMPO', 'mymail@yourmail.com', '0');
INSERT INTO tblfltwrkcntr VALUES (9, 29, 'SCM', 'SCMC', 'mymail@yourmail.com', '0');
INSERT INTO tblfltwrkcntr VALUES (10, 30, 'SCB', 'SCBN', 'mymail@yourmail.com', '0');
INSERT INTO tblfltwrkcntr VALUES (11, 32, 'SCS', 'SCSV', 'mymail@yourmail.com', '0');
here is my sql query statement broken down a little better for readability
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((tbl3215.completed=0) and (itttitle is not null or ittdescription is not null) and (dbo.tblfltwrkcntr.wrkcntr = @WrkCntr))
How do you get the coding colors to make it even easier?
-
Joe
That looks as if it will fit the bill. Unfortunately, I'm off home now and I have three weeks out of the office, so I probably won't be able to look at this again this month! Hopefully somebody else will be able to help you... you may need to post again so that your question appears in the new posts section of the site.
Good luck
John
-
well, that kind of sucks. how do you bump a thread without blatantly bumping it?? Maybe i will just keep posting updates...and hopefully i get it by then.
Thanks
-
such a newbie mistake! I got it though. Basically, in the table tblfltwrkcntr, there was a column called wrkcntrID that SHOULD have been in numerical order but there were old entries deleted. So the numbering went something like this:
1, 2,3,4,5,6 21,22,23,24,25,26,27,31,32,33,34,35,36 so somewhere along the lines someone had edited the old rows and did some deletions. My SQL statement was looking for values that did not exist!
therfore, i made the id field the primary key and changed the sql statement like so and whamo it worked!
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.id = dbo.tblassignment.assfltwrkid
where((tbl3215.completed=2) and (itttitle is not null or ittdescription is not null) and (dbo.tblfltwrkcntr.wrkcntr = @WrkCntr))
all i had to do was change it it tblfltwrkcnter.id and everything worked. another learning experience..lol
thank you all for the help!
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply