Tag: Join

mariadb – Query all of a sudden stopped working – Simple left join but cannot see what is wrong

I had a query that was a simple left join between two tables with IS NULL included in the where clause because I needed all of the rows of the left table shown, even if it gave null values in the right table.

This worked as I had it working within my php code and my site was displaying what it needed. I haven’t looked at this in over a week and went back today to see that it is now all of a sudden not working even though I haven’t touched it.

I have created a db fiddle here with my exact code and tables – https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=2effc82390641ce513806252700fd25c

I want to show – all rows in the left table (level_quiz) and all rows in the right table (student_points) where student_no = 40204123 OR there are NULL rows

Could anyone please have a look at this to see why it is not showing the extra row of the left table? (where there would be NULL values for the right table)

This would be much appreciated.

spells – Can someone under the effect of Join Pasts lie?

Specifically looking at Pathfinder’s Join Pasts, which reads:

With touches to the targets’ foreheads, you bring them into mental communion. The targets can share thoughts and experiences, but not words. When one target attempts to Recall Knowledge, the other can Aid the first target’s skill check, using any Lore skill (even if that Lore wouldn’t normally apply) without having made any preparations to Aid.

Could someone under the effect of this cantrip lie when sharing a thought or an experience? In my game, we’ve used this spell to look at a target’s past experiences and see things through their eyes – but the idea that they could just lie (for instance, by sharing that they grew up impoverished or were stolen from) came up recently.

SQL – Left join with sub select

I need to assign an address to the patient. The one with the highest date is always current in the address list. I would select this with “max”. I compiled the query according to the examples I found here, but if I put more than one column in the sub-select in the select, it doesn’t work. Where is the mistake?
I need to join tables using “p.PatientId = x.SubjectId”, but only always with one row from table x.
MS-SQL

    SELECT * FROM Patient p
    LEFT JOIN (
                select SubjectId, max(LastUpdated) from  AddressBase
                where AddressTypeId = '1'
                group by SubjectId
              ) x  ON p.PatientId = x.SubjectId

// Output: No column name was specified for column 2 of 'x'.

This works, but returns more results than it should:

SELECT * FROM Patient p
LEFT JOIN (
            select SubjectId from  AddressBase
            where AddressTypeId = '1'
            group by SubjectId
          ) x  ON p.PatientId = x.SubjectId

t sql – How to join 2 tables when second table will return more rows?

im trying to join 2 tables with condition – if it matches than im expecting to get all results from another table.

Current querry that doesn’t work :

SELECT (CASE WHEN ZL.NumerCzesci LIKE '%ZŁOŻENIE%' 
  THEN (SELECT NumerCzesci FROM PodZlozenie WHERE NumerPodZlozenia = ZL.NumerCzesci) ELSE ZL.NumerCzesci END) AS (NumerCzesci) FROM Zlozenie ZL LEFT JOIN PodZlozenie PD ON ZL.NumerZlozenia = PD.NumerPodZlozenia

Table (Zlozenie) looks like :

CREATE TABLE (dbo).(Zlozenie) (
    (Id_Zlozenia)   INT          IDENTITY (1, 1) NOT NULL,
    (NumerZlozenia) VARCHAR (50) NOT NULL,
    (NumerCzesci)   VARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ((Id_Zlozenia) ASC)
);
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-001-000-ROTOR-3-1-3','300-000-001 - WKŁADKA GÓRNA');
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-001-000-ROTOR-3-1-3','300-000-002 - WKŁADKA DOLNA');    
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-001-000-ROTOR-3-1-3','300-000-003 - UCHO ROTORA 11 RAM 3 SEKCJE');    
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-001-000-ROTOR-3-1-3','300-000-005 - RAMIĘ ROTORA 565mm');    
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-021-000 - DACH-BA3-1','300-021-001 - ZŁOŻENIE POKRYWY GÓRNEJ BA3-1');
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-021-000 - DACH-BA3-1','300-021-002 - ZŁOŻENIE POKRYWY DOLNEJ TYŁ BA3-1');
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-021-000 - DACH-BA3-1','300-021-003 - ZŁOŻENIE POKRYWY DOLNEJ PRZÓD BA3-1');

Table (PodZlozenie) looks like :

