Select Query From Two Tables?

Jun 7, 2011

I have two tables a and b column names are id and date. Data type of date in a and b are different

table (a)

date id
10-DEC-01 2:08:39 PM 1
10-DEC-01 2:08:39 PM 2
10-JAN-02 10:10:22 PM 3
10-JAN-02 10:10:22 PM 4
10-JAN-02 10:10:22 PM 5

table (b)
date id
10-DEC-01 1
10-DEC-01 2

I need table b like this one

table (b)

date id
10-DEC-01 1
10-DEC-01 2
10-JAN-02 3
10-JAN-02 4
10-JAN-02 5

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: Select * From One Table But Not All Tables In A Query

Oct 30, 2013

How does one select * from one table without selecting * from other tables that are included in a query? For example, if in the query below I want to view all fields in some_table, but not the fields from other_table, how do it?

select *
from some_table st,
other_table ot
where st.id = ot.id

View 15 Replies View Related

Storing Select Query Result Into Array And Using It In Another Query?

Aug 7, 2009

I am looking to simplify the below query,

DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)

Since both the inner queries are same,I want to extract out to a local variable and then use it.

Say,

Array var = SELECT ID FROM B WHERE BID=0;

And then ,

DELETE FROM A WHERE A1 IN (var) OR A2 IN (var)

How to do this using SQLPLUS?

View 8 Replies View Related

3 Tables In SELECT

Jul 22, 2010

When I use the below code in my perl script (it is oracle database):

$query = "select a.sub_id, b.name from subscribertable a, invitationBin b where a.subscriberid=b.subscriberid";
$sth = $dbh->prepare($sub_query) or die "SELECT-Query failed";

everything is ok... and when I try to add a third table...

$query = "select a.sub_id, b.name, c.phone from subscribertable a, invitationBin b, personalDet c where a.subscriberid=b.subscriberid and b.subIdx=c.subIdx";
$sth = $dbh->prepare($sub_query) or die "SELECT-Query failed";

this fails... it seems like it doesnt let me add 3 tables in the SELECT query through perl script. The strange is that when I test this query with Oracle SQL Developer, it works fine...!

View 1 Replies View Related

Getting Top-N Query To Work As Sub-select In Larger Query?

Mar 10, 2012

Is there a technique to getting a Top-N query to work as a sub-select in a larger query -or- is there another way to generate Top-N like results that works as a sub-select?

Background:

We have a large query that is being used to build an export from a legacy HR system to a new one. Amount the data needed in the export is the employees primary phone number.

The legacy HR system allows multiple phone numbers to be stored in a simple table structure:

SELECT emp_id, phone_type, phone_number
FROM employee_phones

emp_idphone_typephone_number
------- --------------- -------------------
46021CELL2222222222
46021HOME1111111111
46021WORK3333333333

The new HR system does allow for multiple phone numbers, however they need a primary phone number identified and stored with the employee master information. (Subsequent phone numbers get stored in alternate table.)

From a business perspective, we have decided that if they have a HOME phone in the legacy system that should be the primary in the new system, if no HOME phone, then WORK, if no WORK then CELL.

That can be represented as:

SELECT *
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')

emp_idphone_typephone_number
------- --------------- -------------------
46021HOME1111111111
46021WORK2222222222
46021CELL3333333333

Or similarly with Top N concept:

