0

I am sitting at a problem currently. I have imported an open source geolocation database.

I have two tables I want access to.

Table A:
ID   | lat | lon
200 | 48 | 12

Table B:
ID   | Type | Value
200 | City   | Munich
200 | State | Bavaria

Now I want to have a result like this:
ID   | lat | lon | TypeValue | TypeValue
200 | 48 | 12  | Munich        | Bavaria

Is this possible in only one query?

EDIT: The only value I know is "Munich"

EDIT2: This is what I've got so far:

SELECT 
geodb_coordinates.lat AS lat,
geodb_coordinates.lon AS lon,
geodb_textdata.text_val AS text
FROM 
geodb_coordinates,
geodb_textdata
WHERE 
geodb_coordinates.loc_id = geodb_textdata.loc_id AND
geodb_textdata.text_val LIKE :location
GROUP BY geodb_textdata.text_val
ORDER BY LENGTH(geodb_textdata.text_val)
LIMIT 3
Ferhat Sayan
  • 216
  • 1
  • 4
  • 19
  • Yes, it is possible with self join. What have you tried so far? Is the number of records in table B with the same id 2 or can it be more, such as city -> state -> country -> continent? – Shadow Jan 28 '16 at 10:40
  • You can't have two columns with same name... – Haytem BrB Jan 28 '16 at 10:41
  • @Shadow what I currently got is, that I can have "ID | lat | lon | TypeValue". No success in getting the other TypeValue. And yes. Table B can have more rows with the same ID. – Ferhat Sayan Jan 28 '16 at 10:46
  • Then this question is duplicate of all dynamic pivot questions. – Shadow Jan 28 '16 at 10:49
  • Possible duplicate of [MySQL pivot row into dynamic number of columns](http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) – Shadow Jan 28 '16 at 10:51
  • @Shadow First time I've ever heard about pivot. Thank you very much! Going to try out the queries ASAP. – Ferhat Sayan Jan 28 '16 at 11:16
  • It is quite a common question. The technique is either referred to as pivoting or cross tabulated (crosstab) query. Excel and Access are really great at it, mysql, well, not that good at it. – Shadow Jan 28 '16 at 11:19

2 Answers2

3

Try something like:

Select A.ID,A.Iat, A.Ion, B.Value as TypeValue1, C.Value as TypeValue2
from 
A inner join B on A.ID=B.ID and B.Type='City'
Inner join B as C on B.ID=C.ID and C.Type='State'
Haytem BrB
  • 1,528
  • 3
  • 16
  • 23
  • Should be C.Type='State' If there are more than 2 records (see comment from OP), then this query is not enough. – Shadow Jan 28 '16 at 10:52
1

Try something like

SELECT b1.ID, a.lat, a.lon, b1.Value, b2.Value
FROM table_b as b1 join table_a as a on a.ID=b1.ID, table_b as b2
where b1.ID=b2.ID and b1.`Type`='City' and b2.`Type`='State' and b1.Value='Munich';

I guess there is only one city per id, right?

rostbot
  • 342
  • 3
  • 12