CREATE TABLE (dbo).(PodZlozenie) (
    (Id_PD)            INT          IDENTITY (1, 1) NOT NULL,
    (NumerPodZlozenia) VARCHAR (50) NOT NULL,
    (NumerCzesci)      VARCHAR (50) NOT NULL
);
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-001 - ZŁOŻENIE POKRYWY GÓRNEJ BA3-1','300-020-001 - BOK POKRYWY GÓRNEJ')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-001 - ZŁOŻENIE POKRYWY GÓRNEJ BA3-1','300-020-002 - DEKIEL POKRYWY GÓRNEJ')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-001 - ZŁOŻENIE POKRYWY GÓRNEJ BA3-1','300-020-003 - KĄTOWNIK')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-001 - ZŁOŻENIE POKRYWY GÓRNEJ BA3-1','300-020-004 - WSTAWKA UZUPEŁNIAJĄCA')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-002 - ZŁOŻENIE POKRYWY DOLNEJ TYŁ BA3-1','300-020-010 - BLASZKA PROWADZĄCA POKRYWĘ')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-002 - ZŁOŻENIE POKRYWY DOLNEJ TYŁ BA3-1','300-020-011 - POKRYWA DOLNA TYŁ BA3-1')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-002 - ZŁOŻENIE POKRYWY DOLNEJ TYŁ BA3-1','300-020-012 - PŁASKOWNIK BA3-1')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-003 - ZŁOŻENIE POKRYWY DOLNEJ PRZÓD BA3-1','300-020-013 - WZMOCNIENIE POKRYWY')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-003 - ZŁOŻENIE POKRYWY DOLNEJ PRZÓD BA3-1','300-020-014 - UCHWYT')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-003 - ZŁOŻENIE POKRYWY DOLNEJ PRZÓD BA3-1','300-020-015 - POKRYWA DOLNA PRZÓD BA3-1')

Desired output:

(NumerCzesci)
300-000-001 - WKŁADKA GÓRNA
300-000-002 - WKŁADKA DOLNA
300-000-003 - UCHO ROTORA 11 RAM 3 SEKCJE
300-000-005 - RAMIĘ ROTORA 565mm
300-020-001 - BOK POKRYWY GÓRNEJ
300-020-002 - DEKIEL POKRYWY GÓRNEJ
300-020-003 - KĄTOWNIK
300-020-004 - WSTAWKA UZUPEŁNIAJĄCA
300-020-010 - BLASZKA PROWADZĄCA POKRYWĘ
300-020-011 - POKRYWA DOLNA TYŁ BA3-1
300-020-012 - PŁASKOWNIK BA3-1
300-020-013 - WZMOCNIENIE POKRYWY
300-020-014 - UCHWYT
300-020-015 - POKRYWA DOLNA PRZÓD BA3-1

sql server – Joining two left join queries

i have two queries the first one :

 Select
VCRNUM_0 As 'Tranx_Number',
ACTQTY_0 As 'Quanity',
sto.CREDAT_0 As 'Create_Date',
sto.PROD_DATE_0,
sto.PROD_TIME_0,
sto.CREUSER_0,
sto.ITMREF_0,
sto.VCRNUMORI_0 as 'Work_Order_number',
itm.ITMWEI_0,
sto.ACTQTY_0 * itm.ITMWEI_0 As Weight,
itm.ITMDES1_0 as 'SKU_Description',
a1.TEXTE_0 As 'Work_Center_Description',
gope.CPLWST_0 as 'Work_Center_Number'

From ZSTOJOU sto JOIN MFGOPE gope on sto.VCRNUMORI_0 = gope.MFGNUM_0
LEFT JOIN ATEXTRA a1 ON gope.CPLWST_0= a1.IDENT1_0 and a1.CODFIC_0 = 'WORKSTATIO' and a1.ZONE_0='WSTDESAXX' AND a1.LANGUE_0='ENG'
LEFT JOIN APLSTD APL ON sto.TRSTYP_0= APL.LANNUM_0 and APL.LAN_0= 'ENG' and APL.LANCHP_0 = 704
LEFT JOIN ITMMASTER itm on sto.ITMREF_0=itm.ITMREF_0
Left Join ATEXTRA a2 On itm.TSICOD_6=a2.IDENT2_0
and a1.CODFIC_0 = 'ATABDIV' and a2.ZONE_0='LNGDES'AND a2.LANGUE_0='ENG'And a2.IDENT1_0=26
WHERE sto.TRSTYP_0 =5
and  sto.VCRTYPORI_0=10
and sto.VCRTYPREG_0 = 0
 AND gope.CPLWST_0 NOT IN('22500L','22600L','225C0L','612B0l','611A0L','214G0','81000L','22050')
 and gope.CPLWST_0 is not null

then i have second query :

Select TOP 5 ITMMASTER.ITMREF_0, ITMMASTER.ITMDES1_0,ITMMASTER.TCLCOD_0, a3.TEXTE_0
from ITMMASTER join ITMCATEG cat ON ITMMASTER.TCLCOD_0= cat.TCLCOD_0
LEFT Join ATEXTRA a3 ON ITMMASTER.TCLCOD_0= a3.IDENT1_0 and a3.CODFIC_0 = 'ITMCATEG' and a3.ZONE_0='TCLAXX' AND a3.LANGUE_0='ENG'
Where ITMMASTER.ITMREF_0 ='2AL00HR0'

