query generate the information one column in two columns in one query

Posted on

Question :

I have a question related with a query in sql. I have the next table:

id        name         
1         name1
2         country1
3         name2
4         country2

I need to call the results so:

id         name      id   country
1          name1      2    country1
3          name2      4    country2

Thanks for your help in advance.

Answer :

To answer the question here is the query

SELECT A.*,B.*
FROM mytable A INNER JOIN mytable B
ON A.id = B.id -1 WHERE MOD(A.id,2)=1;

I made this query up based on staring at the output

It would have been nice to have a table called name_country perhaps like this:

CREATE TABLE name_country
SELECT A.id name_id,B.id country_id
FROM mytable A INNER JOIN mytable B
ON A.id = B.id -1 WHERE MOD(A.id,2)=1;
ALTER TABLE name_country ADD PRIMARY KEY (name_id,country_id);
ALTER TABLE name_country ADD UNIQUE INDEX (country_id,name_id);

That way going forward, you can maintain the name_country table in such a way that name and country do not have to be 1 id apart in the original table. You could freely enter names in bulk. Then later on, attach the country values.

Once you can create a name_country table, you could then do joins like this:

SELECT B.id,B.name,C.id,C.name
FROM name_country A
INNER JOIN mytable B ON A.name_id = B.id
INNER JOIN mytable C ON A.country_id = C.id;

UPDATE 2012-08-15 15:04 EDT

The query I first suggested

SELECT A.*,B.*
FROM mytable A INNER JOIN mytable B
ON A.id = B.id -1 WHERE MOD(A.id,2)=1;

is the answer

First here is your sample data

DROP DATABASE IF EXISTS cabita;
CREATE DATABASE cabita;
USE cabita
CREATE TABLE mytable 
(id int not null auto_increment,
name varchar(20),primary key (id));
insert into mytable (name) values ('name1'),('country1');
insert into mytable (name) values ('name2'),('country2');
select * from mytable;

Here is your sample data loaded

mysql> DROP DATABASE IF EXISTS cabita;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE cabita;
Query OK, 1 row affected (0.00 sec)

mysql> USE cabita
Database changed
mysql> CREATE TABLE mytable
    -> (id int not null auto_increment,
    -> name varchar(20),primary key (id));
Query OK, 0 rows affected (0.14 sec)

mysql> insert into mytable (name) values ('name1'),('country1');
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into mytable (name) values ('name2'),('country2');
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from mytable;
+----+----------+
| id | name     |
+----+----------+
|  1 | name1    |
|  2 | country1 |
|  3 | name2    |
|  4 | country2 |
+----+----------+
4 rows in set (0.01 sec)

Here is my original query executed

mysql>     SELECT A.*,B.*
    ->     FROM mytable A INNER JOIN mytable B
    ->     ON A.id = B.id -1 WHERE MOD(A.id,2)=1;
+----+-------+----+----------+
| id | name  | id | name     |
+----+-------+----+----------+
|  1 | name1 |  2 | country1 |
|  3 | name2 |  4 | country2 |
+----+-------+----+----------+
2 rows in set (0.03 sec)

mysql>

While the query works for your sample data, you have to pay close attention to how name relates to country. You would have to make sure of three things in order for my query work

  1. the id of name is an odd number
  2. the id of country is an even number
  3. the id of name is one less id of country

That’s one relationship I was able to take advantage of.

There is another relationship visible in your sample data: The number at the end of the each field. If you can enter names in bulk and then later load country in bulk, you would then have to rely on the number at the end of each name field. Such a query would look like this:

SELECT A.*,B.*
FROM mytable A INNER JOIN mytable B
ON SUBSTR(A.name,-1)=SUBSTR(B.name,-1)
WHERE A.name > B.name;

and would execute as follows

mysql>     SELECT A.*,B.*
    ->     FROM mytable A INNER JOIN mytable B
    ->     ON SUBSTR(A.name,-1)=SUBSTR(B.name,-1)
    ->     WHERE A.name > B.name;
+----+-------+----+----------+
| id | name  | id | name     |
+----+-------+----+----------+
|  1 | name1 |  2 | country1 |
|  3 | name2 |  4 | country2 |
+----+-------+----+----------+
2 rows in set (0.00 sec)

mysql>

or this query

SELECT A.*,B.*
FROM mytable A INNER JOIN mytable B
ON SUBSTR(A.name,-1)=SUBSTR(B.name,-1)
WHERE LEFT(A.name,4)='name'
AND LEFT(B.name,7)='country';

which would execute as follows

mysql>     SELECT A.*,B.*
    ->     FROM mytable A INNER JOIN mytable B
    ->     ON SUBSTR(A.name,-1)=SUBSTR(B.name,-1)
    ->     WHERE LEFT(A.name,4)='name'
    ->     AND LEFT(B.name,7)='country';
+----+-------+----+----------+
| id | name  | id | name     |
+----+-------+----+----------+
|  1 | name1 |  2 | country1 |
|  3 | name2 |  4 | country2 |
+----+-------+----+----------+
2 rows in set (0.00 sec)

mysql>

All these queries make assumptions on the content of the data. Such queries would produce very poor performance results. This is why I suggested creating an external table to perform the JOIN that would couple the id of name and its country together.

UPDATE 2012-08-16 17:19 EDT

In answer to the last question from the comment

SELECT A.*,B.*,
FROM mytable A
INNER JOIN mytable B ON SUBSTR(A.name,-1)=SUBSTR(B.name,-1)
INNER JOIN mytable C ON SUBSTR(A.name,-1)=SUBSTR(C.name,-1)
WHERE LEFT(A.name,4)='name'
AND LEFT(B.name,7)='country';
AND LEFT(C.name,11)='description';

If there is a different table involved for the descriptions, then perhaps this:

SELECT A.*,B.*,
FROM mytable A
INNER JOIN mytable B ON SUBSTR(A.name,-1)=SUBSTR(B.name,-1)
INNER JOIN difftable C ON SUBSTR(A.name,-1)=SUBSTR(C.name,-1)
WHERE LEFT(A.name,4)='name'
AND LEFT(B.name,7)='country';
AND LEFT(C.name,11)='description';

Leave a Reply

Your email address will not be published.