Two column name same using join in mysql

Question

I have been using inner join to join three tables. Now what i finally got is a joined table er where two column name are same and they have diffrent data. one contains locationname and another contain crop name. Now how do i access the locationname.

My production table is as follows: enter image description here

My crops table is as follows: enter image description here

MY location table is as follows: enter image description here

My sql query is as follows:

  Select * from production AS cust 
    INNER JOIN location AS comp ON cust.location_id = comp.location_id
     INNER JOIN crops AS crop ON cust.crop_id=crop.crop_id 
    where cust.year_of_production =2004 AND crop.name="paddy"

The output after executing the sql query is as follows: Here the there are two columnname as name . i want to access the name that contains taplejung . i donot want the column with name that contains paddy. How do i do that

Instead of SELECT *, name all the columns you want...

Rook
– Rook

2014-07-10 04:06:24 +00:00
Commented Jul 10, 2014 at 4:06 — Rook
– Rook, Commented Jul 10, 2014 at 4:06

maček · Accepted Answer · 2014-07-10 04:07:48Z

First off SELECT * is horrible and evil; just select the columns you want

Second, your aliases are really weird. They have no relation to the table names which makes it difficult to read the query

SELECT 
  p.*,
  c.name AS crop_name

FROM products AS p

JOIN location AS l
  ON l.location_id = p.location_id

JOIN crops AS c
  ON c.crop_id = p.crop_id
 AND c.name    = "paddy"

WHERE p.year_of_production = 2004

Here, we're selecting p.* which will select all fields on the products table. Next, we're selecting c.name AS crop_name which will add the crops.name field to your result aliased as crop_name.

This way you can work with crop_name as the field in your result.

Aside from that, I'm not sure which other fields you need from other tables, but you can modify your select to grab all the fields you want

SELECT p.*, c.name AS crop_name, l.name AS location_name, ...

user2148571 · Accepted Answer · 2014-07-10 09:54:25Z

1

May be the following could help:

Select comp.name,production_amount from production
 AS cust INNER JOIN location AS comp ON cust.location_id = comp.location_id 
INNER JOIN crops AS crop ON cust.crop_id=crop.crop_id
 where cust.year_of_production =2004 AND crop.name="paddy"

answered Jul 10, 2014 at 9:54

user2148571

1298 bronze badges

Comments

gprathour · Accepted Answer · 2014-07-10 04:16:13Z

0

If you have same column names in multiple tables, then you can specify it with table name.

For example,

select table1.id, table2.id from table1, table2

or you can use table alias

select t1.id, t2.id from table1 t1, table2 t2

and nothing to worry.

The same is applicable for any kind of joins. Nothing new.

answered Jul 10, 2014 at 4:16

gprathour

15.4k5 gold badges72 silver badges92 bronze badges

Comments

Jacky · Accepted Answer · 2014-07-10 06:41:05Z

0

I'm thinking of two ways you can do:

You can create a stored procedure and re-edit the column name on how you want it to look like.
For simple way, just replace

SELECT *

with column of products.

answered Jul 10, 2014 at 6:41

Jacky

3,2894 gold badges25 silver badges37 bronze badges

Collectives™ on Stack Overflow

Two column name same using join in mysql

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related