如何从不同的数据库中连接两个表？ [关闭]

Question

Database 1 : sandbox

table 1 : coordinates

+----------------------------------------+
|coord_id | section_name | station_number|
+----------------------------------------+
|   1     |    A         |  A7           |
|   2     |    B         |  B20          |
|   3     |    C         |  C3           |
|   4     |    D         |  D14          |
|   5     |    E         |  E9           |
+----------------------------------------+

database 2 : phone

table 2 : workstations

I only have READ privilege

+----------------------------+
|   ID  |       ws    |  pod |
+----------------------------+
|    1  |  COMP123  |  A07   |
|  2    |  COMP345  |  B20   |
|  3    |  COMP567  |  C03   |
|  4    |  COMP891  |  D14   |
|  5    |  COMP444  |  E09   |
+----------------------------+

PROBLEM:

I only have READ privilege on that second table of that database.

I want to join both tables so I can display the "ws" field on screen for my PHP script. My "station_number" field values are written differently from the "pod" field (they have a zero in front of the letter if it is a single digit after the letter) does it make a difference?

I've seen examples online on how to join but for some reason I can't get it to work.

Do I need to create an extra field on my main Table to store the field values from "ws" or what? I'm a bit confused.

Thanks in advance!

Answer 1

Yes, it makes a difference if there's a 0 in one and not in the other. You'll have to modify the value of one or the other. Something like SUBSTR(station_number, 0, 1) + LPAD(SUBSTR(station_number, 2), 2, '0').

And to join the tables from different databases, you just have to put the database name in front of the table name. Probably something like sandbox.coordinates.

So your query might look something like this:

SELECT
    *
FROM
    sandbox.coordinates c
    INNER JOIN phone.workstations w
        ON (SUBSTR(c.station_number, 0, 1) + LPAD(SUBSTR(c.station_number, 2), 2, '0')) = w.pod

---

If you can update one of your tables to make the columns match, then your query is as simple as:

SELECT
    *
FROM
    sandbox.coordinates c
    INNER JOIN phone.workstations w
        ON c.station_number = w.pod