oracle,join,outer-join , Oracle SQL join the same table

Oracle SQL join the same table


Tag: oracle,join,outer-join

I have a table (MyTable) with this columns: point alarm load_id countAlarms ...

and I use this query:

select point, decode(alarm,0,'new','rec') NewRec, sum (countAlarms) total_alarms, load_id from MyTable
where 1=1 
--and load_id = (select max(load_id) from MyTable ) -0
group by point, decode(alarm,0,'new','rec'), load_id
order by 1, 2

to receive something like this:

point1 new 1200 111113
point1 rec 6000 111113
point2 new 1220 111113
point2 rec 3000 111113
point3 new 3220 111113
point3 rec 1000 111113
point1 new 1300 111112
point1 rec 6300 111112
point3 new 1220 111112
point3 rec 1100 111112
point1 new 1300 111111
point1 rec 6300 111111
point2 new 1120 111111
point2 rec 3100 111111
point3 new 1220 111111
point3 rec 1100 111111

what i need is this:

point   newRec      point   point   point
point1  new         1200    1300    1300
point1  rec         6000    6300    6300
point2  new         1220            1120
point2  rec         3000            3100
point3  new         3220    1220    1220
point3  rec         1000    1100    1100

I have tried with a full outer join but it don't work :(


It looks like you want to pivot your result set, rather than join it to itself. Assuming you're on Oracle 11g or higher you can do this natively:

