CODEWITHSUNDEEP
sqlite
user1883614
user1883614

Reputation: 945

SQLite Get value from other table

Let's say I have a table1: abbr_state, thing1, thing2

And table2 with: state, code (the index), thing3

So abbr_state = GA, while state = Georgia and code = GA.

Is there anyway I can do a SELECT where it's:

SELECT abbr_state,thing1,thing2 FROM table1 ORDERY BY blah blah blah...

But make it so that when it does abbr_state, it comes from the full state name instead of the abbr?

Upvotes: 1

Views: 301

Answers (3)

eddyizm
eddyizm

Reputation: 585

You will need a way to link those two tables together. Your current structure such as it is, will not work. 

/* your example */    
select abbr_state, thing1, thing2
from table1

select state, thing3
from table2

As mentioned above, you need to join the tables. You will need to add another column on table2:

select state, thing3, fk_abbr_state
from table2

Then you can join them on the abbreviation

select table2.state, table1.thing1, table1.thing2
from table1 JOIN table2 ON table1.abbr_state = table2.fk_abbr_state

I hope this helps.

Upvotes: 1

Dhruv Saxena
Dhruv Saxena

Reputation: 1346

The query that could be used is:

SELECT abbr_state, state, thing1, thing2 
FROM table1 INNER JOIN table2
     ON table1.abbr_state = table2.code
ORDER BY blah;

You may add any WHERE conditions before the ORDER BY clause, if the need be. Or, those can even be clubbed in the JOIN statement:

      ....
      ON table1.abbr_state = table2.code
      AND table1.fielda = 'xyz'
      ....

Upvotes: 1

Schwern
Schwern

Reputation: 164689

What you're looking for is a "join" and to do it you need a "join table" linking the state abbreviations to the states themselves.

The join table might be a table with information about the state.

create table states (
    id integer primary key,
    name text unique,
    abbreviation text unique,
    capital text
);

Note that I've enforced that state names and abbreviations must be unique. Not only does this make sure that nobody accidentally gives Alaska and Alabama the same abbreviation, but it also sets up an index to make looking up states by name or abbreviation very fast.

Then you'd join them together using the abbreviation.

select states.name
from table1
join states on table1.abbrev_state == states.abbreviation
order by states.name

Note that once you have the states table, it's safer and more efficient to store the state ID in table1 than the abbreviation. It's faster to compare numbers than strings, it's guaranteed not to change, and the relationship is documented and enforced with a foreign key.

Upvotes: 1

Related Questions