asked on

SQL concat column+matching rows and then join

Hi

I have 4 tables that I need to combine and not sure how this show work.

Tables 1 & 2 are linked by table_id column and I can get the columns from them I need.

Table 2 is linked to tables 3 & 4 buy table_id & job_id.
Tables 3 & 4 need to be concatinated onto a single line with a seperator of 
 where the value of table_id & job_id match to Table 2.

Connecting tables 1 & 2 is easy.

select a.TABLE_ID,
b.JOB_ID,
a.DATA_CENTER
a.SCHED_TABLE,
b.PARENT_TABLE,
b.APPLICATION,
b.GROUP_NAME,
b.MEMNAME,
b.JOB_NAME
from DEF_TABLES a,
DEF_JOB b
where a.TABLE_ID = b.TABLE_ID
and a.DATA_CENTER = 'DEV'

Now I need to combin in to the above the join & concatination of 3 & 4

Table 3 values I need to concatinate "condition odate and_or" and then concatinate matching table_id & job_id that are a like.

select table_id,job_id,condition || ' ' || odate || ' ' || and_or || '
' as incondition from def_lnki_p
order by table_id,job_id

The above gives me

table_id job_id incondition
----------- --------- ---------------------------------------------------------------------------------------------------------------------------------------
9 2 job1-TO-job2 ODAT A

9 2 job1-TO-job3 ODAT A


but need this

table_id job_id incondition
----------- --------- ---------------------------------------------------------------------------------------------------------------------------------------
9 2 job1-TO-job2 ODAT A
job1-TO-job3 ODAT A

Table 4 values I need same type of query as the above.

table_id,job_id,condition || ' ' || odate || ' ' || and_or || '
' as outcondition from def_lnko_p
order by table_id,job_id

table_id job_id outcondition
----------- --------- ---------------------------------------------------------------------------------------------------------------------------------------
9 2 job1-TO-job2 ODAT -

9 2 job1-TO-job3 ODAT -


but need this

table_id job_id outcondition
----------- --------- ---------------------------------------------------------------------------------------------------------------------------------------
9 2 job1-TO-job2 ODAT -
job1-TO-job3 ODAT -

The end result is to do a combination of the first query and add in the third and forth
to get the final of

table_id,job_id,data_center, sched_table, parent_table, application, group_name, memname, job_name, incondition , outcondition
--------,------,-----------,--------------,----------------,--------------,------------,---------,----------,--------------------------------------------------------,--------------------------------------------------------
9 ,2 ,DEV ,TABLE001 ,TABLE001 ,APP001 ,GRP001 ,JOB001 ,JOB001 ,job1-TO-job2 ODAT A
job1-TO-job3 ODAT A ,job1-TO-job2 ODAT -
job1-TO-job3 ODAT -

I am not sure how to do a concat+row join and then how to full join the 3 queries.

Thanks for the help

Mike

chaau

You will need to use string_agg for the columns in Table3 and table4 first, and then join them with table1 and Table2:

with t3 (table_id,job_id,incondition) as (
select table_id,job_id,string_agg(condition || '  ' || odate || ' ' || and_or,  '&#10;') as incondition 
from Table3
group by table_id,job_id),
t4 (table_id,job_id,outcondition) as (
select table_id,job_id,string_agg(condition || '  ' || odate || ' ' || and_or,  '&#10;') as outcondition 
from Table4
group by table_id,job_id)
select a.TABLE_ID,
       b.JOB_ID,
       a.DATA_CENTER
       a.SCHED_TABLE,
       b.PARENT_TABLE,
       b.APPLICATION,
       b.GROUP_NAME,
       b.MEMNAME,
       b.JOB_NAME,
       t3.incondition,
       t4.outcondition
  from DEF_TABLES a INNER JOIN DEF_JOB b ON a.TABLE_ID = b.TABLE_ID
INNER JOIN t3 ON a.TABLE_ID = t3.TABLE_ID AND b.JOB_ID = t3.JOB_ID
INNER JOIN t4 ON a.TABLE_ID = t4.TABLE_ID AND b.JOB_ID = t4.JOB_ID
 where a.DATA_CENTER = 'DEV'

Open in new window

BTW, try to use INNER JOINs instead of comma-separated joins. This way the query is more readable

