CODEWITHSUNDEEP
sqlselect
user2500179
user2500179

Reputation: 307

Modifying a SELECT query

I have a query like this:

 SELECT initials, name
 FROM employee e, projects p
 WHERE e.country = p.country

Until now, both tables used an abbreviation for the country columns. Like "SWE" for Sweden and "ITA" for Italy.

In the future, the employee table will use names for the country columns. Like "Sweden" and "Italy".

Is it somehow possible to change my query so it can match abbreviations with names? Like "SWE" = "Sweden" and "ITA" = "Italy".

Thanks.

Upvotes: 0

Views: 79

Answers (4)

Paul Varghese
Paul Varghese

Reputation: 1655

Always in country table, if country name starts with first three letters of the country in the employee table then you can use substring operator

SELECT initials, name
FROM employee e, projects p
WHERE upper(substring(e.country,1,3)) = upper(p.country)

Upvotes: 0

Rick Hoving
Rick Hoving

Reputation: 3575

Although I fafor the solution by juergen, another solution will be altering the two tables to the new format.

UPDATE employee
SET country = "SWEDEN"
WHERE country = "SWE"

Do this for all the countries you have.

Upvotes: 0

SchmitzIT
SchmitzIT

Reputation: 9572

I'd say the best solution is creating a third table where you match the current abbreviation with the full country name. You can then join both tables on that.

CountryTable (countryAbbreviation, countryName)

The select would then be something like this:

SELECT initials, name
FROM employee e JOIN countryTable c ON c.countryName = c.country 
    JOIN projects p ON p.country = c.countryAbbreviation

Upvotes: 0

juergen d
juergen d

Reputation: 204894

It would be better to have an own country table and the other tables referencing to that.

country table
-------------
id
name
abbreviation

Upvotes: 10

Related Questions