Geek Answers Handbook

SQL - JOIN syntax understanding, order

One part of my purpose is to convert SQL queries to Oracle SQL. I executed the query in MS access, here is the relationship table, SQL query and query result: Query design

Query result

ACCESS QUERY SQL

SELECT PUBLISHER.PUBLISHER_CODE, PUBLISHER.PUBLISHER_NAME, BOOK.TITLE, BOOK.TYPE, INVENTORY.BRANCH_NUM, BRANCH.BRANCH_NAME
FROM PUBLISHER INNER JOIN (BRANCH INNER JOIN (BOOK INNER JOIN INVENTORY ON BOOK.BOOK_CODE = INVENTORY.BOOK_CODE) ON BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM) ON PUBLISHER.PUBLISHER_CODE = BOOK.PUBLISHER_CODE
WHERE (((BOOK.TYPE)='FIC') AND ((BRANCH.BRANCH_NAME)='Henry on the Hill'))
ORDER BY PUBLISHER.PUBLISHER_NAME;

Thinking that MS SQL might work in oracle, I tried:

SQL> SELECT PUBLISHER.PUBLISHER_CODE, PUBLISHER.PUBLISHER_NAME, BOOK.TITLE, BOOK.TYPE, INVENTORY.BRANCH_NUM, BRANCH.BRANCH_NAME
  2  FROM PUBLISHER INNER JOIN (BRANCH INNER JOIN (BOOK INNER JOIN INVENTORY ON BOOK.BOOK_CODE = INVENTORY.BOOK_CODE) ON BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM) ON PUBLISHER.PUBLISHER_CODE = BOOK.PUBLISHER_CODE
  3  WHERE (((BOOK.TYPE)='FIC') AND ((BRANCH.BRANCH_NAME)='Henry on the Hill'))
  4  ORDER BY PUBLISHER.PUBLISHER_NAME;

no rows selected

Obviously, this did not work. So, I somehow did the cropping / pasting / editing thing in my 11g oracle. Here is what I got:

SQL> SELECT PUBLISHER.PUBLISHER_CODE, PUBLISHER.PUBLISHER_NAME, BOOK.TITLE, BOOK.TYPE,
  2   INVENTORY.BRANCH_NUM, BRANCH.BRANCH_NAME
  3  FROM PUBLISHER
  4   inner join BOOK  ON PUBLISHER.PUBLISHER_CODE = BOOK.PUBLISHER_CODE
  5  inner join INVENTORY on INVENTORY.BRANCH_NUM = BRANCH.BRANCH_NUM
  6  inner join BRANCH on INVENTORY.BRANCH_NUM = BRANCH.BRANCH_NUM
  7   where BOOK.BOOK_TYPE = 'FIC'
  8   and BRANCH.BRANCH_NAME = 'Henry on the Hill';
inner join INVENTORY on INVENTORY.BRANCH_NUM = BRANCH.BRANCH_NUM
                                               *
ERROR at line 5:
ORA-00904: "BRANCH"."BRANCH_NUM": invalid identifier

so why am I getting this: ERROR on line 5: ORA-00904: "BRANCH". "BRANCH_NUM": invalid identifier

Does the order of joining justify? As I understand it, I did it sequentially from table to table.

is the meaning of the syntax really? EX:

INVENTORY.BRANCH_NUM = BRANCH.BRANCH_NUM BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM

?

- Oracle SQL, ?

, ?

, -, , , , . !

+4
2

INVENTORY :

inner join INVENTORY ON BOOK.BOOK_CODE = INVENTORY.BOOK_CODE

:

select PUBLISHER.PUBLISHER_CODE, PUBLISHER.PUBLISHER_NAME, BOOK.TITLE, BOOK.TYPE, INVENTORY.BRANCH_NUM, BRANCH.BRANCH_NAME
from PUBLISHER
inner join BOOK on PUBLISHER.PUBLISHER_CODE = BOOK.PUBLISHER_CODE
inner join INVENTORY on BOOK.BOOK_CODE = INVENTORY.BOOK_CODE
inner join BRANCH on BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM
where BOOK.TYPE = 'FIC'
    and BRANCH.BRANCH_NAME = 'Henry on the Hill'
