5

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
');
LoneWOLFs
  • 2,306
  • 3
  • 20
  • 38
Jimmyt1988
  • 20,466
  • 41
  • 133
  • 233
  • 3
    You can use the `AS` operator here, i.e. `pricing.id as price_id` – Sammaye Oct 12 '12 at 11:47
  • 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? :) – Vafliik Oct 12 '12 at 11:51
  • lazy... etc. :) problem? hehehehe – Jimmyt1988 Oct 12 '12 at 11:52
  • No problem at all. I do the same :) I am just saying... – Vafliik Oct 12 '12 at 11:53

1 Answers1

9

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
juergen d
  • 201,996
  • 37
  • 293
  • 362