MySQL join 2 tables but rename columns because they have the same name

Question

I have 2 tables,

admin, pricing

• admin contains columns (id, date_created, type, value)
• pricing contains columns (id, date_created, relation, value)

I want to do a select that joins the two tables where pricing.relation = admin.id

How do I rename the value, id and date_created rows so they do not overwrite each other?

This is the kinda thing i'm trying:

$sub_types = $database->query('
    SELECT 
    pricing.*,
    admin.*
        FROM 
        pricing,
        admin
            WHERE pricing.relation = admin.id
');

Durning

You can use the AS operator here, i.e. pricing.id as price_id – Wilk

Exactly - AS it the thing you want to use. Not only it will help you with the duplicate names, but it also encourages you to use only those columns you really want :) I mean: do we always want ALL the columns when using "SELECT *" ? Or are we just lazy to manually type the columns we really want? :) – Cadmium

lazy... etc. :) problem? hehehehe – Durning

No problem at all. I do the same :) I am just saying... – Cadmium

Answer 1

You can use aliases:

SELECT p.id as pid, 
       p.date_created as pricing_date, 
       p.type, p.value as pricing_value,
       a.id as aid, 
       a.date_created as admin_date,
       a.relation, 
       a.value as admin_value
FROM pricing p
inner join admin a on p.relation = a.id

Elke

You just gave me an idea how to solve a different problem thanks. – Diedrediefenbaker