topic sql join problem in Talend Studio

sql join problem

Anonymous — Mon, 11 Mar 2019 17:05:04 GMT

Hello Community !

I'm trying to solve a problem which consist in :

1/ My first input :

SELECT count(FileName) as number,
FileName
FROM mvt_Ods
group by FileName

2/ My second input :

SELECT count(FileName) as numberCondition,
FileName

FROM mvt_Ods
where Return_Mark!=' ' and PackingList_Type!='P'
group by FileName

3/ A table in my database : mvt_Ods which containt Return_Mark, PackingList_Type, FileName and other attributes

To summarize I must check the conditions:
Return_Mark! = '' And PackingList_Type! = 'P'
If both of these conditions are true I must get the FileName field
and then in a tMap I have to create a table that does not contain the rows that have been checked by both conditions

Note :
the number of rows that satisfy both conditions for the same FileName must be equal to the number of rows in FileName

Example:
Return_Mark! = '' And PackingList_Type! = 'P' => Result:
FileName A: 100 lines
FileName B: 200 Lines

Number of lines for:
100
B: 300

For my output I have to remove A and keep only B

Re: sql join problem

DataTeam1 — Mon, 11 Mar 2019 19:11:44 GMT

I didn't understand you but maybe this helps you:

This should give you list of filenames where both of conditions are true and number of filenames where both of conditions are true is the same as number of all records with this filename (another way, this is the list of filenames for which in 100% of cases both conditions are true)

SELECT FileName, count(*) as number
FROM mvt_Ods
group by FileName

HAVING SUM(CASE WHEN Return_Mark <> ' ' and PackingList_Type <> 'P' THEN 1 ELSE 0 END) = count(*)

I don't know which type of database do you use (MySQL, MSSQL, Oracle...)

Re: sql join problem

Anonymous — Tue, 12 Mar 2019 08:06:25 GMT

Hello DataTeam,

I'm using SQL Server

I have a table wich contains Return_Mark, PackingList_Type and FileName and other columns.

For example the result of Return_Mark!=' ' && PackingList_Type !='P' is

for FileName ='A' : 100 rows

for FileName ='B' : 200 rows

And the result of distinct count(FileName) is

for FileName ='A' : 100 rows

for FileName ='B' : 300 rows

FileName='A' have the same number of rows for the two queries so I have create a new table wich contain the other FileName(s) and for this example it's FileName ='B'.