sql select statement help..

[cfdeveloper]

Hello everybody,

I need your help with some sql select statement.

Let me expalin..

I'm working on a logging system for a support desk which allows customers to call the support desk and log a call if something needs to be done or say for example some equipment needs to be brought.

So, coming to my query. A log can be linked to another log. For example, I can link Log B to Log A and at the same time link Log B to Log C (needless to say I can link Log C to Log A and vice versa)

On the page where the support desk can view a log, there is a hyper link which opens up a pop-up that displays the logs linked to this log. I'm trying to write a sql stmt that returns the logs linked to a log.

Db table design:

The log table stores all the the log information

The 'logNumber' column is a primary key and 'LinkedLog' is the column that stores the logNumber.

The way it currently works is as under:
If I was to link Log B to Log A, the LinkedLog column for Log B would have Log A's logNumber. If I was now to link Log B to Log C, the LinkedLog column for Log C would have Log A's logNumber, so if I view the calls linked to log C, it should display Log A and Log B. However if I wish to view the logs linked to Log A, I want it to only display Log B (because the user has not linnked Log C to Log A)

I've tried to write a select stmt but just can't get the logic right.
here is my sql stmt:


<cfquery name="linked" datasource="db">
SELECT logNumber, LinkedLog
FROM Log
WHERE (LogNumber = '#url.ref#' AND LinkedLog <> '#url.ref#')
OR (LinkedLog = '#url.ref#' AND LogNumber <> '#url.ref#')
</cfquery>

url.ref contains the logNumber for the call the user is viewing.

if you have sussed my problem then please can you help me. If you don't understand something then please let me know. I look forward to hearing from you

Best Regards
cfcoder

 

[nagornyi]

According to your requirement

..to link Log B to Log A, the LinkedLog column for Log B would have Log A's logNumber. If I was now to link Log B to Log C, the LinkedLog column for Log C would have Log A's logNumbe

The following two records to be created:

Log        B     C
LinkedLog  A     A

So it is not clear how this statement arees with the above requirement:

if I view the calls linked to log C, it should display Log A and Log B. However if I wish to view the logs linked to Log A, I want it to only display Log B (because the user has not linnked Log C to Log A)

Please clarify.

 

[cfdeveloper]

I appreciate your reply. I'll try to explain what I'm after:

This is what I'm trying to do.

LogNumber Linked Call LogTitle
01DC1111 Log A
01DC2222 01DC1111 Log B
01DC3333 01DC1111 Log C

Because Log B is already linked to Log A, if the user Linked Log B to Log C, the value stored in the LinkedCall column for Log C would be the Log A LogNumber. Now if Log C is viewed on the site and if the user clicked on the 'logs linked' pop-up, he should see both Log A and Log B. This because although the user only linked Log B to Log C, because Log B is already linked to Log A, he should also Log A in the list.

I hope I am making sense.

Regards,
cfcoder

 

[nagornyi]

To further clarify the example, pleae fill the qustion marks

LogNumber   Linked Call   LogTitle
01DC1111                  Log A
01DC2222    01DC1111      Log B
01DC3333    01DC1111      Log C 
01DC4444    ????????      Log D 
01DC5555    ????????      Log E 
01DC6666    ????????      Log F 
01DC7777    ????????      Log G

if:
Log B linked also to D and E, and log C linked also to F and G,
and then indicate what linked Logs user needs to see for these Logs:
B
C
D
E
F
G

Thanks.

 

[cfdeveloper]

Since Log B is directly linked to Log A, linking Log B to another log (D, E) would automatically link Log A to D and E. Similarly because log C is linked to Log A, linking it to F and G would link A, B, C, D and E to F and G

A - B
B - A
C - A, B
D - A, B and C
E - A, B, C and D
F - A, B, C, D and E
G - A, B, C, D, E and F

Hope this helps!

 

[nagornyi]

What goes for the question marks in my example?

 

[cfdeveloper]

01DC4444 01DC2222 Log D
01DC5555 01DC2222 Log E
01DC6666 01DC3333 Log F
01DC7777 01DC3333 Log G

 

[nagornyi]

OK, something is still not complete in your requirements. Looking into the table it is not clear why
B - A
C - A, B
B and C records look equal -- both refer the same in the LinkedLog column. So how would we know that C related to A via B, not B related to A via C?

 

[PHV]

IMHO the real question is: how to design a many to many self join.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cfdeveloper]

guys, I'm really sorry for not getting back sooner. I've been trying to figure out how I want to work myself and I'm all confused. You probably have a better idea of what I'm trying to do.

But to put this simple, imagine a chain and how you link one chain to another.

When creating a new link, I have to first for the following

1) check if the initiator log LinkedLog value is null and

2) the receiver log LinkedLog value is Null

Case 1: If (1 & 2) are true, then insert Log A LogNumber in Linked.A and Linked.B

Case 2: If (1) true and (2) false, then copy Linked.B into Linked.A

Case 3: If (1) false (2) true, then copy Linked.A into Linked.B

Case 4: If 1 and 2 false: and already equal to each other, already linked

Case 5: If 1 and 2 false: and not equal, take Linked.A and overwrite its value into all rows that contain linked.B's original value. For ex, if Log A is linked to Log Y and Log B is linked to Log Z and the user is trying to link B to A, select all rows (LogNumbers) where linked eq Link Z. Then take value from Linked.A and copy to these LogNumbers.

This is what I think I'm trying to do. Hope this is making sense.

 

[nagornyi]

According to these rules, linked are those logs having the same values in their LinkedLog fields. So, for each log you need to find all other logs having the same LinkedLog value. The following SQL should do this:

select
  L1.LogNumber original_log
, L2.LogNumber linked_log
from
  Log L1
, Log L2
where
  L1.LinkedLog = L2.LinkedLog