my question is how do i left join the two.?

sqlite – How do I show zero when I use count by using self left join?

I am trying to use count() to count the number of the module where the particular student got the average mark below 40. It also needs to show the number 0 if the student does not have any assessment mark lower than 40. However, my query does not show any 0 number at all.

CREATE TABLE assessment (
assessmentId text, 
mark integer, 
studentId text, 
moduleId text, 
PRIMARY KEY (assessmentId, studentId, moduleId), 
FOREIGN KEY(studentId) REFERENCES student(studentId), 
FOREIGN KEY(moduleId) REFERENCES module(moduleId)) ";

in each record, it shows the module that the assessment is from, and show which student took the assessment.

this is my query

 "select count(m.moduleId), avg(e.mark), m.studentId 
from assessment e 
left join assessment m on m.assessmentId = e.assessmentId 
and m.studentId = e.studentId 
and m.moduleId = e.moduleId 
group by m.studentId 
having avg(e.mark)<40";

the module could have more than one assessment, therefore the avg(e.mark) is to find out the average mark for each module for each student. Therefore it is group by studentId

so what i want the query to show is:


studentId|avg(e.mark)|count(m.moduleId)
1        | 50        | 0
2        | 20        | 2

However I am getting the result below


studentId|avg(e.mark)|count(m.moduleId)
1        | 20        | 2

I really don’t know where the problem is

python – How to join two Dataframes and plot data as one line in graph using ‘date’ as index?(Plotly Express)

I’m trying to plot a line graph of two datasets that each contain the values ‘date’,’conversions’, and ‘cpi’ as one line. The graph displays a daily count of the values, so I’m having problems dealing with properly graphing the values of each data seat on the same day: enter image description here

The Dataframe used in this graph:

enter image description here

My code is:

asa_installs_time = get_installs_time(start_date, end_date)
ga_installs_time = get_GAinstalls_time(start_date, end_date)
asa_installsTime_df = pd.DataFrame.from_dict(asa_installs_time(1))
ga_installsTime_df = pd.DataFrame.from_dict(ga_installs_time)
all_installsTime_df = pd.concat((ga_installsTime_df, asa_installsTime_df))
installs_time_series_chart = px.line( all_installsTime_df, x= all_installsTime_df('date'), all_installsTime_df('conversions'), title='Installs per Day')

return (all_installsTime_df)

How can I fix the issue where two of the same dates are graphed?

postgresql – Postgres 12.4 inner join with where statement = ‘string’ returning error

I am running a db on postgres12.4

I have been trying to run a query that looks like this:

SELECT * FROM table_1 INNER JOIN table_2 ON table_1.column = table_2.column WHERE table_1.column = ‘string’;

I am being returned:

ERROR: column 'string' does not exist

The following queries work fine:

SELECT * FROM table_1 INNER JOIN table_2 ON table_1.column = table_2.column WHERE table_1.column = int;

SELECT * FROM table_1 WHERE column = ‘string’;

I read several posts about this error, which seems very common and is usually solved by switching up quotation variations around the table name, column name, and the name of the string I am querying for. I have tried every variation of double and single quotes around each table name, column name, and name of string, and nothing worked.

For some reason, it seems that querying for a string is what is throwing everything off. I am new to postgres, am I missing something? Appreciate any advice possible

database – Join Query on WP_USERMETA Table

I want to check if user time zone is perth and its belong from Group A.

MY QUERY IS NOT WORKING

SELECT * FROM `wp_users` AS t1 LEFT JOIN wp_usermeta AS t2 ON 
t1.ID=t2.user_id 
WHERE t2.meta_key='Time_Zone'
AND t2.meta_value = 'PERTH'
AND t2.meta_key='user_class_group'
AND t2.meta_value='Group A'

THIS QUERY IS WORKING IF I USED ON 01 AND

SELECT * FROM `wp_users` AS t1 LEFT JOIN wp_usermeta AS t2 ON 
t1.ID=t2.user_id 
WHERE (t2.meta_key='Time_Zone') 
AND t2.meta_value='PERTH'

enter image description here
TABLE WHICH I NEED TO CHECK

appletv – How do I join my Apple TV to a different home?

I’m living in a shared house with a roommate. We each have our own Apple TV. Currently our Apple TVs (each linked to our respective Apple ID) show up in different homes in the Home app.

We want both Apple TVs to show up in the same Home. Ideally we just want one shared home between both of us.

How do I un-link my Apple TV from the current home, and then join it to my roommate’s home?

When I go into “AirPlay and HomeKit” these are the only options I see:

  • Room: (Select a Room)
  • My Home: (Disabled)

I’m using Apple TV 4K (A1842) with tvOS 14.3.