writing 2 columns from 1 column

Question

I have a table

Registration
╔══════╦══════════════╦══════════════╦═══════════════╗  
║name1 ║   name2      ║ name2's state║   entity_id   ║
╠══════╬══════════════╬══════════════╣═══════════════╣
║  1   ║     92       ║   complete   ║     152       ║
║  92  ║     1        ║   pending    ║     153       ║
╚══════╩══════════════╩══════════════╩═══════════════╝

Is it possible to replace a row's name2 state into "name1's state"? I mean A state 1-->92 is "complete" I know that state 92--->1 is "pending" the question is how to display the same information in the table in one row like in my_table? Is it possible?

my_table
╔══════╦══════════════╦═══════════════╦═══════════════╦═══════════════╗  
║name1 ║     name2    ║ name2's state ║ name1's state ║   entity_id   ║
╠══════╬══════════════╬═══════════════╣═══════════════╣═══════════════╣
║  1   ║     92       ║   complete    ║  pending      ║     152       ║
║  92  ║     1        ║   pending     ║  complete     ║     153       ║
╚══════╩══════════════╩═══════════════╩═══════════════╩═══════════════╝

Yes, this is called a join. Are you at all familiar with SQL? — Gordon Linoff, May 01 '14 at 15:08
I would like to write a query which is depending on names and I'm confused if it is possible from one table — user3582116, May 01 '14 at 15:11
Are you trying to update your table? or just select information? — Richard Christensen, May 01 '14 at 15:14
Only select but I don't know how to join (make) column name1's state from the table Registration — user3582116, May 01 '14 at 15:17

score 2 · Answer 1 · edited May 23 '17 at 10:27

What you will want to do is something like this

SELECT
 t1.name1,
 t1.name2,
 t1.name2state as name1state,
 t2.name2state as name2state,
 t1.entity_id
FROM
 names t1
JOIN
 names t2
ON
 t1.name2 = t2.name1;
ORDER BY
 t1.name1;

here is an SQL fiddle you can play with.

http://sqlfiddle.com/#!2/79222/4

of course this just gives you the data you asked for and an example of a join, you will need to appy this and probably change it in a way to suit your needs.

Edit:

JOIN keyword for a standard join, this JOIN clause will only show records where the ON statement is true. The ON clause is denoting which two columns are being compared for the JOIN. so in the above SQL statement you are saying you want to SELECT your data from TABLE 1 that has TABLE 2 JOINED onto TABLE 1 where TABLE1's column name2 = TABLE2's column name1. There are many other types of JOINs you can use to manipulate that data. Look at this Stackoverflow thread to see all of the types of JOINs you can use and what they do.

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

writing 2 columns from 1 column

1 Answers1