Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL concat column+matching rows and then join

Posted on 2014-08-27
9
Medium Priority
?
430 Views
Last Modified: 2014-08-27
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
0
Comment
Question by:mikeysmailbox1
  • 5
  • 4
9 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 40289368
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
 
LVL 1

Author Comment

by:mikeysmailbox1
ID: 40289402
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
 
LVL 25

Expert Comment

by:chaau
ID: 40289410
What version of PostgreSQL do you have? It may not support CTE
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 25

Expert Comment

by:chaau
ID: 40289413
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
 
LVL 1

Author Comment

by:mikeysmailbox1
ID: 40289430
Now it is giving me

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


I am using PostgreSQL 8.3.7
0
 
LVL 25

Expert Comment

by:chaau
ID: 40289437
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
 
LVL 1

Author Comment

by:mikeysmailbox1
ID: 40289467
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
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 40289484
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
 
LVL 1

Author Closing Comment

by:mikeysmailbox1
ID: 40289489
That worked perfectly.

Thanks you for your help.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question