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

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.

Originally posted as a Categorized Question.

Comment on How do I get "table.column" format from a select/join? Download Code

Replies are listed 'Best First'.
Re: How do I get "table.column" format from a select/join?⭐ by extremely (Priest) on Nov 11, 2000 at 16:46 UTC
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, ...` [download] 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 ...` [download] in your select, then you really shouldn't ask for anything but the `primary.gene_id` from the left side of the join.	[reply] [d/l] [select]
Re: How do I get "table.column" format from a select/join? by derobert (Initiate) on Jan 27, 2009 at 21:43 UTC
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. */` [download]	[reply] [d/l] [select]