logo
down
shadow

Join Data from 4 tables in DB2 and add a new column that identifies, which table the record is from


Join Data from 4 tables in DB2 and add a new column that identifies, which table the record is from

By : user2949878
Date : November 17 2020, 04:28 AM
this one helps. Use Union all to combine the results of different tables and use a additional column to differentiate the rows.
After that use ListAgg function to group the duplicate rows into comma separated values.Try this.
code :
SELECT EmpId,Code,Date,Listagg(Tablename,',') within GROUP (ORDER BY empid) AS Tablename 
FROM   (SELECT EmpId,Code,Date,'Pay' AS Tablename
        FROM   Pay
        UNION ALL
        SELECT EmpId,Code,Date,'Location' AS Tablename
        FROM   Location
        UNION ALL
        SELECT EmpId,Code,Date,'deparment' AS Tablename
        FROM   deparment
        UNION ALL
        SELECT EmpId,Code,Date,'job' AS Tablename
        FROM   job) A
GROUP  BY EmpId,Code,Date 


Share : facebook icon twitter icon
MYSQL join tables based on column data and table name

MYSQL join tables based on column data and table name


By : Ben Coleman
Date : March 29 2020, 07:55 AM
may help you . No, you can't join to a different table per row in table1, not even with dynamic SQL as @Cade Roux suggests.
You could join to the hotdog table for rows where food is 'hotdog' and join to other tables for other specific values of food.
code :
SELECT * FROM table1 JOIN hotdog ON id = foodid WHERE food = 'hotdog'
UNION
SELECT * FROM table1 JOIN apples ON id = foodid WHERE food = 'apples'
UNION
SELECT * FROM table1 JOIN soups  ON id = foodid WHERE food = 'soup'
UNION 
...
Mysql Join 3 tables and output all record in first table regardless another 2 table record is null

Mysql Join 3 tables and output all record in first table regardless another 2 table record is null


By : Agnes Ferraz
Date : March 29 2020, 07:55 AM
like below fixes the issue I have 3 tables which is employee,quota and leave. I want my query to show all records in employee table. 3 tables primary key as ID
code :
$query="select emp.empId, emp.name, q.leavetype, q.quota, l.date, l.day from employee as emp inner join quota as q on emp.empId = q.empId and q.leavetype = 'sick' inner join leave as l on l.empId = emp.empId and l.leavetype = 'sick'";
select e.id, e.name, q.leavetype, q.quota, le.date, le.day from emp as e inner join quota as q on e.id = q.empId left join emp_leave as le on e.id = le.empId and le.leavetype='sick'
Join two MYSQL Database tables and check if second table has no record then return FALSE and if a column has some specif

Join two MYSQL Database tables and check if second table has no record then return FALSE and if a column has some specif


By : bonus630
Date : March 29 2020, 07:55 AM
hop of those help? I believe you need to change your join on gmmu_case_shortlist to an LEFT JOIN as well. The query as you have it written is only going to return records that are in both gca and gcs.
Also, your case statement needs to be modified to check gcs.status for 'removed' like:
code :
CASE WHEN gcs.status='removed' THEN FALSE 
     WHEN gcs.id is null THEN FALSE 
     else TRUE 
end as shortlist
JOIN two tables, but only include data from first table in first instance of each unique record

JOIN two tables, but only include data from first table in first instance of each unique record


By : Atef
Date : March 29 2020, 07:55 AM
hope this fix your issue Title might be confusing. , Found out that I can name my tables in the query like so:
code :
FROM Case_Calls Calls
IIF( Calls.[ID] <> (select top 1 [ID] from Case_Calls where [Case ID] = Calls.[Case ID]), '', Cases.[Creator]) As [Case Creator]
SQL server 2008: join 3 tables and select last entered record from child table against each parent record

SQL server 2008: join 3 tables and select last entered record from child table against each parent record


By : user2449661
Date : March 29 2020, 07:55 AM
To fix the issue you can do I have following 3 tables and last entered reasoncode from Reasons table against each claimno in claims table. , Is this what you want?
code :
select claimno, enterydate, user, reasoncode
from (select c.claimno, r.*,
             row_number() over (partition by c.claimno order by r.entrydate desc) as seqnum
      from charges c join
           reasons r
           on c.chargeid = r.chargeid
     ) cr
where seqnum = 1;
Related Posts Related Posts :
  • SQL - how to group transactions for a certain date pattern?
  • How to Index SQL with multiple AND conditions nested inside OR
  • SQL rank with priority
  • Does BigQuery support SubQueries?
  • Any ideas for persisting H2 Database In-Memory mode transaction?
  • How to improve SQL query performance (correlated subqueries)?
  • Filter repeated columns in Oracle
  • SQL Server join on whichever column has value populated
  • use sql stored procedure to insert data which is returned from a query stored in a table
  • Query not working1
  • SQL - using an alias in a where clause in a subquery
  • How to search for a whole word with special char using oracle contains?
  • Production Perfomance of using SELECT * statements?
  • String concatenation not working as expected in SELECT statement
  • How to detect which stored procedures UPDATE or INSERT into a certain TABLE?
  • Select statement inside CASE - SQL
  • .Net Float error 4 - float(4) = 4.44089209850063E-16
  • How to show as column to recursive rows in a table With SQL Server
  • SQL Server error: Column name or number of supplied values does not match table definition
  • How to find a table name which's column name consist cl_
  • Cannot access existing table from WCF Code
  • SQL CREATE VIEW Error
  • Many Statement in the same transaction PLSQL
  • Tables are not joining properly in sql server 2008
  • DB2 how to sum two column from two different table
  • SQL msAccess: list unfinished courses of a student
  • SQL Server delete multiple rows
  • Finding max(date) from the resultant query output
  • updating multiple columns using case statement in sql server
  • Nested SQL query having select subquery in update statement
  • "putting" a certain object to the screen from inside an array
  • Retrieve segment from value
  • Error while creating a table in oracle 11g
  • Transaction with Sequelize doesn't work
  • Search time complexity of this sql query
  • How to return unique records between two tables without using distinct and union?
  • Is it possible to maximum occurences of a value for each key in my table all in one query?
  • Divide table into groups of 5000 based on rank
  • rolling back exec and sp_OA methods
  • How to convert multiple rows to one row in SQL Server?
  • How to use substring and charindex in google query language
  • Find table information where part of column matches char variable
  • Need to retrieve rows from table where the following condition was not satisfied column A = column B and column B = colu
  • Assigning an index to a constraint
  • Retrieve records from multiple Records returned by Sub-Query
  • SQL DELETE group of records based on opposite group being empty
  • SQL GetDate() returns wrong time
  • Total sales amount for customers in cities
  • SQL Server -- updating the `sys.*` tables and not just reading them
  • SQL Order By using concat
  • SQL server 2008 - subquery returned more than one value
  • Group Data Hourly and Insert Into a Summary Table in Postgres
  • Execute SQL Server Pass-Through Query From Access VBA
  • MS Access SQL Transform Aggregate Manipluation of Values for Pivot
  • SQL "if exists..." dynamic query
  • HANA: Split string?
  • Create a list of dates and match records to the list
  • Why won't SQL round correctly from my batch script?
  • SQLServer - Multiple PIVOT on same columns
  • Update a table foreign keys by substituting by a lookup from the result of another table
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org