CODEWITHSUNDEEP
mysqlsqljoininner-join
ahmed adel
ahmed adel

Reputation: 89

how to join two columns into single column from the same table

Iam trying to get the results from two columns with the same type into single column for example 

table 
id      secId
1         2
3         1
5         1
1         4

i want to retrive them like that where id or secId = 1

id
2
3
4
5

i have tried inner join but it gives me 2 columns with data redundancy

Upvotes: 0

Views: 2461

Answers (4)

Winsher McCoy
Winsher McCoy

Reputation: 11

Here's the simplest code for that:

Select Case When id = 1 then secId end ID from yourtable

Upvotes: 0

ryanyuyu
ryanyuyu

Reputation: 6486

You can also use a CASE statement to pick between the two columns. 

SELECT CASE 
        WHEN id = 1 --if this is a 1 select the other col
            THEN secId
        ELSE id --otherwise just select this column
        END AS newCol
FROM yourTable
WHERE id = 1
    OR secId = 1

Upvotes: 0

Andreas Wederbrand
Andreas Wederbrand

Reputation: 39951

You could use a union to get that

select id from yourTable where secId = 1
union
select secId from yourTable where id = 1

Use union all if you don't like the way union also does a distinct

Upvotes: 3

Jean-François Savard
Jean-François Savard

Reputation: 21004

You don't need any Join for that, this simple query would do it

select if(id=1, secId, id)
  from yourTable
  where id = 1 or secId = 1;

You select all data that have id or secId equal to 1 then, depending on which of both is equal to 1 you display secId or id.

Assuming here only one of both value can be 1.

If there is the possibility to have two values, then you can use the union syntax as @AndreasWederbrand described.

Upvotes: 4

Related Questions