select candidate and manager for a category

[TCOEN]

Hi,

In our database mananger and candidate names etc are stored in the same table. A column called type allows program to seperate using manager (type m) and candidate (type c). How do I select manager name and candidate name in the one query, e.g I want to select all candidates (limit firstname and lastname with type = c) that have got a job and the manager type = m that they report to.

thanks,

TCOEN

 

[kublait]

You probably have another table in your database that stores the relationship of the candidates to the managers. Join this table to the name tables and then queries by the candidate field. Conversely you can also query the manager field and returns all the candidates that reports to a particular manager. Hope that helps.

 

[TCOEN]

Hi,

Here is the sql statement I would like to get using Brio. I think it involves a table alias.
SELECT "MPSN"."PS_IDNO" Placement_ID,"CANDIDATE"."NM_LTNM" Candidate,"MJOBO"."JC_RLTL" Job_title,
"LPSST"."PC_DESC" Placement_status,"MPSN"."PS_CNRT" Pay_rate,"MPSN"."PS_PSCALE" Payment_frequency,
"MPSN"."PS_R1_US" Agency,"MCMP"."BS_NAME" Company_name,"MPSN"."PS_JC_JC" Job_order,"MNME_A"."NM_LTNM" Manager_name,
"MPSN"."PS_PONO" Cost_centre,"MPSN"."PS_STDT" Start_date FROM "MPSN","MCMP","MJOBO","MNME" "MNME_A","MNME" "CANDIDATE",
"LPSST" WHERE ( mpsn.ps_cs_bs = mcmp.bs_idno (+)) and ( mpsn.ps_jc_jc = mjobo.jc_idno (+)) and
( mpsn.ps_ct_nm = MNME_A.nm_idno (+)) and ( mpsn.ps_cn_nm = CANDIDATE.nm_idno (+)) and ( mpsn.PS_PC_PC = LPSST.PC_IDNO (+))
and ( mpsn.ps_gp_gp = 16) and ( LPSST.pc_idno IN (1,10) ) and ( MPSN.PS_STDT BETWEEN '2004-02-22 00:00:00' AND '2004-02-28 00:00:00' );

 

[kublait]

TCOEN,
Looks like your query will return both manger and candidate name. It seem that the query is extracting from the following tables:
MPSN, MCMP, MJOBO, LPSST, MNME alias as MNME_A, and MNME alias as CANDIDATE.

Pull these tables into the Brio Designer and then join them appropriately as your SQL statemnt describe.

On the SELECT line of the Brio Designer you want all the columns from the tables as describe by your SQL statement.

On the LIMIT line of the Brio Designer set the appropriate value for mpsn.ps_gp_gp, LPSST.pc_idno, MPSN.PS_STDT as describe by your SQL statement.

Then add the MNME_A.NM_LTNM as another limit on the LIMIT line. If you right click the the field and make it a variable limit then Brio will prompt you to select the "Manager Name" value everytime you run it. This should bring back the selected Manager(s) and the associated CANDIDATES.

Hope that help.

 

[TCOEN]

How do I create the alias MNME_A?

 

[kublait]

You will need to pull the table MNME into the Brio Designer twice. Then double click on the table. It should gives you the option to create the new alias

 

[TCOEN]

That worked thanks.