mikeysmailbox1

ASKER

Getting syntax error near t3

Not sure what it is.

with t3 (table_id,job_id,incondition) as (
select table_id,job_id,string_agg(condition || ' ' || odate || ' ' || and_or, '
') as incondition
from def_lnki_p
group by table_id,job_id),
t4 (table_id,job_id,outcondition) as (
select table_id,job_id,string_agg(condition || ' ' || odate || ' ' || sign, '
') as outcondition
from def_lnko_p
group by table_id,job_id)
select a.TABLE_ID,
b.JOB_ID,
a.DATA_CENTER,
a.SCHED_TABLE,
b.PARENT_TABLE,
b.APPLICATION,
b.GROUP_NAME,
b.MEMNAME,
b.JOB_NAME,
t3.incondition,
t4.outcondition
from DEF_TABLES a INNER JOIN DEF_JOB b ON a.TABLE_ID = b.TABLE_ID
INNER JOIN t3 ON a.TABLE_ID = t3.TABLE_ID AND b.JOB_ID = t3.JOB_ID
INNER JOIN t4 ON a.TABLE_ID = t4.TABLE_ID AND b.JOB_ID = t4.JOB_ID
where a.DATA_CENTER = 'DEV'

thanks

chaau

What version of PostgreSQL do you have? It may not support CTE

chaau

Modify the query to this:

select a.TABLE_ID,
       b.JOB_ID,
       a.DATA_CENTER,
       a.SCHED_TABLE,
       b.PARENT_TABLE,
       b.APPLICATION,
       b.GROUP_NAME,
       b.MEMNAME,
       b.JOB_NAME,
       t3.incondition,
       t4.outcondition
  from DEF_TABLES a INNER JOIN DEF_JOB b ON a.TABLE_ID = b.TABLE_ID
INNER JOIN (
select table_id,job_id,string_agg(condition || '  ' || odate || ' ' || and_or,  '&#10;') as incondition
from def_lnki_p
group by table_id,job_id) AS t3 ON a.TABLE_ID = t3.TABLE_ID AND b.JOB_ID = t3.JOB_ID
INNER JOIN (
select table_id,job_id,string_agg(condition || '  ' || odate || ' ' || sign,  '&#10;') as outcondition
from def_lnko_p
group by table_id,job_id) AS t4 ON a.TABLE_ID = t4.TABLE_ID AND b.JOB_ID = t4.JOB_ID
 where a.DATA_CENTER = 'DEV'

Open in new window

mikeysmailbox1

ASKER

Now it is giving me

ERROR: function string_agg(text, unknown) does not exist

I am using PostgreSQL 8.3.7

chaau

Yes, string_agg exists since version 9.0. You need to use:
array_to_string(array_agg(columnName), separator), like this:

select a.TABLE_ID,
       b.JOB_ID,
       a.DATA_CENTER,
       a.SCHED_TABLE,
       b.PARENT_TABLE,
       b.APPLICATION,
       b.GROUP_NAME,
       b.MEMNAME,
       b.JOB_NAME,
       t3.incondition,
       t4.outcondition
  from DEF_TABLES a INNER JOIN DEF_JOB b ON a.TABLE_ID = b.TABLE_ID
INNER JOIN (
select table_id,job_id,array_to_string(array_agg(condition || '  ' || odate || ' ' || and_or),  '&#10;') as incondition
from def_lnki_p
group by table_id,job_id) AS t3 ON a.TABLE_ID = t3.TABLE_ID AND b.JOB_ID = t3.JOB_ID
INNER JOIN (
select table_id,job_id,array_to_string(array_agg(condition || '  ' || odate || ' ' || sign),  '&#10;') as outcondition
from def_lnko_p
group by table_id,job_id) AS t4 ON a.TABLE_ID = t4.TABLE_ID AND b.JOB_ID = t4.JOB_ID
 where a.DATA_CENTER = 'DEV'

Open in new window

mikeysmailbox1

ASKER

That did not fix it. It errored with the following

ERROR: function array_agg(text) does not exist

I tried replacing array_agg with array but it did not work either.

ASKER CERTIFIED SOLUTION

chaau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mikeysmailbox1

ASKER

That worked perfectly.

Thanks you for your help.