Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Be aware that deleting and then adding a row in most DBMS will leave a gap in auto-incremented IDs. –  shannon Aug 12 '12 at 23:21
    
Note also that having two columns with identical names (id) doesn't really make much sense. –  Andriy M Aug 16 '12 at 13:22
add comment

1 Answer

up vote 2 down vote accepted

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';
share|improve this answer
    
Is it possible call results in an only query? without create tables? I have updated my question. Thanks for your full answer. –  cabita Aug 15 '12 at 18:07
    
It's good. Thanks very much. I have the last question. It's possible this: screencast.com/t/O3NwmZEKW3nU ?. Thanks a lot!!! –  cabita Aug 16 '12 at 20:49
    
I cannot see another table so I will answer based on the above information –  RolandoMySQLDBA Aug 16 '12 at 21:16
    
Hi Rolando. I'll try the query. Thanks very much. The other table according with our example is : CREATE TABLE IF NOT EXISTS description ( id int(10) unsigned NOT NULL AUTO_INCREMENT, description text NOT NULL, id_id int(10) unsigned NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; INSERT INTO description (id, description, id_id) VALUES (1, 'description1', 2), (2, 'description2', 4), (3, 'description3', 6), (4, 'description4', 8); The id_id is related with the id of mytable. Thanks very much again. –  cabita Aug 17 '12 at 17:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.