Left join and distinct

SELECT distinct email,lastupdated FROM FantasyTeams A Left JOIN ‘Users` B ON (A.handle = B.handle)

Where lastupdated <‘2021-08-31′ AND lastupdated>’2021-04-31’ AND email is not null ORDER BY lastupdated DESC

I have two tables, Fantasy Teams and Users, which I’m joining using left join, but the emails seem to duplicate. What’s the solution?

Could not write to hash join temporary file : operation not permitted

I am facing this error in postgres:

could not write to hash-join temporary file : operation not permitted

I face this error while trying to update a table named “inventory_history”, for this query :

update inventory_history t1

set asset_status=’REMOVED’,

status_change_date=now()::date

from remove_main_keys t

where t1.main_key=t.main_key

and t1.asset_status<>’REMOVED’;

It seems it is not able to write to some postgres temporary files of hash-joins.

We had restarted the db on because this table had got locked. I had tried "vacuum full","update" and then a partial truncate on this table. It had got locked after vacuum full itself so I stopped it. Tried other 2 operations but stopped those operations halfway as well. I was not able to apply select query on it so I had restarted the db. Then, i created its .bak file and i have tried to restore it again. Now i am able to apply select queries on it, i granted permissions but when i run a pentaho job on it, i get the above error. Is this table still in locked state? Can we unlock this table?

I checked other similar questions on this but all temporary hash join file problems are related to space issues in other stackoverflow problems. I am not able to find any particular solution for my problem.

Please help.

How do you JOIN two tables as a combination of LEFT and RIGHT JOINs?

This is probably a stupid question. In two tables of

CREATE TABLE t1 ( id int(11) unsigned NOT NULL, col1 int(11) unsigned NOT NULL, PRIMARY KEY(id) ) ENGINE=InnoDB;  CREATE TABLE t2 ( id int(11) unsigned NOT NULL, col2 int(11) unsigned NOT NULL, PRIMARY KEY(id) ) ENGINE=InnoDB;  INSERT INTO t1 (id,col1) VALUES (1,2), (3,5), (11,3); INSERT INTO t2 (id,col2) VALUES (1,1), (3,4), (10,2); 

How do you JOIN tables to include missing ids too?

id  col1    col2 1   2       1 3   5       4 10  NULL    2 11  3       NULL 

Left Join with multiple Depth in postgres

I have a schema that looks like this:

                  +------------+ +------------+    + acc_email  | | account    |    +============+ +============+    | id         | | id         |<-+-+ account_id | +------------+  | | email      |                 | +------------+                 |  +------------+                 |  | password   |                 |  |============|                 |  | id         |                 +--+ account_id |                    | password   |                    | iterations |                    | salt       |                    +------------+ 

The users login via email, need to find the account, than find the password. I got this far:

       SELECT *           FROM acc_email     LEFT JOIN account            ON acc_email.account_id = account.id 

How do I join and retrieve the account email, account and latest password in a single query?

Advice on the type of JOIN I should use in this scenario using MySQL Database

I have 3 sections as follows;

  1. TIER I
  2. TIER II
  3. TIER III

Each having tables as below

  1. Objectives
  2. Performance Measures which references Objectives
  3. Performance Actual which references Performance Measure

The Objectives of Tier II references those of Tier I & those of Tier III references those of Tier II.

My requirement I want to get Tier II Performance from Tier III performance, and also get Tier I performance as from Tier II performance.

What I have Tried

SELECT   d_o.ObjectiveName,   ia.Q1 AS 'Q1Actual',   ia.Q2 AS 'Q2Actual',   ia.Q3 AS 'Q3Actual',   ia.Q4 AS 'Q4Actual',   i_o.ObjectiveName AS 'Individual Objective',   fy.FY AS 'FY',   AVG(ia.Q1),   dpm.DeptMeasureId,   dpm.Q1,   ia.* FROM   individual_appraisal ia   LEFT JOIN individual_pm ipm on ia.MeasureId = ipm.MeasureId   LEFT JOIN individual_objectives i_o ON ipm.IndObjectiveId = i_o.IndObjectiveId   LEFT JOIN department_objectives d_o ON i_o.DeptObjectiveId = d_o.DeptObjectiveId   LEFT JOIN department_pm dpm ON dpm.DeptObjectiveId = d_o.DeptObjectiveId   LEFT JOIN tier_one_objectives t1_o ON d_o.TierOneObjectiveId = t1_o.TierOneObjectiveId   LEFT JOIN financial_years fy ON t1_o.FYID = fy.FYID WHERE   i_o.StaffUser = 'staffuserName...' GROUP BY   dpm.DeptMeasureId 

Note I have attached a database diagram for the same to better understand the question. Database Diagram for the above Illustration.

Your kind help is highly appreciated.

Shortcode letting user to “join” the multiple user roles

It is possible to create some sort of shortcode which allows users to "join" the particular user role from frontend? Like if user is subscriber by default to let him join also user role such as "supporter" so he will be subscriber but also supporter?

And the second one the opposite – to let users from frontend "leave" the particular user role if they are members of it.

I’ve tried to google it, but I found only code to let users to switch between roles and I can’t figure out how to make it to let users to be member of multiple roles.

P. S. I am using plugin which allows me to have multiple user roles, so I am not limited by default WP functionality in this.

How do I join rows from the same table with slightly different info?

We have a new sector of our company that is being put into our employee record feed. However this sector is listed as contractors as there are still parts of the sector that need access to systems that require domain access… so…

I have:

TABLE EMP

EmpID, email, INFO1, INFO2, INF03…

and for Tom Smith 5763, tom.smith@co1.com, blah blah, blah blah, blah blah…

and also for Tom Smith ZZ98401, tom.smith@co2.com, null, null, null…

Not everyone on the feed is like this, only about 10%. Most are in case number 2.

What I am looking for is the easiest way to equate the two rows associated with Tom and fill in as much info on Tom as I can because some fields come from one account and other fields come from the other. The only thing that can accurately make the two rows common is the beginning of the email address – this is standardized. So there should be a few thousand people (rows of duplicates) on my feed that I can combine if it can be done right.

I would also like to capture both EmpIDs somehow in the same row and both email addresses.

How to SELECT DISTINCT records with INNER JOIN?

I have two tables Finance Commissions May2021 (FMAY) and Consolidated Client Codes (CCC).

Here is the code for Insert Into: For table FMAY

 USE [FinanceCommissions26May2021] GO  /****** Object:  Table [dbo].[Finance_Commissions_May26_2021$  ]    Script Date: 22/06/2021 11:06:26 AM ******/ SET ANSI_NULLS ON GO  SET QUOTED_IDENTIFIER ON GO  CREATE TABLE [dbo].[Finance_Commissions_May26_2021$  ](     [Lender Reference] [nvarchar](255) NULL,     [F2] [nvarchar](255) NULL,     [F3] [nvarchar](255) NULL,     [Client] [nvarchar](255) NULL,     [F5] [nvarchar](255) NULL,     [Supplier] [nvarchar](255) NULL,     [Product] [nvarchar](255) NULL,     [Settlement / Inception Date] [nvarchar](255) NULL,     [Base Value] [nvarchar](255) NULL,     [Original Loan Amount] [money] NULL,     [Adviser Split (%)*] [money] NULL,     [F12] [nvarchar](255) NULL,     [Supplier_Amount_(incl GST)] [money] NULL,     [Supplier_Amount_(GST)] [money] NULL,     [Adviser Share (ex GST)] [money] NULL,     [F16] [nvarchar](255) NULL,     [F17] [nvarchar](255) NULL,     [Adviser Share (GST)] [money] NULL,     [Adviser Share (incl GST)] [money] NULL,     [LastNameOnly] [nvarchar](255) NULL,     [FirstNameOnly] [nvarchar](255) NULL,     [FirstFourLastName] [nvarchar](255) NULL,     [ClientCode] [nvarchar](255) NULL,     [IndexMatch] [nvarchar](255) NULL,     [F25] [nvarchar](255) NULL,     [F26] [float] NULL ) ON [PRIMARY] GO 

FMAY

Table FMAY contains the following columns:

  • commission amounts
  • a Lender Reference Number
  • client names (LastName, FirstName).

This report comes in monthly and contains monthly commissions for some of the clients (usually contains 350-400 records).

CREATE TABLE [Finance_Commissions_May26_2021$  ] (     CommissionAmount,     LenderReferenceNumber,     ClientLastName,     ClientFirstName ) 

& here is the code for table CCC:

USE [FinanceCommissions26May2021] GO  /****** Object:  Table [dbo].[Consolidated_ClientCodes$  ]    Script Date: 22/06/2021 11:07:27 AM ******/ SET ANSI_NULLS ON GO  SET QUOTED_IDENTIFIER ON GO  CREATE TABLE [dbo].[Consolidated_ClientCodes$  ](     [FirstFourLastName] [nvarchar](255) NULL,     [ClientCode] [nvarchar](255) NULL,     [Client] [nvarchar](255) NULL ) ON [PRIMARY] GO 

CCC

Table CCC contains the following columns:

  • client names
    • (different format though – i.e. FirstName,LastName)
  • a client code (which is a unique identifier (Primary key) assigned to each client.
    • Client code takes the form of SMIT123.
    • e.g. for "John, Smith" it’s SMIT123
    • for "Mark, Smith" it’s SMIT345, and so on.
    • i.e. First Four letters of last name followed by a unique 3 digit code.

This table contains Client names and unique Client codes for ALL clients (more than 7000 records)

CREATE TABLE ['20210617145928-Exception - No C$  '] (     ClientCode PRIMARY KEY, -- '\w\w\w\w\d\d\d'     FirstName,     LastName ) 

The problem

My aim is : To assign this unique Client code to each client within the FMAY table.

Since there was NO common column between tables FMAY and CCC, I created a new column (FirstFourLastName) which parses out FirstFour letters of Last Name (like "SMIT") from both Tables.

Then using this new column (FirstFourLastName), I wrote code for Inner Join in SQL with an aim to assign the Unique "Client Code" to each record in table FMAY.

I am able to get the correct client code assigned but not able to ONLY select distinct records.

My code is:

select     FMAY.[Lender Reference],     CCC.Client,     CCC.ClientCode,     FMAY.FirstFourLastName,     FMAY.[Adviser Share (ex GST)],     FMAY.[Adviser Share (GST)],     FMAY.[Adviser Share (incl GST)],     FMAY.Product  from     [FinanceCommissions26May2021].[dbo].[Finance_Commissions_May26_2021$  ] FMAY     inner join [FinanceCommissions26May2021].[dbo].['20210617145928-Exception - No C$  '] CCC on         FMAY.FirstFourLastName = CCC.FirstFourLastName 

The above code is giving me 6300 records, with the correct client code attached to each record though! However, I only need to assign Client Code to the 350-400 records in the FMAY table.

How can I select distinct records for my problem please?