SELECT *
FROM (SELECT *
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1

emp_idphone_typephone_number
------- --------------- -------------------
46021HOME1111111111

Or really what I want in my export:

SELECT phone_number
FROM (SELECT phone_number
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1

phone_number
-------------------
1111111111

However, when the Top-N query is added as a sub-select in a larger query using the employee id from the larger query (WHERE emp_id = export.emp_id), it fails saying that �export.emp_id� is not a valid id.

(SELECT phone_number
FROM (SELECT phone_number
FROM employee_people_phones
WHERE emp_id = export.emp_id
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1)

1.Any way around this? Is it possible to put a Top-N (with a WHERE clause using data from the main query) in a sub-select?

2.Any alternatives (other than Top-N) to delivering a ROWNUM=1 result with a �custom� ORDER BY statement?

Other Notes: Yes, we know we could do two queries in the data conversion first deliver the bulk data to the target table, and then update with the phone numbers. However, for multiple reasons, that is less than desirable.

View 3 Replies View Related

How To Select Rows From Two Tables

Aug 30, 2007

Consider the following tables

MANAGERID
101
102
103
104

MANAGERID EMPID
101 ----------------24
101-----------------25
101 ----------------26
104 --------------- 27

write sql query to get the following output without using minus,union,intersect.

MANAGERID EMPID
101 ---------------------------------- 24
101------------------------------------25
101 -----------------------------------26
102 -----------------------------------N/A
103 -----------------------------------N/A
104 -----------------------------------27

View 3 Replies View Related

SQL & PL/SQL :: Select Count Using 2 Tables?

Mar 28, 2011

I'm having trouble with some SQL code regarding count and an outer join.

Here is my code.

SELECT o.salespersonid, Count(*) from
salesperson s, Ord o
Where s.salespersonid(+) = o.salespersonid
Group By o.salespersonid;

Where salesperson is a salesperson table and ord is a table containing orders.

The orders table contains a FK to salespersonid in the salesperson table.

I want it to return all salespersons along with the amount of orders they are on. It works but does not show the ones that do not appear on any orders hence the outer join.

View 13 Replies View Related

SQL & PL/SQL :: Select Data From All Tables At Once

Sep 12, 2011

Is there any way i can select * from all the tables owned by particular schema at once.

View 22 Replies View Related

SQL & PL/SQL :: Select From Multiple Tables?

Mar 29, 2011

I have two tables containing dates:

A
From____________To________
01.01.2009 || 01.01.2010
01.01.2013 || 01.10.2014

B
From____________To________
01.01.2007 || 01.01.2008
01.01.2011 || 01.10.2012
01.01.2009 || 01.01.2010
01.01.2015 || 01.01.2016

I need to get list of all entries from Table A and entries from B where dates are before min begin date from A or somewhere between any entry from A.

In this Example result:
From____________To________
01.01.2007 || 01.01.2008
01.01.2009 || 01.01.2010
01.01.2011 || 01.10.2012
01.01.2013 || 01.10.2014

View 1 Replies View Related

Coding Select In Multiple Tables

Aug 25, 2011

I am working on the following SQL select and I am having a mental block on how to get it fixed. I have two tables that I need to match on the codes in each table. If there is a just one record in Table1 with the same code as one record in table2 and both the date and name match then dont output those two records. Output all records if there are more than 1 record with the same code in each table. Below is some example data that is representive of a sample in the two tables and how the output should look based on that data:

Table1
code date name
aaaa 1/1/2003 billy bob
bbbb 2/2/2004 louis lewis
cccc 3/3/2005 joe crab
dddd 4/4/2006 mary little
eeee 5/5/2007 joe black

Table2
code date name
aaaa 2/2/2004 larry cole
aaaa 3/3/2005 nat king
bbbb 2/2/2004 louis lewis
cccc 3/3/2005 joe crab
cccc 6/6/2008 dennis jackson
dddd 7/7/2009 missy muffet
dddd 5/5/2007 joe black
eeee 8/8/2010 elton rocket

desired output results from select

aaaa 1/1/2003 billy bob aaaa 2/2/2004 larry cole
aaaa 1/1/2003 billy bob aaaa 3/3/2005 nat king
cccc 3/3/2005 joe crab cccc 3/3/2005 joe crab
cccc 3/3/2005 joe crab cccc 6/6/2008 dennis jackson
dddd 4/4/2006 mary little dddd 7/7/2009 missy muffet
dddd 4/4/2006 mary little dddd 7/7/2009 missy muffet
eeee 5/5/2007 joe black eeee 8/8/2010 elton rocket

Here is the select that I have so far:

select table1.rowid, table1_code, table1_date, table1_name,
table2.rowid, table2_code, table2_date, table2_name from table1, table2
where table1_code= table2_code
order by table1_code;

The above select gives me all records just fine, but does not eliminate single records that match. I tried using the Count(table1_code) > 1 and table2 code but I get a message about inproper grouping.

View 1 Replies View Related

Select Fields From Across Multiple Tables?

Mar 25, 2011

I have had a google around and can't seem to find an answer as to how do do the following Select statement. i am wanting to Select the following fields from across multiple tables.

(field.table)
CustName.CUST
SalesNo.SALE
SalesDate.SALE
ItemDes.ITEM
Qty.SALEITEM
OrderComplete.SALEITEM

with 2 types of WHERE criteria:
WHERE SalesDate is between 'dateX' AND 'dateY'
and also WHERE OrderComplete = 'Y'

i understand this will require some sort of join in the statement so the keys for the different tables are as follows:

CUST
CustNo - PK

SALE
SalesNo - PK
CustNo - fk

ITEM
ItemNo - PK

SALEITEM
SalesNo -fk
ItemNo - fk (compound PK)

i have had a play around with using some joins & embedded statements

View 4 Replies View Related

SQL & PL/SQL :: Give Select Grant For Few Tables?

Jul 8, 2010

I created a user and granted connect,resource priviliges. I gave access to this user for only 5 tables. when i check it later, other tables are also given access. How can i avoid this and give access to selected tables.

View 9 Replies View Related

SQL & PL/SQL :: Select Records Where Date Is Max From Tables

Oct 6, 2011

I've got 3 tables and I need to select records where date is max from these tables.

select * from the_table_1
----------------------------------
contract_key date_1 saldo
1234 30.9.2011 12:06:50 14,6638

select * from the_table_2
----------------------------------
contract_key date_1 saldo
1234 26.9.2011 11:04:02 5,6638

select * from the_table_2
----------------------------------
contract_key date_1 saldo
1234 29.9.2011 17:39:43 2,5438

how to do that ?

View 11 Replies View Related

Application Express :: Select From Several Tables

Nov 26, 2012

i have 3 tables in my database..

table1 with one column

table1
table1_id
11
12
13
14

table2 with one column

table2
table2_id
21
22
23
24

table 3 with 2 primary foreign keys from table1 and table2

table3
table1_id table2_id
11 21
11 22
12 23
12 24
13 21
14 22

i want a select statement that selects all from table2 where the specified table1_id is not in the list of the table1_id in table3

for example if i want the list for id 11 it should return (23, 24)

i came up with the following but it didn't work

select table2_id
from tabel3 c, table2 b
where table1_id <> 11 and c.table2_id = b.table2_id

i know my explanation is not that clear..

View 1 Replies View Related

How To Grant Select On All Tables Of User1 To Another Schema

Jan 18, 2012

User1 is having 10000 tables in his schema...How can i grant "select" on a all tables of a user1 to another schema(user2) so that in future when user1 will create tables , the user2 will have "select" access on those tables automatically.

I dont want user2 to have "select any table" privillege.

User2 should not have "drop" privillege on his own tables.

View 1 Replies View Related

Long Select Operate On 5 Tables - Optimization?

Sep 4, 2013

I have long select which operate on 5 tables and has a lot of conditions in where clause (many combinations of values of just a few columns). Does reducing of those conditions could improve performance or just has a small impact?

I think if I have a lot of conditions on the same column, it don't take a lot of time to check them because values are in memory.

View 3 Replies View Related

Left Joining Select Of Multiple Tables

Nov 22, 2010

i am trying to left join a selection of two or more tables. what i have found, and solved part of my problem, is that oracle left joins only the last table in the select statement ...

i.e : select * from A, B left join C on C.id = A.id wouldn't work because left join applies to B and not A.

but as my queries grow i need to make something as follows :

select * from A, B
left join C on (C.ID_A = A.ID and C.ID_B = B.ID)
[... evantually more left joins as the preceding one may go here]

this query works for DB2 but Oracle claims that "A"."ID" is an invalid identifier, while the B.ID is recognized since it's the last table stated before the "LEFT JOIN" keyword.

View 7 Replies View Related

Select From Two Tables - Remove Repeated Rows

May 31, 2011

I have two tables one source table and one destination . Column names and data types of both table are same.

source table (source)

ID NAME
1 aa
2 bb
3 cc
. ...
. ..
. ..

destination Table(dest)

ID NAME
1 aa
2 bb

I need destination table like

destination table
ID NAME
1 aa
2 bb
3 cc
. .....
. ....

I want to remove repeated rows

View 2 Replies View Related

Making A Select Statement To Join A Few Tables Together?

May 2, 2009

I am trying to make a select statement to join a few tables together. What i would like to know is if i can do this by saying the following.

I want to select indviduals that have a skill, but i want to say that if they have any of these skills to show the name so for example

SELECT S.NAME
FROM EMPLOYEE S ,PROJECT_TEAM T, SKILL_LIST L
WHERE T.PROJECT = 'Tesco' AND
L.SKILLNO = 'skill1' or 'skill2' or 'skill3';

View 6 Replies View Related

SQL & PL/SQL :: Select Columns From Different Tables Dynamically In A Function

Jan 25, 2013

im trying to select columns from different tables dynamically in a function . The parameter for the function will be table name and column id's, In this number of columns may vary . Is it possible to have dynamic %rowtype to store the cursor value in it.

View 2 Replies View Related

SQL & PL/SQL :: Select Data From 2 Tables And Insert Into Another Table

Apr 25, 2013

I want to select data from different tables and insert this into one table based on some conditions:

SELECT *
FROM welltest_msr
WHERE well_s = 3419740
AND check_ind = 1

[Code]....

So I tried doing this with selecting the data and looping through it to do the insert.

DECLARE
--
-- WELLS
--
CURSOR c_well

[code].....

View 6 Replies View Related

Get All Tables That A User Can Select / Insert / Update Or Delete

Dec 17, 2010

How to get all the name of tables that a user can select, insert, update or delete?

View 2 Replies View Related

SQL & PL/SQL :: Cursor Select For Update / Multiple Columns Of Different Tables

Apr 8, 2010

i have two tables test1 and test2. i want to update the column(DEPT_DSCR) of both the tables TEST1 and TEST2 using select for update and current of...using cursor.

I have a code written as follows :

DECLARE
v_mydept1 TEST1.DEPT_CD%TYPE;
v_mydept2 TEST2.DEPT_CD%TYPE;
CURSOR C1 IS SELECT TEST1.DEPT_CD,TEST2.DEPT_CD FROM TEST1,TEST2 WHERE TEST1.DEPT_CD = TEST2.DEPT_CD AND TEST1.DEPT_CD = 'AA' FOR UPDATE OF TEST1.DEPT_DSCR,TEST2.DEPT_DSCR;
[code].......

The above code when run says that it runs successfully. But it does not updates the desired columns[DEPT_DSCR].

It only works when we want to update single or multiple columns of same table...i.e. by providing these columns after "FOR UPDATE OF"
I am not sure what is the exact problem when we want to update multiple columns of different tables.

View 5 Replies View Related

SQL & PL/SQL :: Select Columns Of 3 Tables In Such A Way That Period Column Should Be In Group By Function

Aug 16, 2011

i want to select columns of 3 tables in such a way that period column should be in the group by function.

create view allocated_budgets_detail as
select ba.ba_fin_year, ba.ba_start_date, ba.ba_end_date, ba.ba_rev_no,
bh.bh_budget_code,
bd.bd_period,
bb.bb_entered_amount
from budget_header bh, budget_allocation ba, budget_distribution bd, budget_balance bb
where bh.bh_budget_id = ba.ba_budget_id
and ba.ba_line_id = bd.bd_budget_line_id
and ba.ba_line_id = bb.bb_budget_line_id
group by bd.bd_period

View 13 Replies View Related

Different Plans On Select Query

Dec 6, 2012

We're using Oracle 10g for development purposes. I have 2 same schema with approximately same data. I'm running same query on 2 schemas and I see that the first schema runs the query around 20sec and the 2.schema less than 1 sec. I thought first that there may be missing constraints or indexes but all are the same.

I checked the plan for the 2 schemas and I see that the plan is different.

Here is the query:
SELECT ccc.ComponentId AS "ComponentId", ccp.Code AS "ParentCode", ccc.Code AS "ChildCode" FROM CatalogueComponent ccp INNER JOIN CatalogueComponent ccc ON ccp.ComponentId = ccc.ParentComponentId WHERE ccc.ComponentId IN (20934777, 1594747)

I'm sending also the 2 output of PLAN results from 2 different schemas.

what should I do to fix the problem with the NAFBCA schema.

Attached File(s)

NAFBCA.JPG ( 57.49K )
Number of downloads: 6

NAFBCA2.JPG ( 60.36K )
Number of downloads: 4

View 1 Replies View Related

How To Add Alias In Select * Query

Feb 23, 2009

I want to select all the field from the table and want to put alias for only field i m just wondering is there any way to write a query something like

select *, emp as employee
from empMaster

here i want to display all the column but only with emp column i want to put alias.

View 1 Replies View Related

SQL & PL/SQL :: Oracle Select Query?

Jul 22, 2010

SELECT DISTINCT PATIENT_ID , (
SELECT ROWNUM,APPOINTMENT_ID FROM
AppointmentDetailsHistory WHERE APPOINTMENT_STATUS_ID = 2
AND VISIT_TYPE_ID NOT IN (7) AND PATIENT_ID = ADH.PATIENT_ID

[code]...

MY SELECT query failing actually i am trying to convert sql server select into oracle.My sql server query is like this.

SELECT DISTINCT PATIENT_ID , (
SELECT TOP 1 APPOINTMENT_ID FROM
EMRAppointmentDetailsHistory WHERE APPOINTMENT_STATUS_ID = 2
AND VISIT_TYPE_ID NOT IN (7) AND PATIENT_ID = ADH.PATIENT_ID
ORDER BY PATIENT_ID, LAST_UPDATED_DATE ASC) AS FIRST_APPOINTMENT FROM
EMRAppointmentDetailsHistory ADH WHERE ADH.APPOINTMENT_STATUS_ID = 2
AND ADH.VISIT_TYPE_ID NOT IN (7)

View 14 Replies View Related

SQL & PL/SQL :: Different Plans On Select Query?

Dec 6, 2012

We're using Oracle 10g for development purposes.I have 2 same schemas with approximately same data.I'm running same query on 2 schemas and I see that the first schema runs the query around 20sec and the 2.schema less than 1 sec. I thoughtfirst that there may be missing constraints or indexes but all are the same.I checked the plan for the 2 schemas and I see that the plan is different.

Here is the query:
SELECT ccc.ComponentId AS "ComponentId", ccp.Code AS "ParentCode", ccc.Code AS "ChildCode" FROM CatalogueComponent ccp INNER JOIN CatalogueComponent ccc ON ccp.ComponentId = ccc.ParentComponentId WHERE ccc.ComponentId IN (20934777, 1594747)

I'm sending also the 2 output of PLAN results from 2 different schemas.

View 19 Replies View Related

SQL & PL/SQL :: How To Select And Delete At The Same Query

Jun 10, 2010

select SQL_CALC_FOUND_ROWS navl.xydata.Lat,
navl.xydata.Longi,
MIN(navl.xydata.GpsTime) as mn,
MAX(navl.xydata.GpsTime) as mx,
timediff(MAX(navl.xydata.GpsTime) ,MIN(navl.xydata.GpsTime) ) as idle,
[code]......

i want to delete WHERE mn = mx .

View 4 Replies View Related

PL/SQL :: Select Query On Emp Table

Jun 7, 2012

How to find display the o/p like manager name under dependent employess same like parent child relation ship on noraml emp table:

sample o/p:

name job
xx manger
yy sales
yy1 sales
aa manager
rr marketing
rr1 marketing

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved