[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 129
  • Last Modified:

Can someone help with this query?

I am trying to write a query that matches the "request_num" field from two tables, then takes what is in the "subproject" from table A and writes it to the "subproject" field in table B.

What has me stumped, is that table A has duplicate "request_num", each having a different "subproject" number.

What I need to do is:
1. compare the "request_num" field for both tables.
2. then collect the "subproject" numbers in table A.
3. then write/append them to the "subproject" field in table B.

Can someone help me with this query?




0
g118481
Asked:
g118481
  • 7
  • 5
1 Solution
 
g118481Author Commented:
This is the start of my query, but it does not address how to write/append the multiple subproject from A, into one subproject in B.

<cfset dsn="mydb">
<CFQUERY name="query5" dataSource="#dsn#" >
SELECT request_num, subproject
FROM A
</CFQUERY>

<cfloop query="query5">

<CFQUERY name="query6" dataSource="#dsn#">
UPDATE B
SET subproject= '#query5.subproject#'
where INumber='#query5.request_num#'
</CFQUERY>
</cfloop>
0
 
_agx_Commented:
> What has me stumped, is that table A has duplicate "request_num", each having a different
> "subproject" number.

What do you want to do in that case?  Do you want to update TableB with the subproject value from the latest record, eariliest record or something else?

For example if this was the sample data,what do you expect the result to be?

TableA
1234, subproject1
1234, subproject2
1234, subproject3

TableB
1234, NULL
0
 
g118481Author Commented:
This line "where INumber='#query5.request_num#'"  should read
"where request_num='#query5.request_num#'"
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
g118481Author Commented:
From your sample data example, I need the value to look like this in table B:

TableA
request_num | subproject
1234              | subproject1
1234              | subproject2
1234              | subproject3

TableB
request_num | subproject                                            |
1234              | subproject1, subproject2, subproject3 |
0
 
_agx_Commented:
> how to write/append the multiple subproject from A, into one subproject in B.

I would recommend against that. Storing multiple values in a single column like that is almost always a bad idea and its considered a poor design. Two of the reasons being its error prone and is difficult to query.
0
 
g118481Author Commented:
No, it is ok for me to have it this way.  
0
 
_agx_Commented:
Storing delimited values violates normalization rules.  Do a search on database normalization and you'll find many articles that describe in detail why you should avoid storing delimited values.

Consider this, if you need to retrieve all request_num's associated with "subproject2", how will you do that?
0
 
g118481Author Commented:
OK, it really doesn't matter to me what the delimiter is, as long as they are all in that field.

How about a space between each?
0
 
_agx_Commented:
The delimiter used isn't the issue.  My question is have you considered what the ramifications will be? I ask because most people think this structure will make their queries easier.  Only later when they try to retrieve information from the delimited field, or use it in a join do they realize how difficult or near impossible it is.  That's around the time they start thinking, "what the heck have i done?".  

The reality is this structure usually causes nothing but headaches.  So again my question is have you thought about how this will affect your other queries?  

0
 
g118481Author Commented:
Yes, I have thought it out.

This is a table that will never be queried solely on the "subproject" field.
It is queried only based on the "request_num."

I am very confident that this is a non-issue.
Thank you for asking.  
0
 
_agx_Commented:
Okay. You've been warned ;)

One way is to grab the distinct request_num's in Table A

<CFQUERY name="getRequestNumbers" dataSource="#dsn#" >
   SELECT DISTINCT request_num
   FROM A
</CFQUERY>

Inside your loop run a query to get all of the subproject numbers associated with the current #request_num#.  Then use ValueList(queryName.ColumnName, delimiter) to convert the values to a list, and use it in your update statement.  This is not tested, but shows the basic structure.  Make a backup of TableB first of course :)

<cfloop query="getRequestNumbers">
    <cfquery name="getSubProjects" dataSource="#dsn#">
        SELECT  subproject
        FROM    getAllSubProjects
        WHERE   request_num = '#request_num#'
    </cfquery>
   
    <CFQUERY name="query6" dataSource="#dsn#">
        UPDATE B
        SET   subproject= '#ValueList(getSubProjects.subproject, ",")#'
        WHERE INumber = '#request_num#'
     </CFQUERY>
</cfloop>

There are better ways to do this (ex. using a temp table). But that depends on your db type.  
0
 
g118481Author Commented:
Thank you.  This solution works good.
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.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now