Please help

pts.
Tags:
Oracle
SQL
Visual Basic
Yet another question, and an example of my code. i have pulled some of the information that i need for the flat file by connecting to 3 tables, then sending the information i have chosen from the table into columns. i need to connect to another 2set of tables to add further information to my first record. how can i do a select statement within a select statement; example below; Thank you for your help, just to make sure i am in the right track could you just please see if i go the right idea. my code so far for the flat file is set as such; set echo off; column version format a4; column description format a5; column relationship format a2; column ssn1 format a11; column ssn2 format a11; column lastname format a26; column firstname format a26; SELECT a.relat_id relationship, a.ssn_id ssn1, a.ssn_id ssn2, b.last_name lastname, c.first_name firstname, from Tbl1 a, Tbl2 b, Tbl3 c where a.empl_id = b.empl_id and b.empl_id = c.empl_id and c.empl_id = a.empl_id. this will produce something like this; wife 123456789 123456789 lastname firstname after first name i need to pull another set of information from another select statement how can i add the new select staement to what i already have above so it can pull the info right after first name.

Answer Wiki

Thanks. We'll let you know when a new response is added.

If you are on Oracle 9i, you can use an inline view:

select a.col1, a.col2, b.col3
from table1 a, (select col3, col4 from table2) b
where a.col1 = b.col4

This example assumes a one-to-one relationship between table1 & table2. If there is a one-to-many relationship between table1 & table2 (table2 has the many), you would need a aggragate function in the inline view (i.e. sum, min, max)

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • BKMerrell
    Your request for help was a little vague on exactly what you need. I will try to generalize in hopes that I cover bases. This may cover things that you already know. To select for a prior selection: Selet a, b, c, d from (select 1 as a, 2 as b, 3 as c, 4 as d from DataB1 Where 1 = "criteria" and 2 = 5) where C "Bogus" I just embed the select inside a select and can play with the columns any way I wish. Notice that I can also do filtering inside each select. I have also used this technique with inner, left, right, etc. joins. HTH Bryan Merrell
    0 pointsBadges:
    report
  • Jwhanon
    There is a few ways to do this. It all depends upon what you are trying to accomplish and what kind of performance constraints you have. If you can guarantee a single value being returned, then: SELECT a.ssn, a.last_name, a.first_name , (SELECT b.ssn FROM Tbl2 b WHERE b.parent_id = a.id ) FROM Tbl1 a Some potentially nasty performance with this, but, it is sometimes the only way to go. Another option is to build a CURSOR in PL/SQL, and loop through that, pulling in the other data via a SELECT or another CURSOR. Or, somtimes an inline view is appropriate. SELECT a.ssn, a.last_name, a.first_name , d.ssn, d.last_name, d.first_name FROM Tbl1 a , (SELECT b.ssn, c.last_name, c.first_name, c.id FROM Tbl2 b, Tbl3 c WHERE b.parent_id = c.id ) d WHERE c.id = a.id
    0 pointsBadges:
    report
  • Jwhanon
    Randy, Just for your edification :-) ... Inline views are considerably older than 9i. I was introduced in 7.3 I think it was, maybe 8.0. Anyways, they work well, eh? Also, I've used inline views many times where I was expecting multiple rows from the inline view. Granted, in Alex's case that's not what he's (she's?) looking for. I agree completely that there has to be a one-to-one when they're used in the SELECT rather than the FROM clause. For example, if you are writing a list of Employees and their Dependents, then you will probably have multiple rows from the inline view, and that is what you'll want.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following