select * from (
  select point, decode(alarm,0,'new','rec') NewRec, countAlarms, load_id
  from MyTable
pivot (
  sum(countAlarms) as alarms
  for (load_id) in (111113 as a, 111112 as b, 111111 as c)
order by 1, 2;

Which with sample data matching your output above gives:

------ ------ ---------- ---------- ----------
point1 new          1200       1300       1300 
point1 rec          6000       6300       6300 
point2 new          1220                  1120 
point2 rec          3000                  3100 
point3 new          3220       1220       1220 
point3 rec          1000       1100       1100 

SQL Fiddle demo.

You have to know the values you're pivoting on though; it isn't clear if you know the load IDs in advance, but the commented-out load ID filter in your original query suggests you might not. If you always want the three (or any fixed number of) highest load IDs then that can be achieved by modifying the inner query and the pivot criteria, e.g. with an analytic dense_rank() pseudo-column:

select * from (
  select point, decode(alarm,0,'new','rec') NewRec, countAlarms,
    dense_rank() over (partition by null order by load_id desc) as rnk
  from MyTable
pivot (
  sum(countAlarms) as alarms
  for (rnk) in (1 as a, 2 as b, 3 as c)
order by 1, 2;

SQL Fiddle.


Unique movies by UNION the results of two join, MYSQL

I ran the following query to UNION the result of two joins over the below 4 tables: SELECT member_CROWD.worker_Id, member_CROWD.Unreliable, Answer_CROWD.qId, Answer_CROWD.answer FROM member_CROWD JOIN `Answer_CROWD` ON member_CROWD.worker_Id = Answer_CROWD.worker_Id WHERE member_CROWD.Unreliable = '0' AND Answer_CROWD.qId = 'q8' GROUP BY Answer_CROWD.answer UNION SELECT member.mem_id, member.Unreliable, Answer.qId, Answer.answer FROM member JOIN...

left join table, find both null and match value

I have two 2 table t1(years int, numOfppl int), t2(years int, numOfppl int). t1 contains years between 2001 and 2010 , t2 contains years between 2003-2005 and 2007-2010 I want to query a result like that t1.years t1.numOfppl t2.yeras t2.numOfppl 2001 7 null null 2002 6 null null 2003 4...

Notice: Array to string conversion in “path of php file” on line 64

PHP CODE -: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" ""> <html xmlns=""> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Untitled Document</title> </head> <body> <?php $rows =0; $fp = fopen("leave1.csv","r"); if($fp){ while(!feof($fp)){ $content = fgets($fp); if($content) $rows++; } } fclose($fp); //echo $rows; $_SESSION['rows'] = $rows; ?>...

MySQL query JOIN with 2 tables and multiple entries

I'm trying to do a MySQL JOIN query to grab the content from two tables. This is what Table A looks like: Table A ID | ISBN | Type ---------------------- 12 | 0338566 | book 15 | 6656565 | post 16 | 9435644 | book 20 | 8525446 | book...

How to create a SELECT query FROM “TABLE1 AND TABLE2”

I have a PostgreSQL database, with only SELECT permissions. In this DB there are two tables with the same structure (the same columns). I need to write several query in each table and join the results. There is a way for writing a query like this one? SELECT field1, field2,...

Column ambiguously defined error with Oracle Merge statement

I have a problem with this query in Oracle SQL. Errore alla riga del comando:18 Colonna:42 Report errori: Errore SQL: ORA-00918: column ambiguously defined 00918. 00000 - "column ambiguously defined" *Cause: *Action: This is the query, but i don't see the column ambiguously defined: MERGE INTO T_HPSM_CM_UBIS H USING (SELECT...

oracle sql error Case When Then Else

SELECT * FROM FirstTable WHERE RowProcessed = 'N' AND ( CASE WHEN EXISTS(SELECT top 1 FROM SecondTable) THEN 1 ELSE EXISTS( SELECT SecondTable.RowProcessed FROM SecondTable WHERE FirstTable.Key = SecondTable.Key AND SecondTable.RowProcessed = 'Y' ) END ) AND OtherConditions Case When then else in where clause. Not sure about the syntax....

Groovy - timestamp from minutes

I have an array or times/values coming back to be in an array like: [0, 60] Which are times in minutes, 0 = 12:00 a.m, 60 = 1:00 a.m. I am wanting to store these in an oracle database as timestamps. How do I convert minutes into timestamps in groovy?...

Complex SQL with Multiple Joins

I have a database of politicians, political parties and candidacies for a post in the election. The database consists of the following tables: People, Groups, Candidacies and Posts. The Candidacies table is a join relationship between People and Groups. Each record in the Candidacies table represents a candidacy in the...

Get unmatched records without using oracle minus except not in

Actually I have two table and each having column name, I just want the result which are not there in Table2 Table1 ---- Name --- |A| |B| |C| |D| Table2 ------ |Name| ----- |A| |B| Answer |C| |D| I am able to do it by using minus select name from...

SQL Developer does not connect with SID as defined in tnsnames.ora


mysql query retrieve too slow from 10,000 of data (Query Optimizing)

10,000 of data in memberships, members and payments table. Retreiving the query is too slow, while searching a particular payment status in each members latest payment. SELECT AS member_id, m.full_name, m.unit, m.street, m.block,, m.postal_code, cat . * , AS cat_id, mem.membership_num, AS membership_id FROM memberships mem...

How to pull the date in proper format from timestamp

I am working in Oracle SQL Developer and have the following code SELECT Trunc(assigned_date, 'IW') AS bonus_week FROM (SELECT Trunc(blf.assigned_date) AS assigned_date FROM olap.bonus_lifecycle_fact blf) I get output in this form 07-12-03 08-01-28 08-01-28 08-01-28 08-01-07 Which is DD-MM-YY That's fine, except when I export to Excel I get some...

ms access query very slow

I have this ms access query: SELECT, suchbegriff2, menge FROM (SELECT artnr & '/' & [lfdnr-kal] AS sb, left(suchbegriff,7) & val(right(suchbegriff,4)) AS suchbegriff2 FROM kvks WHERE suchbegriff like '*/*') AS t1 INNER JOIN (SELECT artnr & '/' & [lfdnr-kal] AS sb, [artnr-hz] & '/' & val(lfdnr) AS hz, menge...

How to use subquery result as the column name of another query

I want to use the result from subquery as the column name of another query since the data changes column all the time and the subquery will decide which column the current forcast data stored. My example: select item, item_type ... forcast_0 * 0.9 as finalforcast forcast_0 * 0.8 as...

Oracle SQL - Returning the count from a delimited field

I'm fairly inexperienced with SQL so hopefully this question is not too silly. Here is the scenario: I have a VARCHAR2 column that stores a series of values delimited by product. Depending on on the account, they can have one or multiple products. I'm trying to write a query that...

join two different list by id into one list

I've got two different list of two different objects. Then i got one list of a viewmodel that contains properties from both the objects and i want them to be joined into that list. //Product public string id { get; set; } public string unitMeasurement { get; set; } public...

SQL Server - Substitute for OR when JOINing on non-indexed field

Background: One of our clients has a system where they have recordings of phone conversations between their agents and their customers, who they create various contracts with. The recordings are stored on a server with their locations saved in the Recordings table in the database. The agents can then "attach"...

JOIN on keys that don't have the same value

I am trying to do an INNER JOIN on two tables that have similar values, but not quite the same. One table has a fully qualified host name for its primary key, and the other the hosts short name, as well as the subdomain. It it safe to assume that...

Does Maria DB support ANSI-89 join syntax

Does Maria DB support ansi-89 join syntax? SELECT sites.site_id, sites.site_name, pages.page_id, pages.page_title FROM sites, pages WHERE sites.site_id = pages.site_id; or only ansi-92 syntax? SELECT sites.site_id, sites.site_name, pages.page_id, pages.page_title FROM sites INNER JOIN pages ON sites.site_id = pages.site_id; ...

Why does the date doesn't match with what I have inserted into the database?

I tried to display the sqlcommand after I saved into the database. Everything seemed to work pretty fine, but when I opened my table from TOAD, the dates are wrong. Here is my sql command: INSERT INTO USERTASK (USERTASKKEY, USERID, TASKKEY, TASKDATE, CREATEDATE, CREATEUSERID) VALUES (USERTASKSEQUENCE.NEXTVAL, 'admin2', '1', TO_DATE('05-06-2015','yyyy/mm/dd HH24:MI:SS'),...

sql script to find index's tablespace_name only

Trying to find the specific tablespace names that were created for indexes only. I don't want to see the tablespaces names that are already used for tables. Something like below, but i couldn't have it working due to syntax error. Can someone fix it? select tablespace_name from dba_indexes where tablespace_name...

Why does .Where() with a Func parameter executes the query?

Here's how my DataAccessLayer works : public Foo GetFooBy(Func<Foo, bool> filter) { var query = from item in this.DataService.FooSet select item; var where = query.Where(filter); var first = where.First(); return first; } I assumed the query would be run when First() is called but it's actually executed by the Where()....

Fill with zero to complete a defined number in sql [closed]

I need to complete cards numbers in sql. I have the prefix =11111 and the number of the card which is variable, therefore it could be '25' or '2130' but at the end I must have 14 numbers. So I need to fill spaces with zeros. I've read about 'LPAD'...

Why I can't compare dates?

I have this simple query: select, h.time from main_data sm INNER JOIN TA t on = AND t.first=1 LEFT OUTER JOIN History h on WHERE trunc(TO_DATE(h.time, 'DD-MM-YYYY')) BETWEEN trunc(TO_DATE('07.05.2015', 'DD.MM.YYYY')) AND trunc(TO_DATE('07.06.2015', 'DD.MM.YYYY')); h.time looks like 07-MAY-15 The above query returns no results, even if...

SQL Oracle | How would I select a substring where it begins with a certain letter and ends with a certain symbol?

If I had this: NAME EYES==ID==HAIR Jon Brown==F9182==Red May Blue==F10100==Brown Bill Hazel/Green==F123==Brown ...and I wanted to create a new ID column with the ID alone, and I know that everyone's ID starts with an 'F' and will end at the '=' how would I select a substring from the compact...

ONLY display certain rows from an inner joined table using a certain colum as a parameter from one of the inner joined tables

I have two tables, one with various details such as Username, email id, user code and another table with columns which are: UserCode, supervisor email id, active_flag I required an output of a table which only displays the active users i.e. the users with active_flag (Y or N) column as...

Identifier is too long

Please help me to find why am getting identifier is too long error , even after having enough space for role in record. Exception : Error report - ORA-06550: line 14, column 24: PLS-00114: identifier 'ou=internal,ou=users,dc=chinas' too long ORA-06550: line 18, column 24: PLS-00114: identifier 'ou=internal,ou=users,dc=chinas' too long 06550. 00000...

update a table from another table using oracle db

I have to update one table from another one: I can do the update with the MySQL sgbd: update product pr , provider p set pr.provider_name = where p.provider_id = pr.provider_id ; but when I try to do it with oracle : I tried this query for oracle UPDATE...

Find any character occur more than 4 times

I want to find any character occurs between 4 and 10 times, I used REGEXP_LIKE but it's valid just for one character 'a' , I want to find for all alphabet: SELECT regex_test_name FROM regex_test WHERE REGEXP_LIKE(regex_test_name, 'a{4,10}') ...

PLSQL - Error in associative array

Im trying to delete a set of tables and afterwards I want to recreate them using as select from. For couriousity I wanted to do this with an associative array. Unfortunately something is messed up, several errors appear and I can't find the reasons. This is the code: DECLARE TYPE...

Result from pipelined function, always will sorted as “written”, or not?

Needed get renumbered result set, for example: CREATE OR REPLACE TYPE nums_list IS TABLE OF NUMBER; CREATE OR REPLACE FUNCTION generate_series(from_n INTEGER, to_n INTEGER, cycle_max INTEGER) RETURN nums_list PIPELINED AS cycle_iteration INTEGER := from_n; BEGIN FOR i IN from_n..to_n LOOP PIPE ROW( cycle_iteration ); cycle_iteration := cycle_iteration + 1; IF...

How to catch Oracle exception “ORA-06535: statement string in OPEN is NULL or 0 length”?

I would like to catch specific exception for exception "ORA-06535: statement string in OPEN is NULL or 0 length" But couldn't figure out the exact "exception name" for it. Please note I don't want to catch it under generic other exceptions block.. EXCEPTION WHEN <exception_name1> THEN executable_statements; WHEN <exception_nameN> THEN...

Entity Framework code-first: querying a view with no primary key

Our customer has given the access to views in which there is no primary key is defined. I know Entity Framework needs a primary key for table to identify. But for views not having primary key is it still possible to query. I try to find but always Entity Framework...

Get only Oracle function return table's columns and their types

Is there an oracle query which I can use to get back the details of an oracle function which returns a table, where I'm looking for the info of that table, say the record name, but mostly the columns in that record and their types. Example function: create or replace...

Extracting XML data from CLOB

How can I extract Food ItemID and Food Item Name and Quantity from the data as mentioned below. This is in clob column in plsql. <ServiceDetails> <FoodItemDetails> <FoodItem FoodItemID="6486" FoodItemName="CARROT" Quantity="2" Comments="" ServingQuantityID="142" ServingQuantityName="SMALL GLASS" FoodItemPrice="50" ItemDishPriceID="5336" CurrencyName="INR" Currency Id="43"/> </FoodItemDetails> <BillOption> <Bill Details Total Price="22222" BillOption="cash"/> </BillOption> <Authoritativeness/>...

Using MyBatis Update with foreach

i am struggling with update statement in MyBatis. I want to put multiple strings in one row: UPDATE MY_FILTERS SET GROUPS = <foreach item="item" collection="selectedGroups" open="" separator="," close=""> #{item} </foreach>, TEMPLATES = <foreach item="item" collection="selectedTemplates" open="" separator="," close=""> #{item} </foreach> where ID = #{id} I've tried the following alternatives: open...

Cant delete in database because of constraints

I am making an project and I am trying to delete a row in my database but that table got constraints. I am using this method to delete it.How can I rewrite it so that all constraints are disabled or something in order that i can properly delete a...

Exclude / ignore weekends in Oracle SQL

I made a query that groups text together and their start and end date, i want to exclude/ignore the weekends. In this example 11-12 and 18-19 april are the weekends create table t ( d date, v varchar2(10)); insert into t values (date '2015-04-10', 'ne'); insert into t values (date...

Trying to access Oracle's Maven repository

I'm attempting to access Oracle's repository. Oracle doesn't make it easy. However, I'm attempting to follow the documentation that Oracle provided. I've: Approved the licensing agreement on my system (in case there's some sort of cookie that needs to be set). Set both M2_HOME and MAVEN_HOME to /usr/share/apache-maven. Created an...

Is there a better way to write this query involving a self select?

I am looking for a "better" way to perform a query in which I want to show a single player who he has played previously and the associated win-loss record for each such opponent. Here are the tables involved stripped down to essentials: create table player (player_id int, username text);...

Can't obtain connection with the DB due to very long schema validation and connection reset afterwards

I have an app where I use Hibernate/Oracle 11g XE/Jboss6.2 and I am having trouble starting up the application on my homolog server at DigitalOcean (Jboss and Oracle xe installed locally). Running everything on my dev machine I have no problem at all and it starts in about 30s, but...

MySQL 4 Table Join

I've been trying to get the proper data for this 4-table join for awhile and I can't seem to pin it down. I'm trying to grab a list of ids from songs based on who I am following in follows, and show the song id if my followers are the...

chunk of data into fixed lengths chunks and then add a space and again add them all as a string

I have got hex values as a85b080040010000. I want it to be as a8 5b 08 00 40 01 00 00. I have done it by using below code. But I have to work with very large data. So I want computed time to be very low. import binascii import...

Oracle 11g Insert Statement into Multiple Tables

I am currently having trouble trying to execute multiple statements at the same time. I keep getting this error when trying to run the following INSERT statements: INSERT INTO report_header ( report_number, company_id, user_id, entry_date) VALUES ( 6797, 15967, 84, TRUNC(SYSDATE)); INSERT INTO report_detail (part_id, condition_id, uom_id, dvc_id, cqh_id, alt_part_id,...

How to join 2 tables with select and count in single query

I need to join 2 tables (Person and PersonLine). The result should contain id and name column from Person table and count of personlineid column from PersonLine Table for each id. But sql query returns count of all personlineid. Can anyone help to form the sql. Person: ID NAME AGE...

'ORA-00942: table or view does not exist' only when running within a Stored procedure

This should be easy pickin's for a PL-SQL person. Before you mark this question a duplicate, please ensure that while the error message may be common that the underlying problem is the same as a previous question. If so, please provide a link to the exact logical duplicate question that...

SQL*Loader Control File Custom Date Format

I need to import from a CSV file in which timestamps are given in the following format 2014-06-14T09:38:29 I tried the following in the control file for SQL*Loader but it doesn't work TIME DATE "YYYY-MM-DDTHH:MI:SS" and TIME DATE "YYYY-MM-DDTHH24:MI:SS" How can I parse this custom date? The error I get...

like and regexp_like

For like we have %. for ex: if we give ad% it ll fetch all the records which starts with ad but i should use regexp_like. for regexp_like what can be used so that it acts as % for like. i cant use ^ad because from UI we ll give...

Trigger to find next available inventory location

I am trying to implement inventory tracking and am running into problems. As this is my first foray into database triggers (& PL/SQL in general) I think I need an adjustment to my thinking/understanding of how to solve this issue. My situation is as follows: Each time a new item...