CODEWITHSUNDEEP
sqlmysql
W.Gerick
W.Gerick

Reputation: 92

mySQL - query to combine two tables

I have two tables. The first one holds information about cities:

Locations:

locID     |   locationID    |  locationName    |  countryCode    |
1         |      2922239    |     Berlin       |      de         |
2         |      291074     |     Paris        |      fr         |
3         |      295522     |    Orlando       |      us         |
3         |      292345     |    Tokyo         |      jp         |

There is a second table, which holds alternative names for locations. There might be NO alternative name for a location in the Locations table: AlternateNames:

altNameID |   locationID    |  alternateName    |
1         |      2922239    |     Berlino       | 
2         |      2922239    |     Berlina       |
3         |      2922239    |     capital       |
4         |      291074     |     Parisa        |
5         |      291074     |     Pariso        | 
6         |      295522     |     Orlandola     |
7         |      295522     |     Orlandolo     |

What I would like to get is the locationID, name and the countryCode of a location for a location name search like "Berlin", or "Ber":

|   locationID   |   name       |  countryCode    |
|   2922239      |   Berlin     |        de       |

However, if the user searches for "Berlino", I would like to get the alternateName back:

|   locationID   |   name       |  countryCode    |
|   2922239      |   Berlino    |        de       |

The "locationName" has a higher priority than the alternateName, if the searchterm matches both.

I can't figure out how to build a query to do that. Since the name can come from one of the two tables, it seems quite difficult to me.

Any help is really appreciated!

Upvotes: 1

Views: 1584

Answers (4)

James
James

Reputation: 4152

SELECT 
   locationID, 
   (IF (name LIKE 'Ber%', name, alternateName)) as name, 
   countryCode 
FROM
   Locations l LEFT JOIN AlternateNames a ON (l.locationID = a.locationID)
WHERE
  name LIKE 'Ber%'
OR
  alternateName LIKE 'Ber%'

Obviously, use variable substitution (:searchname) in place of 'Ber%'.

Add LIMIT 1 to the end of the query if you only want 1 row returned.

Edited based on comment about preference for alternateName. Made LEFT join per Schultz999 suggestion

Upvotes: 3

Ed Mays
Ed Mays

Reputation: 1760

This query joins the two tables via a union query, ranks them and takes the top result from the union.

Try this:

SELECT
      locationID
    , `name`
    , countryCode
FROM
    (
        SELECT
                  locationID
                , `name`
                , countryCode
                , 1 AS priority
        FROM
            locations
        WHERE
            locationName LIKE 'Ber%'
        UNION
            SELECT
                  a.locationID
                , a.alternateName AS `name`
                , l.countryCode
                , 2 as priority
            FROM
                AlternateNames a
                INNER JOIN Locations l
                    ON a.locationID = l.locationID
            WHERE
                a.alternateName LIKE 'Ber%'
    ) u
ORDER BY
    priority
LIMIT 1

Upvotes: 0

Chandu
Chandu

Reputation: 82893

Try this:

SELECT a.locationID, :locationName, a.countryCode // :locationName being the city name parameter FROM cities a, locations b WHERE a.locationID = b.locationID AND (a.locationName = :locationName OR b.alternateName = :locationName ) 

SELECT a.locationID, a.locationName, a.countryCode 
  FROM cities a
 WHERE a.locationName = :locationName
 UNION ALL
SELECT a.locationID, b.alternateName locationName, a.countryCode
  FROM cities a, locations b
 WHERE a.locationID = b.locationID
   AND b.alternateName =  :locationName

Upvotes: 0

alampada
alampada

Reputation: 2409

something like this should do this.

select a.locationID, coalesce(b.alternateName,a.locationName), a.countrycode FROM table1 a LEFT JOIN table2 b ON a.locationId=B.locationID where a.locationName=?

Upvotes: 0

Related Questions