this is how I'd do it

Post #75,702 by SpiceWare 1/20/03 2:01:36 PM 1/20/03 2:06:41 PM Reply	this is how I'd do it select t.*,\n (u1.FIRSTNAME \|\| ' ' \|\| u1.LASTNAME) AT_FULLNAME,\n (u2.FIRSTNAME \|\| ' ' \|\| u2.LASTNAME) OB_FULLNAME,\n (u3.FIRSTNAME \|\| ' ' \|\| u3.LASTNAME) RB_FULLNAME\n from tickets t,\n users u1,\n users u2,\n users u3\n where t.ASSIGNEDTO = u1.USERID (+)\n and t.OPENEDBY = u2.USERID (+)\n and t.REQUESTEDBY = u3.USERID (+)\n u1.USERID would be NULL if there wasn't a match. W/out the (+) the result would be empty if any USERID didn't match. I've standardized our company to use this format for queries. It makes them much easier to read and maintain. Note: I'm using Oracle Darrell Spice, Jr. [link\|http://www.spiceware.org/\|SpiceWare] - We don't do Windows, it's too much of a chore Edited by SpiceWare Jan. 20, 2003, 02:06:41 PM EST Expand All History
Post #75,703 by inthane-chan 1/20/03 2:06:00 PM Reply	Do you do that... ...even when the DB is guaranteed to always have a match between UserID and the other fields? Just curious, as a n00b db programmer, always looking out for Best Practices, etc. Any deity worthy of a graven image can cobble up a working universe complete with fake fossils in under a week - hey, if you're not omnipotent, there's no real point in being a god. But to start with a big ball of elementary particles and end up with the duckbill platypus without constant twiddling requires a degree of subtlety and the ability to Think Things Through: exactly the qualities I'm looking for when I'm shopping for a Supreme Being.
Post #75,706 by drewk 1/20/03 2:24:33 PM Reply	As to whether you should do that It sort of depends on the DB you're using. If you're using something like MySQL without triggers and foreign key constraints[1] then you can't really "guarantee" that there will really always be matches. Adding field constraints as in Postgres or Oracle, you can guarantee that a mis-typed query will never produce invalid data. Any time I think I can rely on an assumption that is only enforced by policy, and not by the system itself, the assumption is always proved wrong at some point. I like having assumptions spelled out in the code/queries. [1] Yes, I know there are versions that have it. I don't know anyone who has migrated a production system to one of these versions yet. === Microsoft offers them the one thing most business people will pay any price for - the ability to say "we had no choice - everyone's doing it that way." -- [link\|http://z.iwethey.org/forums/render/content/show?contentid=38978\|Andrew Grygus]
Post #75,707 by SpiceWare 1/20/03 2:30:06 PM Reply	how's it guaranteed? is the relationship being enforced by the database? I've been bit a couple times where data that was "guaranteed to be there" wasn't. While I don't always use it, if there's a chance of data missing I'll use (+) and test for null on the key field using ISNULL() in the program. NULLS are hard to see when running the query interactively, so I'd then use the NVL function: NVL(p1,p2) = if p1 is null then return p2 else return p1 select t.,\n nvl(u1.FIRSTNAME, 'data missing') AT_FIRSTNAME,\n nvl(u2.FIRSTNAME, 'data missing') OB_FIRSTNAME,\n nvl(u3.FIRSTNAME, 'data missing') RB_FIRSTNAME\n from tickets t,\n users u1,\n users u2,\n users u3\n where t.ASSIGNEDTO = u1.USERID (+)\n and t.OPENEDBY = u2.USERID (+)\n and t.REQUESTEDBY = u3.USERID (+) I found that using (+) is a quick(performance) way to get a list of what's not* in the other file. To see all tickets with an invalid ASSIGNEDTO value, I'd use this: select t.*,\n from tickets t,\n users u1\n where t.ASSIGNEDTO = u1.USERID (+)\n and u1.USERID is null\n Darrell Spice, Jr. [link\|http://www.spiceware.org/\|SpiceWare] - We don't do Windows, it's too much of a chore
Post #75,714 by inthane-chan 1/20/03 2:53:02 PM Reply	Enforced by DB.
Post #75,704 by drewk 1/20/03 2:19:48 PM Reply	This is how I'd do it SELECT t.,\n (u1.FIRSTNAME \|\| ' ' \|\| u1.LASTNAME) AT_FULLNAME,\n (u2.FIRSTNAME \|\| ' ' \|\| u2.LASTNAME) OB_FULLNAME,\n (u3.FIRSTNAME \|\| ' ' \|\| u3.LASTNAME) RB_FULLNAME\n FROM tickets t,\n LEFT JOIN users u1 ON t.USERID = u1.USERID,\n LEFT JOIN users u2 ON t.USERID = u2.USERID,\n LEFT JOIN users u3 ON t.USERID = u3.USERID This will solve the problem of non-matching records that you fixed on Oracle with the (+) operator. If you can assume there will always be a match, you can do: SELECT t.,\n (u1.FIRSTNAME \|\| ' ' \|\| u1.LASTNAME) AT_FULLNAME,\n (u2.FIRSTNAME \|\| ' ' \|\| u2.LASTNAME) OB_FULLNAME,\n (u3.FIRSTNAME \|\| ' ' \|\| u3.LASTNAME) RB_FULLNAME\n FROM tickets t,\n users u1,\n users u2,\n users u3\n WHERE\n t.USERID = u1.USERID AND\n t.USERID = u2.USERID AND\n t.USERID = u3.USERID The second will (I believe) be faster. I can't verify the first version is SQL92 compliant, but works[1] on both Postgres and MySQL. [1] "I have not tested this, only verified it for correctness." === Microsoft offers them the one thing most business people will pay any price for - the ability to say "we had no choice - everyone's doing it that way." -- [link\|http://z.iwethey.org/forums/render/content/show?contentid=38978\|Andrew Grygus]

Welcome to IWETHEY!