469,360 Members | 1,799 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,360 developers. It's quick & easy.

Left Outer Join with subqueries?

I need to use a left outer join to get all of one table, and match it to specific instances of another table. Eg, report all of A, and where A has made a specific kind of B, report the name of that B.

Now, to get the specific B, I need to join three other relations together, and then do a string match. That's easy. I can report the subset of A that has made specific kind of B. I can report all of A. I can report all of A that has made all the kinds of B. But I can't report all of A with specific kinds of B.

I've been trying to use a Left outer join to join A to the subquery that'll tell me the specific kinds of B, and it tells me that the 'b' isn't a valid identifier.

EG:
Expand|Select|Wrap|Line Numbers
  1.  
  2. select A.name, B.name 
  3. from A LEFT OUTER JOIN (
  4. select * from B, C, D where b.foo= c.foo AND c.baa= d.baa
  5. AND B.specifictype = 'My Type') ON a.poe= b.poe
  6. group by A.name, B.name;
  7.  
it tells me b.poe isn't a valid identifier on line 4. I've tried putting an alias after the subquery, and joining on that alias instead, and it tells me that alias name isn't a valid identifier. Can someone show me where I'm going wrong?

Edit - I'm using Oracle9i EE, SQL*Plus 9.2
May 14 '07 #1
6 48669
frozenmist
179 Expert 100+
Hi,
Can you give the query that you tried with aliasing?
That would be more helpful. This query is any way not correct as it has no aliasing.
Post it soon
Cheers
May 15 '07 #2
Okay - II'll have to use made-up names, though, because this is a question of an assignment, and my uni takes plagiarism REALLY seriously. (but this is my query with the names all subbed out. )

Expand|Select|Wrap|Line Numbers
  1. select name, fruitjuicename
  2. from student LEFT OUTER JOIN
  3. (select * from school, cafeteria, drinks
  4. where school.cafID = cafetera.cafID AND
  5. cafeteria.drinkID = drink.drinkID AND
  6. drinktype = 'fruitjuice') AS juice
  7. ON student.schoolID = juice.schoolID
  8. group by name, fruitjuicename, studentID;
  9.  
Edit - the query's trying to show: the names of all students, and when they have chosen a fruit juice at lunch, show the name of hte fruit juice, too.
May 15 '07 #3
*snip code above*
(too late to edit)
That should actually not have an 'as' after the subquery. I keep forgetting, but for some reason, this version of sql doens't like 'as' when aliasing.

(also, if this is an error because of stupidity on my part, feel free to show me. We haven't actually be *taught* anything more than very basic sql queries for this; we're just expected to magically pick them up by ourselves. So I don't really understand what I'm doing...)
May 15 '07 #4
frozenmist
179 Expert 100+
Okay - II'll have to use made-up names, though, because this is a question of an assignment, and my uni takes plagiarism REALLY seriously. (but this is my query with the names all subbed out. )

Expand|Select|Wrap|Line Numbers
  1. select name, fruitjuicename
  2. from student LEFT OUTER JOIN
  3. (select * from school, cafeteria, drinks
  4. where school.cafID = cafetera.cafID AND
  5. cafeteria.drinkID = drink.drinkID AND
  6. drinktype = 'fruitjuice') AS juice
  7. ON student.schoolID = juice.schoolID
  8. group by name, fruitjuicename, studentID;
  9.  
Edit - the query's trying to show: the names of all students, and when they have chosen a fruit juice at lunch, show the name of hte fruit juice, too.
Hi,
As this is an assignment, may be i can guide you rather than give u the direct answer.
In the sub query aliased JUICE, how many columns of same name may come because you have given select * ?
Just try it out on two simple tables with one column (same column name).
If you give
Select * from table1 T1,table2 T2 where T1.col1=T2.col1
Then you will obviously get two columns named col1.

In the juice subquery instead of giving select * . Try selecting those columns you want
eg: select student.cafeid,drink.drinkid....

Try it out and lemme know.
Cheers
May 16 '07 #5
frozenmist
179 Expert 100+
Hi,
One more thing
In
ON student.schoolID = juice.schoolID
group by name, fruitjuicename, studentID;

Check if there is a column with the name SchoolID in juice.

What I want you to do is run subquery alone and correct it first.


Try it out and lemme know.
Cheers
May 16 '07 #6
Okay, I have the subquery working fine (yes, SchoolID is in juice, because school is in juice).....

(*tries various suggestions*)

I think you're right on the multiple columns - I swapped around the two selects, so that the outer query had "select *" and the inner had the specific stuff (thereby removing multiple columns), and now it works!

Thanks for your help!
May 17 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Ian Boyd | last post: by
5 posts views Thread by Sascha.Moellering | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.