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: -
-
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;
-
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
6 51469
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
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.
*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...)
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
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
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!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Dam |
last post by:
Using SqlServer :
Query 1 :
SELECT def.lID as IdDefinition,
TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour
FROM serveur.Data_tblDEFINITIONTABLEDECODES def,...
|
by: Steve |
last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three
"Left Outer Joins" in order to return every transaction for a specific
set of criteria.
Using three "Left Outer Joins"...
|
by: Ian Boyd |
last post by:
i know nothing about DB2, but i'm sure this must be possible.
i'm trying to get a client to create a view (which it turns out is called a
"Logical" in DB2). The query needs a LEFT OUTER JOIN, but...
|
by: mike |
last post by:
I'm using postgresl 7.3.2 and have a query that executes very slowly.
There are 2 tables: Item and LogEvent. ItemID (an int4) is the primary key
of Item, and is also a field in LogEvent. Some...
|
by: Doug |
last post by:
Hi,
I'm more familiar with MSSQL than Access syntax and have run into a problem
with correctly putting ( )'s around the joins in a 3 table query. I want to
INNER JOIN lenders and accounts and...
|
by: media.opslag |
last post by:
Hi,
How can i get this to work in access / jet sql ??? Someone??
SELECT
tbl1.,
tbl2.
FROM tbl1
left outer join tbl2 on
|
by: Sascha.Moellering |
last post by:
Hi,
I receive the error code SQL0338N if I try to compile this statement
(part of the statement):
.... left outer join lateral
(SELECT * FROM LIZSYSABA.VWZL0359TBS WHERE tbs_name = CASE WHEN...
|
by: nico3334 |
last post by:
I have a query that currently pulls data from a main table and a second table using LEFT OUTER JOIN. I know how to do make another LEFT OUTER JOIN with the main table, but I want to add another LEFT...
|
by: teneesh |
last post by:
Here I have a code for a view that has been created by a developer on my team. I am trying to use the very same code to create a view for a different formid/quesid. But I cannot figure out how this...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |