CODEWITHSUNDEEP
mysql
nzy
nzy

Reputation: 864

Two column name same using join in mysql

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 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

Upvotes: 2

Views: 8216

Answers (4)

user2148571
user2148571

Reputation: 129

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"

Upvotes: 1

Jacky
Jacky

Reputation: 3239

I'm thinking of two ways you can do:

  1. You can create a stored procedure and re-edit the column name on how you want it to look like.

  2. For simple way, just replace

    SELECT *

with column of products.

Upvotes: 0

gprathour
gprathour

Reputation: 15333

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.

Upvotes: 0

maček
maček

Reputation: 77778

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, ...

Upvotes: 2

Related Questions