CODEWITHSUNDEEP
phpmysql
chris
chris

Reputation: 461

SQL select from 2 databases on the same server that have the exact same structure

I am trying to select from 2 databases on the same server that have the exact same structure and db2 is a continuance of db1. The problem is I am trying to select the same info "different unique id's though" from both databases and I need the query formatted as such: link_id AS id, title AS title to present the info correctly using $row['title'] etc. Here is my query:

$CONF['mysql_query'] = 'SELECT db1.l.link_id AS id, db1.l.title AS title, db2.r.link_id AS id, db2.r.title AS title FROM db1.links AS l LEFT OUTER JOIN db2.links AS r ON (r.link_id = l.link_id) WHERE l.link_id IN ($ids)';

Why will it not allow me to use title AS title etc for both databases? Is there a way I can do this?

Upvotes: 0

Views: 75

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

You can't have 2 columns with same name in a SELECT list. That's why you can't have two id or two title.

If you want to join the two tables, use:

$CONF['mysql_query'] = "
        SELECT l.link_id AS idL
             , l.title AS titleL
             , r.link_id AS idR
             , r.title AS titleR
        FROM db1.links AS l
          LEFT OUTER JOIN db2.links AS r
            ON (r.link_id = l.link_id)
        WHERE l.link_id IN ($ids)
    ";

The above will show all ids and titles that satisfy the l.link_id IN ($ids) condition from the first table and the corresponding title from the second table. NULLs in the 3rd and 4th column will show if there is no corresponding row in the second table.

If you want a UNION, e.g. all rows from the first table (that satisfy the l.link_id IN ($ids) condition) plus all rows from the second table, use:

$CONF['mysql_query'] = "
        SELECT link_id AS id
             , title AS title
        FROM db1.links AS l
        WHERE l.link_id IN ($ids)
    UNION                   
        SELECT link_id AS id
             , title AS title
        FROM db2.links AS r
    ";

This will possibly show rows with same id, one from each table. Or even duplicate rows. Duplicate rows can be avoided easily though, by putting UNION ALL instead of UNION.

Upvotes: 2

Ovais Khatri
Ovais Khatri

Reputation: 3211

use alias title in single quotes, it will allow you

select t1.Title as 'title' , t2.Title as 'title' from Table1 t1,Table t2

Upvotes: 0

Related Questions