Oracle left outer join query

select a,last_note_user,c,d,iso_src from X,Y,Z left outer join W ON (W.last_note_user=Z.userid AND W.user_ten=Y.iso_src)

when I am executing the above query it is giving me the error "Y.iso_src" is a invalid identifier.However,Y.iso_src is a valid identifier and it exist in the system table. Is something wrong with the above left outer join?? Please help me out!!!

--------------Solutions-------------

You cannot mix JOIN syntaxes in Oracle this way.

A JOIN condition can only refer tables previously joined using ANSI JOIN syntax.

Use this:

SELECT a, last_note_user, c, d, iso_src
FROM X
CROSS JOIN
Y
CROSS JOIN
Z
LEFT OUTER JOIN
W
ON W.last_note_user = Z.userid
AND W.user_ten = Y.iso_src

It's probably a good habit to not mix ANSI and non-ANSI join syntax as the other responses indicate. However, it is technically possible, by isolating the non-ANSI joins into a subquery:

create table X as (select dummy a, dummy c, dummy d from dual);

create table Y as (select dummy iso_src from dual);

create table Z as (select dummy userid from dual);

create table W as (select dummy last_note_user, dummy user_ten from dual);

select a,last_note_user,c,d,iso_src
from (select a, c, d, iso_src, userid FROM X,Y,Z) xyz
left outer join W
ON (W.last_note_user=xyz.userid AND W.user_ten=xyz.iso_src);

A LAST_NOTE_USER C D ISO_SRC
- -------------- - - -------
X X X X X

1 rows selected

Category:sql Time:2009-07-22 Views:1
Tags: sql oracle

Related post

  • Join query or subquery 2009-03-18

    Are there rules of thumb for developers when to use join instead of subquery or are they the same. --------------Solutions------------- Depends on RDBMS. You should compare execution plans for both queries. In my experience with Oracle 10 and 11, exe

  • Symfony Propel: Join Query Across different Database 2009-08-28

    I have two tables that should be joined together by a foreign key relationship, unfortunately they are separate into two different database. My question is that is it possible for me to create a Criteria thingy that allows me to create cross database

  • Joining tables in Oracle (multiple outer joins) 2011-08-25

    I am trying to convert an Informix query to Oracle: The Informix query looks like this: SELECT r.aa, n.bb, nd.cc,u.id, ud.dd, g.attr FROM tab1 u, tab2 ud, OUTER (tab3 a, tab4 n, tab5 nd, tab6 r, OUTER (tab7 g, tab8 atr)) WHERE r.xx = n.xx AND n.nas =

  • How can I create a MySQL JOIN query for only selecting the rows in one table where a certain number of references to that row exist in another table? 2008-10-06

    I have two tables in my database, called ratings and movies. Ratings: | id | movie_id | rating | Movies: | id | title | A typical movie record might be like this: | 4 | Cloverfield (2008) | and there may be several rating records for Cloverfield, lik

  • How to create a dataview In Sharepoint with data from a join query? 2009-01-22

    I have 3 Lists in Sharepoint. I want to create a dataview that is a join of 3 tables. Table1 is joined with Table2 on FieldA Table 2 is joined to Table3 on FieldB Table1 has duplicate values in FieldA so I need to only return one value to join with T

  • a JOIN query edited so that a field that has unique values 2009-05-06

    I have a JOIN query that pulls the last 6 "topic" records (the records with the highest topic_ids that are have a topic_status of 0, i.e. it's not spam): SELECT topic_title, topic_slug, meta_value, topic_poster FROM `folio_topics` as Topics INNER JOI

  • Django .."join" query? 2009-07-31

    guys, how or where is the "join" query in Django? i think that Django dont have "join"..but how ill make join? Thanks --------------Solutions------------- If you're using models, the select_related method will return the object for any foreign keys y

  • Which MySQL JOIN query is more efficient? 2009-11-12

    Given the following table structure: CREATE TABLE user ( uid INT(11) auto_increment, name VARCHAR(200), PRIMARY KEY(uid) ); CREATE TABLE user_profile( uid INT(11), address VARCHAR(200), PRIMARY KEY(uid), INDEX(address) ); Which join query is more eff

  • C# Display Join Query in DataGridView at designTime using TableAdapter, etc 2009-12-04

    I have a DataGridView I also have some tableAdapters (groupTableAdapter, userTableAdapter) generated from sqlserver database. I have created a JOIN query in userTableAdapter that shows users with their correspoding groupname. Of course, I've got the

  • mysql join query using like? 2009-12-18

    I have been trying to get this working for quite a while now but it just doesn't seem to work, maybe it is is not even possible, what i am wanting to do is to perform a mysql join query using like, such as this example i found... SELECT * FROM Table1

  • MS-Access: What could cause one form with a join query to load right and another not? 2010-03-11

    Late breaking news! If I manually create the new record with SQL and then open the form in edit mode using the code below, it works. I would still like to know what the problem was before, but at least I can get on with my application now. Form1 Form

  • How to deal with the Hibernate hql multi-join query result in an Object-Oriented Way? 2010-03-12

    How to deal with the Hibernate hql multi-join query result in an Object-Oriented Way? As I see it returns a list of Objects. yes, it is tricky and only you who write the query know what should the query return (what objects). But are there ways to si

  • (Oracle Performance) Will a query based on a view limit the view using the where clause? 2010-03-22

    In Oracle (10g), when I use a View (not Materialized View), does Oracle take into account the where clause when it executes the view? Let's say I have: MY_VIEW = SELECT * FROM PERSON P, ORDERS O WHERE P.P_ID = O.P_ID And I then execute the following:

  • Basic join query understanding 2010-05-10

    I know this very silly, but can anybody help me in understanding what does this join query is doing in elabortive description? SELECT j1.* FROM jos_audittrail j1 LEFT OUTER JOIN jos_audittrail j2 ON (j1.trackid = j2.trackid AND j1.field = j2.field AN

  • Want to avoid the particular rows from select join query- See description 2010-05-13

    I have a Select Left Join Query whis displays me the rows for the latest changedone(its a time) column name ("field" should not be equal) column name ("trackid" should not be equal), and column name "Operation should be "UPDATE" ", below is the query

  • Join query in MySQL cursor 2010-05-29

    We can declare a cursor like this DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1; Can we use a join query instead of a simple query? --------------Solutions------------- Yes, you can use all the various clauses in the SELECT statement except the

  • A join query for three tables!! for the given condtion below 2010-06-25

    I have three tables Orders table and customers table and orderstatus table, both order and customer table have customerid as common field,and order and orderstatus have order_status_id as common field customer table have firstname, lastname, phone an

  • Mysql join query for multiple "tags" (many-to-many relationship) that matches ALL tags? 2010-07-16

    I am trying to query for Objects that match ALL of a given set of Tags. Basically I want users to be able to add on more and more Tags to filter or "narrow down" their search results, kind of like newegg.com does. My table structure is a table of Obj

  • MySQL implications of 'like' vs 'join' query 2010-08-15

    I've got two tables - Videos and Playlists Typically I would create the two respective tables and a multi-value table for the 1-Many relationship, such that : Videos = [pk, videoId, title, etc..] Playlists = [pk, title, etc..] Playlists_Videos = [Vid

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 0.406 (s). 13 q(s)