Correct Join?

  • 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

  • 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.

  • 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

  • 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