Left Outer Join with subqueries?

[Avaenuha]

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:
[CODE=oracle]
select A.name, B.name
from A LEFT OUTER JOIN (
select * from B, C, D where b.foo= c.foo AND c.baa= d.baa
AND B.specifictype = 'My Type') ON a.poe= b.poe
group by A.name, B.name;
[/CODE]

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

[frozenmist]

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

[Avaenuha]

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. )

[CODE=oracle]
select name, fruitjuicename
from student LEFT OUTER JOIN
(select * from school, cafeteria, drinks
where school.cafID = cafetera.cafID AND
cafeteria.drink ID = drink.drinkID AND
drinktype = 'fruitjuice') AS juice
ON student.schoolI D = juice.schoolID
group by name, fruitjuicename, studentID;
[/CODE]

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.

[Avaenuha]

*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...)

[frozenmist]

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. )

select name, fruitjuicename
from student LEFT OUTER JOIN
(select * from school, cafeteria, drinks
where school.cafID = cafetera.cafID AND
cafeteria.drinkID = drink.drinkID AND
drinktype = 'fruitjuice') AS juice
ON student.schoolID = juice.schoolID
group by name, fruitjuicename, studentID;

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

[frozenmist]

Hi,
One more thing
In
ON student.schoolI D = 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

[Avaenuha]

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!