-1

I am trying to pull store data for locations in the US and around the world.

select
    s.store,
    s.store_name,
    s.store_city,
    s.state,
    st.description as "STATE_DESC",
    s.country_id,
    c.country_desc
from
    store s,
    country c
left join state st on st.state = s.state
where c.country_id = s.country_id
and s.store in ('123456', '654321')

I get:

ORA-00904: "S"."STATE": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 13 Column: 34

If I join the state table regularly, it only pulls up locations in the US and does not show NULL values in the state or state description column.

How do I join the state table?

store columns:
STORE STORE_NAME STORE_CITY STATE COUNTRY_ID

state columns:
STATE DESCRIPTION

I want:

STORE STORE_NAME STORE_CITY STATE STATE_DESC COUNTRY_ID COUNTRY_DESC
123456 DALLAS STORE DALLAS TX Texas US United States
654321 GERMANY STORE BERLIN null null DE Germany

I ended up doing a VLOOKUP to add the state description in Excel.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Aly S
  • 9
  • 1
  • 2
    Don combine comma separated tables with joins use only joins, you can also try the format button for code instaed of using html tags – nbk Aug 22 '23 at 13:49
  • 1
    Explicit JOINs are evaluated _before_ comma separated, implicit ones. This means the ON clause has no access to s columns. – jarlh Aug 22 '23 at 14:30
  • Tip of today: Always use modern, explicit `JOIN` syntax everywhere. Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed! – jarlh Aug 22 '23 at 15:40
  • Please read the editor help re how to format using 'markdown' without html, including code blocks & inline code. https://stackoverflow.com/editing-help Please avoid social & meta commentary in posts. Please clarify via edits, not comments; delete & flag obsolete comments.Debug questions require a [mre]. [ask] [Help] ) [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) – philipxy Aug 24 '23 at 06:18
  • [Mixing implicit and explicit JOINs](https://stackoverflow.com/q/761545/3404097) – philipxy Aug 24 '23 at 06:23
  • Answers do not belong in question posts, post an answer in an answer post. [answer] – philipxy Aug 24 '23 at 06:42

1 Answers1

0

Instead of joining tables by using the implicit comma seperated way, Aly S joined all the tables with the "modern, explicit JOIN syntax".

select
 s.store,
 s.store_name,
 s.store_city,
 s.state,
 st.description as "STATE_DESC",
 s.country_id,
 c.country_desc
from store s
left join state st
 on s.state = st.state
join country c
 on s.country_id = c.country_id
where s.store in ('123456', '654321')
jarlh
  • 42,561
  • 8
  • 45
  • 63