August 17, 2009 at 9:17 am
SELECT e.* FROM ESTDETL e
WHERE e.JOB = '04-3001'
AND e.PHASE > 0
AND (e.ITEM NOT IN
(SELECT t.edItem FROM TASKS t
INNER JOIN PROJECTS p ON t.taidProject = p.idProject
WHERE TRIM(p.prJob) = TRIM(e.JOB)
)
)
ORDER BY e.PHASE, e.ITEM
EstDetl Table
JOB
| Project Table
+--------- prJOB
idProject --+
| Tasks Table
| edItem
+---- taidProject
Does the above SQL look correct for the diagram? There can only
be one project per JOB and multiple tasks per project. We want
to be able to import the details in EstDetl where the detail
item hasn't already been imported into the tasks table
August 17, 2009 at 9:33 am
It looks OK, however your design would allow multiple projects per job. If that's not the case, it doesn't make a lot of sense to build the separate table unless they are wide tables.
Avoid SELECT * is another comment I'd make.
August 17, 2009 at 9:38 am
Like Steve, I'm not sure why you'd have separate tables for projects and jobs if it's a 1:1 relationship. There are reasons to do that kind of thing, mainly having to do with annoyingly wide tables, but it is something to mention anyway.
The first thing I saw that struck me was the use of "TRIM" in SQL. There isn't such a function in T-SQL. There are LTrim and RTrim, but not a single command that does both.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 17, 2009 at 9:49 am
GSquared (8/17/2009)
Like Steve, I'm not sure why you'd have separate tables for projects and jobs if it's a 1:1 relationship. There are reasons to do that kind of thing, mainly having to do with annoyingly wide tables, but it is something to mention anyway.The first thing I saw that struck me was the use of "TRIM" in SQL. There isn't such a function in T-SQL. There are LTrim and RTrim, but not a single command that does both.
On the TRIM, it is AdvantageSQL so it is OK.
AFA the separate tables, the EstDtl table goes with the current application, the Projects/Tasks tables are for another project that imports the records from the EstDtls table.
Thanks for your comments, though..
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply