3

I have the following issue. I have 2 tables I want to Join "TableALong" and "TableBLong"

TableALong

ID, Name, Store,Age
1, John, Walmart, 5
2, Johnny, Walmart, 8
3, Johnny, Target , 10
4, Bill, Shoppers, 2
5, Joe, Target, 3

TableBLong
ID, Name, Store, StoreAddress
1, John, Walmart, 35353 Address
1, John, Walmart, 53544 Address
2, Johnny, Walmart, 35353 Address

I want to do something like an ALIAS before I join where I have something like:

SELECT A.ID, A.NAME, A.STORE, A.AGE, B.STOREADDRESS
FROM TableALong as A, TableBLong as B
ON A.NAME = B.NAME and A.STORE = B.STORE

This is invalid in oracle. What is the correct query to get this to work in oracle? I assume it is a left join I want? (After the join, there will be multiple rows for each item in TableALong.)

Rolando
  • 58,640
  • 98
  • 266
  • 407
  • 1
    I think this is invalid in all SQL engines. In Oracle you can use legacy implicit joins (`FROM A, B`) or modern explicit joins (`FROM A JOIN B ON...`) but the `ON` clause alone has no meaning. – Álvaro González Apr 01 '16 at 19:27
  • @Rolando To correct your your query you can try by deleting "as" keyword from your query. As "FROM TableALong A, TableBLong B" now everything should work..If it is working then let me know... – Shiv Apr 01 '16 at 19:46

1 Answers1

5

The fixed query:

SELECT A.ID,
       A.NAME,
       A.STORE,
       B.STOREADDRESS as yourAlias /* AS is ok for column aliases ... */
FROM TableALong A                  /* ... but not for table aliases */
     LEFT OUTER JOIN TableBLong  B /* JOIN syntax */
       ON (A.NAME = B.NAME and A.STORE = B.STORE)
WHERE ...

Instead of LEFT OUTER you could have INNER, FULL OUTER, ...; see here for more.

Community
  • 1
  • 1
Aleksej
  • 22,443
  • 5
  • 33
  • 38
  • 2
    It is worth pointing out that the problem is the `as` in the `FROM` clause. Oracle does not support this. And, `NATURAL JOIN` is a bug waiting to happen, so don't use it. – Gordon Linoff Apr 01 '16 at 19:26
  • If I wanted to add a where caluse where A.NAME='John' is that possible? – Rolando Apr 01 '16 at 19:31
  • You can add it at the end of the query exactly the way you wrote it. – Aleksej Apr 01 '16 at 20:05