order by PUBLISHER.PUBLISHER_NAME;
+1

, . , , . BRANCH 5, .

MS Access . :

SELECT
  PUBLISHER.PUBLISHER_CODE,
  PUBLISHER.PUBLISHER_NAME,
  BOOK.TITLE,
  BOOK.TYPE,
  INVENTORY.BRANCH_NUM,
  BRANCH.BRANCH_NAME
FROM
  PUBLISHER
  INNER JOIN (
    BRANCH INNER JOIN (
      BOOK INNER JOIN INVENTORY
        ON BOOK.BOOK_CODE = INVENTORY.BOOK_CODE
      )
      ON BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM
    )
    ON PUBLISHER.PUBLISHER_CODE = BOOK.PUBLISHER_CODE
WHERE
  (
    (
      (BOOK.TYPE)='FIC'
    )
    AND
    (
      (BRANCH.BRANCH_NAME)='Henry on the Hill'
    )
  )
ORDER BY
  PUBLISHER.PUBLISHER_NAME

:

SELECT
  PUBLISHER.PUBLISHER_CODE,
  PUBLISHER.PUBLISHER_NAME,
  BOOK.TITLE,
  BOOK.TYPE,
  INVENTORY.BRANCH_NUM,
  BRANCH.BRANCH_NAME
FROM
  PUBLISHER
  INNER JOIN BRANCH
  INNER JOIN BOOK
  INNER JOIN INVENTORY
             ON BOOK.BOOK_CODE = INVENTORY.BOOK_CODE
                AND
                BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM
                AND
                PUBLISHER.PUBLISHER_CODE = BOOK.PUBLISHER_CODE
WHERE
  BOOK.TYPE='FIC'
  AND
  (BRANCH.BRANCH_NAME = 'Henry on the Hill')
ORDER BY
  PUBLISHER.PUBLISHER_NAME

, , ANSI :
: ANSI, , : -)

SELECT
  PUBLISHER.PUBLISHER_CODE,
  PUBLISHER.PUBLISHER_NAME,
  BOOK.TITLE,
  BOOK.TYPE,
  INVENTORY.BRANCH_NUM,
  BRANCH.BRANCH_NAME
FROM
  PUBLISHER,
  BRANCH,
  BOOK,
  INVENTORY
WHERE
  BOOK.TYPE='FIC'
  AND
  (BRANCH.BRANCH_NAME = 'Henry on the Hill')
  AND
  PUBLISHER.PUBLISHER_CODE = BOOK.PUBLISHER_CODE
  AND
  BOOK.BOOK_CODE = INVENTORY.BOOK_CODE
  AND
  BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM
ORDER BY
  PUBLISHER.PUBLISHER_NAME

, , :

SELECT
  PUBLISHER.PUBLISHER_CODE,
  PUBLISHER.PUBLISHER_NAME,
  BOOK.TITLE,
  BOOK.TYPE,
  INVENTORY.BRANCH_NUM,
  BRANCH.BRANCH_NAME
FROM
  BRANCH,
  INVENTORY,
  BOOK,
  PUBLISHER
WHERE
  (BRANCH.BRANCH_NAME = 'Henry on the Hill') -- start from most restrictive 
                                             -- condition (concrete branch)  
  AND
  INVENTORY.BRANCH_NUM = BRANCH.BRANCH_NUM   -- get all inventory from this branch
  AND
  BOOK.BOOK_CODE = INVENTORY.BOOK_CODE       -- access book specification 
                                             -- corresponding to inventory
  AND    
  BOOK.TYPE = 'FIC'                          -- of specific type 
  AND
  PUBLISHER.PUBLISHER_CODE = BOOK.PUBLISHER_CODE  -- and finally find 
                                                  -- all publishers of that books
ORDER BY
  PUBLISHER.PUBLISHER_NAME

, .
, ( , Oracle, - ) , . ANSI .

+2

More articles:


All Articles