Mysql - I've messed up - help!

[whitecrook]

Hi all

Doing some my sql was trying to join some data from one table in to
another and realised I think I've made a bit of a mistake - what should I do -
or what should I have done....

If you look at the php out put I want the 'friendly name' to be listed instead
of the mount point. But I can't see a way to get the frinedly name for
input AND output as well?


table locations:

table jobqueue

output on web page

out put with 'freindly name'

and the code that produced that:

SELECT `jobname` AS `Job Name` , `sourceroot` , `sourcepath` AS `Source Location` , `destroot` AS `Output Drive` , `destpath` AS `Destination` , `batchstart` AS `Start` , `batchend` AS `End` , `timestamp` AS `Submitted` , `status` AS `Status` , `friendly_name` 
FROM `jobqueue` 
INNER JOIN locations ON locations.mount_point = jobqueue.sourceroot
WHERE 1 
LIMIT 0 , 30

edit i could actually just store the friendly name along with the moutn point in the job queue table. That would probably work for now.

 

[kkelly]

Oracle/MS SQL based answer

Select a.jobname, a.sourcesroot, b.friendly_name
from jobqueue a, locations b
where a.sourceroot = b.mount_point

Or am I missing something? email me if this is daft I do a lot of work on oracle systems not mysql but I will come up with something

 

[whitecrook]

The column friendly_name has to be 'joined' on two other fields, sourceroot and destroot.

I hacked it by quering the db again for the associated friendly name for each mount punt both source and dest and saving that into the job queue so the jobqueue has every bit of info required.

But, I'm ditching the 'queue' system for now and will concentrate on other aspects of this task, and will perhaps look at the queue system again in the future.

 

[kkelly]

Select job, sour, name
from
(
Select a.jobname as "job", a.sourcesroot as "sour", b.friendly_name as "Name"
from jobqueue a, locations b
where a.sourceroot = b.mount_point
union
Select a.jobname as "job", a.sourcesroot as "sour", b.friendly_name as "Name"
from jobqueue a, locations b
where a.destroot = b.mount_point
);

?