LEFT JOIN in Postgres
Ok what's wrong with this SQL query:
select j.* from judge as j left join user as u on j.user_id = u.id where u.email="email@example.com"; ERROR: column "firstname.lastname@example.org" does not exist
You are using double quotes where you should actually be using single quotes. Single quotes denote values, while double quotes represent columns and tables, etc. You can find out more about the difference here.
See also questions close to this topic
how to call postgresql function with array parameter from laravel
I've error in laravel when I call postgresql function with array parameter from laravel.
this is my function :
CREATE OR REPLACE FUNCTION public.get_eprop_break_data( IN integer, IN integer, IN integer, IN integer) RETURNS TABLE(year_budget character, comp_id integer, comp_name character varying, comp_code character varying, div_id integer, div_name character varying, brandcode character varying, brandname character varying, brandid integer, kategoryid integer, activityid integer, activityname character varying, kategoryname character varying, produkid integer, produkname character varying, budget_str_det_id integer) AS $BODY$ DECLARE in_division_id ALIAS FOR $1; in_brand_id ALIAS FOR $2; in_activity_id ALIAS FOR $4; in_produk_id ALIAS FOR $3; BEGIN return Query ( select budget_year , company_id , company_name , company_code , division_id, division_name , brand_code , brand_name , brand_id , kategory_id , activity_id , activity_name , kategory_name , produk_id , produk_name , budget_str_detail_id from eprop_budget_breakdown_mv where division_id = ANY(in_division_id) And brand_id = ANY(in_brand_id) and produk_id = ANY(in_produk_id) and activity_id = ANY(in_activity_id)); END $BODY$ LANGUAGE plpgsql VOLATILE COST 100 ROWS 1000;
This is how I call the function from laravel
$TestGetFunc = DB::select('select * from get_eprop_break_data(?,?,?,?)',[$division_id,$det_prop_brand,$det_prop_activity,$det_prop_varian]);
and this is the Error :
Array to String Conversion
i've googling for this error but no luck. Please .. Help me to Solve this Error.
Many Thanks for your help .
Selecting rows created up to 30 seconds ago in Postgres
Say I want to find orders made recently. I am trying this statement:
SELECT * FROM orders WHERE time < now() - interval '30 second';
This doe not work (It does not select any row.) although the following works:
SELECT * FROM orders WHERE time < now();
Do I need to use a fixed time variable? Like:
SELECT * FROM orders WHERE time < '2018-07-01 12:00:00' - interval '30 second';
I am guessing that I should not use now() in statements because it can be changing during operations ...
Acquiring Aggregate Total in SQL
I am working with PostgreSQL in apache-Superset. using the following code:
SELECT district_name AS district_name, facility_name AS facility_name, encounter_date AS __timestamp, SUM(facility_encounters.measles_1) + SUM(facility_encounters.mr_1) AS "Measles + MR 1", SUM(facility_encounters.mr_2) + SUM(facility_encounters.measles_2) AS "Measles + MR 2", SUM(facility_encounters.bcg_1) AS sum__bcg_1, SUM(facility_encounters.rota_1) AS sum__rota_1, SUM(facility_encounters.rota_2) AS sum__rota_2, SUM(facility_encounters.opv_1) AS sum__opv_1, SUM(facility_encounters.opv_2) AS sum__opv_2, SUM(facility_encounters.pentavalent_1) AS sum__pentavalent_1, SUM(facility_encounters.pentavalent_2) AS sum__pentavalent_2, SUM(facility_encounters.pentavalent_3) AS sum__pentavalent_3, SUM(facility_encounters.opv_3) AS sum__opv_3, SUM(facility_encounters.opv_4) AS sum__opv_4, SUM(facility_encounters.pcv_1) AS sum__pcv_1, SUM(facility_encounters.pcv_2) AS sum__pcv_2, SUM(facility_encounters.pcv_3) AS sum__pcv_3 FROM facility_encounters WHERE encounter_date >= '2018-01-01 00:00:00' AND encounter_date <= '2018-12-31 00:00:00' GROUP BY district_name, facility_name, encounter_date ORDER BY "Measles + MR 1" DESC LIMIT 10000;
How do I aquire:
a) the count of how many children were immunized at each facility, per antigen in a given period?
b) Immunization coverage per facility per antigen in a given period?
c) Total number of fully immunized children per facility per period?
d) Number of children seen/immunizations given should be broken down to the specific No. of antigens given per specified period of time per facility.
e) Coverage for under 1s is very important, however it will also be very important to have statistics for under 2 to monitor how many are getting measles 2. In dashboard context, there’s need to display the fully immunized coverage at age 1. Coverage for under 2s is also preferred.
Oracle SQL left outer join on two subqueries
I'm working in an oracle database and receive the following error:
Microsoft OLE DB Provider for SQL Server : Invalid object name
I've also tried joining in the where clause, but I also receive
Here's the query:
select UE_invoice.InvoiceNo, UE_invoice.DueDate, UE_payment.CheckNo, UE_payment.Amount from (select tblTransaction.InvoiceNo, tblTransaction.DueDate from tblTransaction, tblTransactionType where tblTransaction.Type = tblTransactionType.TransactionType and tblTransaction.Date >= '01/01/2018' and tblTransaction.Type = -88) UE_invoice, (select tblTransaction.InvoiceNo, tblTransaction.DueDate, tblTransaction.CheckNo, tblTransaction.Amount from tblTransaction, tblTransactionType where tblTransaction.Type = tblTransactionType.TransactionType and tblTransaction.Date >= '01/01/2018' and tblTransaction.Type = -86) UE_payment Left outer join UE_invoice on UE_invoice.InvoiceNo = UE_payment.InvoiceNo
PostgreSQL: COALESCE in Join Condition Returning Nulls
I am attempting to join a table on the following conditions:
SELECT * FROM target y LEFT JOIN src.source x ON src.acct_id = x.acct_id AND COALESCE( y.as_of_dt, ( y.as_of_dt - 2 ) ) = x.as_of_dt WHERE y.as_of_dt <= '20180711' ORDER BY y.as_of_dt DESC;
The reason for the
COALESCEis that, on weekends, table
xfinishes its weekend load on Fridays while table
yfinishes its weekend load on Sundays (
as_of_dtvalues during the week are equal); however, when I use this join, values for table x are still returning as
NULLfor weekend runs.
Any help on what I may be missing or doing wrong is appreciated. Thanks.
Join four tables using model in Yii2
this is table1
id1 Name ------------ 1 value1 2 value2
this is table2
id2 Name id1 --------------------- 1 value1 2 2 value2 1
this is table3
id3 Name id2 --------------------- 1 value1 2 2 value2 1
this is table4
id4 Name id3 --------------------- 1 value1 2 2 value2 1
I Want to join above 4 tables in Yii2 with model
select * from table1 left join table2 on table2.id2 = table1.id1 left join table3 on table2.id3 = table1.id2 left join table4 on table2.id4 = table1.id3