Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

How do I get "table.column" format from a select/join?

( #41067=categorized question: print w/ replies, xml ) Need Help??
Contributed by Anonymous Monk on Nov 11, 2000 at 09:27 UTC
Q&A  > database programming


Description:

My problem is that I have two (more, actually, but I'm only going to talk about two here) tables. They both have a column named gene_id, but one of them (primary.gene_id) actually identifies the gene, and the other (secondary.gene_id) specifies that the row in the secondary table should be attached to the gene.

My problem is that if there is nothing in the secondary table that matches the primary table, gene_id gets overwritten with undef when I call $query->fetchrow_hashref.

I am currently using a LEFT JOIN to join the tables.

Is there a way that I can solve this without renaming the column in the secondary table? I'd really like for the column names to be the same just so I can keep my database (mostly) consistent.

Answer: How do I get "table.column" format from a select/join?
contributed by extremely

Lordy, don't change the column names. You can alias the name with AS in most SQL dialects:

SELECT p.gene_id, s.gene_id AS SecGene, ...

If the gene_id in the second table is a foreign key into the primary table, you really want it to have the same name, for your and everyone elses sanity. I gotta ask, is the gene_id field the field that you use to tie the two tables together? If so, and you have:

... FROM primary LEFT JOIN secondary ON primary.gene_id=secondary.gene +_id ...
in your select, then you really shouldn't ask for anything but the primary.gene_id from the left side of the join.
Answer: How do I get "table.column" format from a select/join?
contributed by derobert

Do not use SELECT *. Not only are you probably pulling far more data than you need (which may or may not matter, depending on how big that data is) but as you've noticed, duplicate field names are problematic and make your code fragile (what happens when the DBA adds another column?). Instead, select exactly the columns you need, and under the names you want:

SELECT p.gene_id AS parent_gene_id, c.gene_id AS child_gene_id, c.name AS child_name FROM parent p LEFT JOIN child c ON ( p.gene_id = c.gene_id ) /* etc. */

Please (register and) log in if you wish to add an answer



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • Outside of code tags, you may need to use entities for some characters:
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others browsing the Monastery: (14)
    As of 2014-09-30 12:17 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      How do you remember the number of days in each month?











      Results (367 votes), past polls