0

Is this possible? Currently I am trying to run these series of commands through my database. However, there is an issue with the last line that adds gifts. This is because this table has no primary key set. Do I need to set a primary key for it, or is there another way to deal with this?

SELECT b.title, p.name, m.gift
FROM books b
     JOIN publisher p USING (pubid)
     JOIN promotion m USING (gift);

Error code I am getting is.

SQL Error: ORA-00904: "from$_subquery$_003"."GIFT": invalid identifier 00904. 00000 - "%s: invalid identifier"

piet.t
  • 11,718
  • 21
  • 43
  • 52
Strom
  • 93
  • 1
  • 10
  • 3
    I don't think `using` depends on primary keys. It should just be looking up the column name in the previous tables. – Gordon Linoff Mar 20 '19 at 20:19
  • 2
    Replace SELECT b.title, p.name, m.Gift with SELECT b.title, p.name, Gift - Oracle doesn't like a using field to be qualified in a select. – Jim Castro Mar 20 '19 at 21:32
  • 2
    What's wrong with the simple `ON`? – Eric Mar 20 '19 at 21:56
  • 1
    Please include the error message in your post along with the DDL to reproduce the problem. Looks like it's telling you that `books` or `publisher` doesn't have a `gift` column. – William Robertson Mar 20 '19 at 22:44
  • 1
    PKs & other constraints are not needed to join. Why do you think they are? Any 2 tables can be joined ON any condition. Learn what USING means: `r JOIN s USING (c,...)` is `r JOIN s ON r.c=s.c AND ....`. What exactly are you trying to ask? Please clarify via edits, not comments. – philipxy Mar 21 '19 at 01:08
  • Please in code questions give a [mcve]--cut & paste & runnable code plus desired output plus clear specification & explanation. Minimal means adding minimal problem code to minimal working code. So give minimal code that you show does what you expect & minimal code with the first place you go wrong. (Debugging fundamental.) Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. [ask] – philipxy Mar 21 '19 at 01:11
  • Thank you Philipxy, that helped clear up my issues. Sorry for the confusing title and the lack of clarity in the code. I will endeavor to keep both of these things in mind in the future. – Strom Mar 21 '19 at 01:17
  • So what did you learn when you googled that error message without your particular tables & columns? Or about the SQL JOIN USING clause? Or read the manual or other authoritative reference? (oh--our comments just crossed) – philipxy Mar 21 '19 at 01:18
  • I think it was looking for shared columns, I should have used the ON command to add the gift column. Still learning the SQL syntax, so admittedly I am just not very sharp about picking these things up. – Strom Mar 21 '19 at 01:20
  • PS `r JOIN s USING (c,...)` does `r INNER JOIN s ON r.c=s.c AND ...` then replaces each c column pair by a column c that is COALESCE(r.c,s.c). (Some DBMSs also let you still refer to r.c & s.c.) PS I don't understand "I should have used the ON command to add the gift column"--why not another `using`? – philipxy Mar 21 '19 at 01:37
  • Primarily because Gift was the only column that had no shared columns with the others. – Strom Mar 21 '19 at 01:39
  • Possible duplicate of [difference between ON Clause and using clause in sql](https://stackoverflow.com/q/10432107/3404097) – philipxy Mar 21 '19 at 01:45

1 Answers1

0

The using syntax doesn't rely on the columns involved being primary keys, or any other type of keys for that matter - it just relying on the both tables having columns of the same name that are used for the join (see, e.g., the wikipedia article).

Mureinik
  • 297,002
  • 52
  • 306
  • 350