Hi, Recent interesting discussion on the list, on (just) naming convention reminded me of a related problem which I haven't resolved myself, jet. As slowly I learn SQL (like a blind dog in a meat market), currently I've just started to use table JOINS more extensively ... and I often bump on a "two columns have the same name" error. The point is, that my "large object" often contain sets of (quite) identical component objects; like: a SLED has LEFT_RUNNER and RIGHT_RUNNER, both referring to the same RUNNERS table. And yes, I have sattled with naming convention where table is a plural noun (table RUNNERS), while primary key column name is an unspeciffic singular noun (A_RUNNER). So, when I join the SLEDS table with RUNNERS table (twice: left and right runner, to get a complete bom for a sled): SELECT * FROM sleds s JOIN runners l ON (s.left=l.runner) JOIN runners r ON (s.right=r.runner); .... it doesn't work, when SLED table and RUNNERS table both have the same column (like LENGTH). And it wouldn't work even if I called sled.length a SLED.SLED_LENGTH and runner.length a RUNNER.RUNNER_LENGTH, since RUNNERS are joined twice. Is there an sql-language level solution (idiom) to cope with such queries? And I don't mean: SELECT s.*,r.*,l.* FROM sleds s JOIN runners l ON (s.left=l.runner) JOIN runners r ON (s.right=r.runner); ...since in such case, there would be even more duplicate column names then in the first example. The only way I know to avoid the column name duplication is to explicity select column list: SELECT s.sled,s.length,s....,r.runner as right,r.length as right_length,r....,l.runner as left,l.length as left_length,l.* FROM sleds s JOIN runners l ON (s.left=l.runner) JOIN runners r ON (s.right=r.runner); .... which is truely overtalkative (and thus obfuscates future query analize during code maintenance). To explain the problem a little better, here is a "pseudoSQL" query example, which should ilustrate the problem/solution: SELECT * FROM sleds s JOIN runners(length as left_length, weight as left_weight, runner as left) l USING (left) JOIN runners(length as right_length, weight as right_weight, runner as right) r USING (right); I found one way to get "almost exactly" to this point, that is by createing VIEWs to do the job of renameing columns: CREATE VIEW right_runner AS SELECT length as right_length, weight as right_weight, runner as right FROM runners; CREATE VIEW left_runner AS SELECT length as left_length, weight as right_weight, runner as left FROM runners; .... one problem with this solution is that during the livetime of an application, columns of tables (like RUNNERS) change (like by adding new attributes), and it's quite tricky to promote those changes smoothly up to the top "SELECT * FROM sleds ...", particularly if that last one is actually a CREATED VIEW. ... the other problem is that it exploads application/db schema, and thus make it more difficult to "comprehend" during future maintenance. A real live example of such query is quering addreses of a person: residential_address, office_address, delivery_address, etc... all of them from a single ADDRESSES table. Or people telephone numbers from PHONE_NUMERS table. I have seen the WINDOW-PARTITION-OVER syntax very similar to this problem, but countrary to JOINS, WINDOW can be defined outside of a select column list, which is helpfull. I havent found such construct for JOINs. Is there any? Is there an ordinary SQL phrase/idiom (laconic/tarse in it's form) to get the above result? -R -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
