CODEWITHSUNDEEP
mysqlsqlselect
Alladin
Alladin

Reputation: 1062

Select Statement To Retrieve Same Column Twice

I Have 2 tables:

Table 1 named category_desription, it contains 2 fields: category_name and category_id.

Table 2 named category includes the category_id and parent_Category_id fields which are foreign keys from category_id in the first table.

I want to select 4 fields whereby the result will contain category_id and its name category_name and parent_category_id and its name as well which happens to be category_name as well

I couldn't figure out how to do it so far so I wish if someone assist me in this.

Upvotes: 0

Views: 437

Answers (3)

madcap
madcap

Reputation: 90

This is a simple name conflict issue in SQL, which can be solved easily by using alias

e.g. 

Select t1.category_id, t1.category_name, t2.category_id, t2.category_name
from table1 t1 join table2 t2 on <some condition..>

You can also give different names to the result column

 Select t1.category_id, t1.category_name as 'Parent Category Name',
 t2.category_id, t2.category_name as 'Category Name'
 from table1 t1 join table2 t2 on <some condition..>

I hope it solves your issue.

Upvotes: 0

saikumarm
saikumarm

Reputation: 1575

 with category_desc as
 (select 1 as category_id, 'Category 1' as category_name
    from dual
  union all
  select 2 as category_id, 'Category 2' as category_name from dual),
category as
 (select 1 as category_id, 2 as parent_category_id from dual)
select c.category_id,
       c_d.category_name,
       c.parent_category_id,
       pc_d.category_name parent_category_name
  from category c
  join category_desc c_d
    on c_d.category_id = c.category_id
  join category_desc pc_d
    on pc_d.category_id = c.parent_category_id

use category_desc with different alias to find parent_category details

Upvotes: 0

Darshak
Darshak

Reputation: 867

I hope this will solve your prblm

SELECT  category_id, 
(Select category_name from table1 where category_id= table2.category_id) as category_name , 
parent_category_id, 
(Select category_name from table1 where category_id= table2.parent_category_id) 
as parent_category_name from table2

Upvotes: 1

Related Questions