MySQL Self Join with a specific condition

My Question is, I have a table which is relatively big (~2 millions rows). Table is like ; id Action user_id Date ----------------------------------- 1 FOP userx date1 2 POP userx date2 3 FOP userx NULL 4 FOP usery date4 5 POP usery date5 6 FOP userz

Oracle Join with NULL

I'm trying to get the latest revision for each submission number and I found a way to do it by using LEFT JOIN - I lost the link to the SO question. But now my problem is that when the submission number is NULL it seems to return more than what I exp

SQL data duplication request

Greetings of the day!!!! I have a table having multiple columns of data with different status. Assume I have 500 rows of data with Status 'Valid' And I have 150 rows of data with 'chkDuplicate'. Now I have to write query to Update these 150 records s

Auto-comparison of the transaction table to find values

I have a mysql table with the following fields Transaction_id, toy_id, toy_name, Quantity_sold, Date_sold. The question I am trying to answer is: What toys were sold today that were not sold yesterday. I understand this is very vague, so please feel

MySql - Auto-join - Full table scan (can not scan index)

I have the following self-join query: SELECT FROM mytbl AS A LEFT JOIN mytbl AS B ON (A.lft BETWEEN B.lft AND B.rgt) The query is quite slow, and after looking at the execution plan the cause appears to be a full table scan in the JOIN. The tabl

multiple auto-joins or unions in Access

I have this dummy temptable that looks like this ID Sum Indicator Month Year 1 10 Ind1 3 2016 1 20 Ind2 3 2016 2 15 Ind1 3 2016 2 19 Ind3 3 2016 . . . . . . . . . . 50 5 Ind1 3 2016 50 5 Ind2 3 2016 50 5 Ind3 3 2016 What I want to get as result is th

Using Self Join

I have two tables (see below) Employees and Departments. Under the employees table, I have columns: SQL> describe employees Name Null? Type ----------------------------------------- -------- ---------------------------- EMPLOYEEID NOT NULL NUMBER(9)

Retrieve the value of the column according to another column

From this table of football players, how would you select the players' names alongside their captains' names? PlayerID | PlayerName | TeamCaptainID ===================================== 1 | Jay | 5 2 | John | 3 3 | James | 3 4 | Jack | 5 5 | Jeremy |

Finding names and similar addresses in the attached table

I've been stuck on this problem for a while and I'm hoping I can get some help here! In SQL Server 2012 I have two tables, Name (containing Name and ID) and Address (containing ID and AddressID), from which I need to find duplicate records based on s

How to correctly SELF JOIN tables in Oracle 11g?

I want to refer the employees to their manager. I got two examples here. Both of them give me a solution that I don't want. First self join query: select m1.naam as medewerker, m2.naam as manager from medewerkers m1, medewerkers m2 where m1.medewerke


I am trying to delete duplicate records with older dates. I just uploaded some dups with a later date and only want to keep those. The following is my code DELETE FROM Config WHERE ( SELECT * FROM Config one INNER JOIN Config two ON =

How can I improve this self-membership to 4 accounts?

Suppose I have the following sample data set: emplid | Citizenship | 100001 | USA | 100001 | CAN | 100001 | CHN | 100002 | USA | 100002 | CHN | 100003 | USA | And I want to arrange it to show the citizenships for each employee in one row. We can assu

#TAble is ambiguous

I am getting an error that states #Plans is ambiguous. This happens when I am joining a table to itself on a self join and am not sure why. Here is the code that leads to the error: Alter Table #Plans Add SecondPlanDate date Update #Plans Set SecondP

Internal SQL join without matching (same table)

I've been trying to write a SQL query to return all rows in a table without a matching value. I have company, job, subjob, costcode, and costtype (among other fields). I need to return all rows which have a 'J' costtype, but no 'L' costtype. This is

SELF JOIN Single Resultset Issue - MySQL

I have a table as below ID | CID 1 | 3 2 | 0 3 | 4 4 | 0 5 | 0 6 | 3 Below is the SQL query I use which is SELF JOIN. SELECT t1.ID FROM `tbl_a` AS t1 JOIN `tbl_a` AS t2 ON t1.ID = t2.CID Which gives me O/P as below. ID | CID 3 | 4 4 | 0 But what I wa

firebird - join a table

I have a table STARTSTOP ACTION DATA ID_PPSTARTSTOPPOZ 0 2013-03-18 08:38:00 10451 1 2013-03-18 09:00:00 10453 0 2013-03-18 09:50:00 10466 1 2013-03-18 10:38:00 10467 0 2013-03-19 11:54:00 10499 1 2013-03-19 12:32:00 10505 Action 0 -> START ACTION Ac

How to set the auto join query in Oracle 11g

Oracle & SQL new to me,and I'm still in learning phase. Could you please help me to tune below oracle query?? This table contains ~95 lac records and it takes 1 hour to retrieve data using this query. Your suggestions/comments/help will be appreciate

Slow self-join delete the query

Does it get any simpler than this query? delete a.* from matches a inner join matches b ON (a.uid = b.matcheduid) Yes, apparently it does... because the performance on the above query is really bad when the matches table is very large. matches is abo

Hibernate Self Join

Background I have a table with columns id, imageId, propertyId. There could be many images associated with a single property and there could be many properties associated with a single image. imageId - many-to-many - propertyId I have a list of prope