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

 

[erwino]

I can't follow the example completely but why does the query select * from log where lognumber = #url.ref# or linkedlog = #url.ref# not work? IS it the ' in your sql script? You are refering to numbers I suppose, so you don't want to look for strings.
Let me know if this is the solution ;-)

Erwin Oosterhoorn
Analyst Programmer,
ice hockey player/fan.

 

[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

 

[erwino]

I think I do, and let me know if this works or what is not working.

select * from LOG
where (select linkedLog from LOG where lognumber = '#url.ref#')
= LOG.lognumber AND lognumber != '#url.ref#'
or (select linkedLog from LOG where lognumber = '#url.ref#')
= LOG.linkedLog AND lognumber != '#url.ref#'

where the #url.ref# contains the selected log number

select * from LOG
where (select linkedLog from LOG where lognumber = '01DC1111')
= LOG.lognumber AND lognumber != '01DC1111'
or (select linkedLog from LOG where lognumber = '01DC1111')
= LOG.linkedLog AND lognumber != '01DC1111'

I am logging of, so if this doesn't work I'll have another look tomorrow.

Erwin Oosterhoorn
Analyst Programmer,
ice hockey player/fan.

 

[philhege]

As far as I can tell, the logic links log C back to log A. There's no way for anyone to tell that the user linked log C to log B, because they both link back to A.

If you want to see, simply, a log and all of its linked logs, the url.ref needs to be the linkedlog value of the current log. Then the query is just

select * from Logs where linkedlog = #url.ref# OR
lognumber = #url.ref#

This is a set of all the records with either the lognumber or the linknumber equal to the current record's link number. Isn't that what your list displays?

HTH,

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....
-----------
A skeleton walks into a bar, and says "I'll have a beer and a mop.