CODEWITHSUNDEEP
mysqlsql
Fracu
Fracu

Reputation: 845

Get coordinates (row,column) of a table in a MySQL query

Is there any way to get (row,column) coordinates of a table?

Example:

tbl1:                              tbl2:
+--------+--------+--------+       +------+-----------+---------+
| Name   |   ROW  |   COL  |       |  ROW |  COL_0    |  COL_1  |
+--------+--------+--------+       +------+-----------+---------+
| VALUE1 |     0  |     0  |       |    0 |  OPEN     |  CLOSE  |
| VALUE2 |     2  |     1  |       |    1 |  RIGHT    |  LEFT   |
| VALUE3 |     1  |     0  |       |    2 |  OFFLINE  |  ONLINE |
+--------+--------+--------+       +------+-----------+---------+

The result should look like:

+--------+----------+     
| Name   |   ROWCOL | 
+--------+----------+ 
| VALUE1 |   OPEN   | 
| VALUE2 |   ONLINE | 
| VALUE3 |   RIGHT  | 
+--------+----------+

Thanks in advance!

Upvotes: 0

Views: 192

Answers (2)

fvu
fvu

Reputation: 32973

I think relying on implicit data like rownumbers or so are not a good idea.

I propose to reorganize tbl2 to something like this:

create table tbl2 (
   rowid integer,
   colid integer,
   textvalue varchar(32)
);

Data will look like

0 0 OPEN
0 1 CLOSE
1 0 RIGHT
1 1 LEFT
...

which allows you to obtain your result with

select tbl1.name as Name, tbl2.textvaluevalue as ROWCOL
from tbl1, tbl2
where tbl1.ROW = tbl2.rowid and tbl1.COL = tbl2.colid

Upvotes: 0

Abe Miessler
Abe Miessler

Reputation: 85116

This is an odd requirement but I believe this will work:

SELECT name, 
CASE COL
  WHEN 0 THEN COL_0
  WHEN 1 THEN Col_1
END as rowcol
FROM tbl1 as t1
INNER JOIN tbl2 as t2
ON t1.ROW = t2.ROW

Upvotes: 2

Related Questions