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
LVL 1
mikeysmailbox1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
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,  '
') 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,  '
') 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
0
mikeysmailbox1Author Commented:
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
0
chaauCommented:
What version of PostgreSQL do you have? It may not support CTE
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

chaauCommented:
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,  '
') 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,  '
') 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

0
mikeysmailbox1Author Commented:
Now it is giving me

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


I am using PostgreSQL 8.3.7
0
chaauCommented:
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),  '
') 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),  '
') 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

0
mikeysmailbox1Author Commented:
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.
0
chaauCommented:
You have to use ARRAY. However to use it you will have to modify the statement 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 d.table_id,d.job_id,
array_to_string(array(SELECT condition || '  ' || odate || ' ' || and_or FROM def_lnki_p WHERE table_id = d.table_id AND job_id = d.job_id),  '
') as incondition
from def_lnki_p d
group by d.table_id,d.job_id) AS t3 ON a.TABLE_ID = t3.TABLE_ID AND b.JOB_ID = t3.JOB_ID
INNER JOIN (
select o.table_id,o.job_id,array_to_string(array(SELECT condition || '  ' || odate || ' ' || sign FROM def_lnko_p  WHERE table_id = o.table_id AND job_id = o.job_id),  '
') as outcondition
from def_lnko_p o
group by o.table_id,o.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

If you run these type of queries very often you should consider upgrading your server
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mikeysmailbox1Author Commented:
That worked perfectly.

Thanks you for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.