Last week, we had the author of TDD for a Shopping Website LiveProject. Friday at 11am Ranch time, Steven Solomon will be hosting a live TDD session just for us. See for the agenda and registration link
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • paul wheaton
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Frits Walraven
Bartenders:
  • Piet Souris
  • Himai Minh

Getting different records in one table using one record from another table

 
Bartender
Posts: 582
14
IntelliJ IDE Spring Fedora
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello all,


I am stumped at the moment about how to write a certain oracle select query.

The flow is that I get the values from one row in table a, and then use different column values in that one row to search for many rows in another table.

What I have done so far is to use joins but it doesn't work because the  values I need from the 'another' table use the same column.




Doing it as so gives a 'ORA-00918 column ambiguously defined' error.



I was able to get it working by creating sub queries for the left joins.  
Is there a better way?



 
Saloon Keeper
Posts: 25470
180
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes. The "AS" clauses allow you to define shorthand IDs for tables (and views), so you can unambiguously reference fields from different sources in the same SELECT. It's not uncommon to have a JOIN clause that looks like "ON c.user_id = f.user_id".
 
Al Hobbs
Bartender
Posts: 582
14
IntelliJ IDE Spring Fedora
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The SQLI posted has aliases for the tables.
When there is one join it works without problems, the problem is when there is more than one and selecting the same column.

Right now the only way to make it not throw that error is to have a subquery with column aliases after the first left join.


if there is a way to give an alias to the column within the join statement that might solve it.

Is that possible to do?


After looking at the original statement again. The problem was that the join statements didn't have the table alias in front of one columns.
It only became a problem after adding more joins so I assumed it was due to using the same column name.
 
Tim Holloway
Saloon Keeper
Posts: 25470
180
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Generally the "real" (fully-qualified) name of a column is going to be something like "databasename.schemaname.tablename.columnname" (Not all databases use quite that structure, but many do). So if you just say "columnname" then the upper levels are assumed from context.

Two column names are not the "same" column if their fully-qualified names are different. So in cases of ambiguity you just need to add better qualifications.
 
Al Hobbs
Bartender
Posts: 582
14
IntelliJ IDE Spring Fedora
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes, that sounds right.
The problem was the I didn't put the table name in front of CD_GRP



Thank you!
 
Aaaaaand ... we're on the march. Stylin. Get with it tiny ad.
Free, earth friendly heat - from the CodeRanch trailboss
https://www.kickstarter.com/projects/paulwheaton/free-heat
reply
    Bookmark Topic Watch Topic
  • New Topic