cfg database question...

[tony]

I reckon this should be easy because I've definitely worked it out before - but I can't remember how I did it...

I need to find a reference between [b]cfg_Agent[/b] and [b]cfg_Person[/b] within the database Table structure.  I've tried the DBID's, CSID's and pretty much everything else I can think of but still not able to find the correct pair of Foreign/Primary keys.  I expect that there is an interim Table ([b]cfg_???[/b]) which might be used to connect them but I haven't found it yet...

Help please???

Thanks!

TT

[borkokrz]

cfg_agent ? are you sure ? or do you mean cfg_agent_login ? check cfg_login_info table - it links persons to agent_logins.

[tony]

Yes - that's what I meant... 

But I just tried this, in the same Query;

cfg_Agent_Login.dbid = cfg_Login_Info.Agent_Login_dbid
and also
cfg_Login_Info.Person_dbid = cfg_Person.dbid

- returns 35,000+ rows - even with duplicates removed...

Not sure what I am doing wrong...?

Thanks!

TT

[quote author=borkokrz link=topic=6215.msg27060#msg27060 date=1298894678]
cfg_agent ? are you sure ? or do you mean cfg_agent_login ? check cfg_login_info table - it links persons to agent_logins.
[/quote]

[bandorka]

Hi,

It should be work:

SELECT p.dbid, p.user_name, p.last_name, p.first_name, l.login_code
FROM        cfg_person p LEFT OUTER JOIN
                      cfg_login_info i ON p.dbid = i.person_dbid LEFT OUTER JOIN
                      cfg_agent_login l ON i.agent_login_dbid = l.dbid

BR,
bandorka

[bandorka]

Or a more complex one:

SELECT
DISTINCT p.dbid, p.user_name, p.last_name, p.first_name,
l.login_code,
CASE WHEN p.state = 2 THEN 'disabled' ELSE '' END AS status,
s.name FROM
cfg_person AS p
LEFT OUTER JOIN cfg_login_info AS i ON p.dbid = i.person_dbid
LEFT OUTER JOIN cfg_agent_login AS l ON i.agent_login_dbid = l.dbid
LEFT OUTER JOIN cfg_switch AS s ON l.switch_dbid = s.dbid
WHERE (p.is_agent = 2) AND (i.agent_login_dbid IS NOT NULL)

BR,
Bandorka

[tony]

Thank you - I will try them out now...

[tony]

Not exactly the same but had to apply it in Hyperion, after telling it that non-fully-joined queries were ok(!).  Came up with this from the Hyperion SQL Output;

[color=blue]SELECT AL4.dbid, AL4.user_name, AL4.last_name, AL4.first_name, AL2.login_code, AL3.name
FROM dbo.cfg_login_info AL1 FULL OUTER JOIN dbo.cfg_person AL4 ON (AL1.person_dbid=AL4.dbid) FULL OUTER JOIN dbo.cfg_agent_login AL2 ON (AL1.agent_login_dbid=AL2.dbid) FULL OUTER JOIN dbo.cfg_switch AL3 ON (AL2.switch_dbid=AL3.dbid)
WHERE (AL4.is_agent=2 AND (NOT AL1.agent_login_dbid IS NULL))[/color]

It seems to have worked, since I now only have 188 entries... 

Thanks!

TT