Difficulty in formulating a query

Navigation
0

In a system there are registered plots . users can as plots with FamilyParcela where each user has their own / strong>.

plots are common to all.

I have 4 tables:

User that has classificar PK Parcel that has cod_usuario PK
Parcel Family that has cod_parcela FK and cod_usuario PK
Parcel_FamiliaParcela that has cod_familia FK , cod_familia fault , and a PK composed by cod_parcela and cod_familia

Example usage:

We have 5 parcels registered 

    Horas extras além da 6a,
    Horas extras além da 8a,
    danos morais,
    danos materiais,
    periculosidade.

User 1 registers the families cod_parcela , HORAS EXTRAS and DANOS that will belong only to him and can then sort 

    Horas extras além da 6a --> HORAS EXTRAS 
    Horas extras além da 8a --> HORAS EXTRAS
    danos morais ---> DANOS
    danos materiais ---> DANOS
    periculosidade ----> OUTROS

And user 2 registers the OUTROS and SALARIAL families that will belong only to him and can then sort 

    Horas extras além da 6a --> SALARIAL
    Horas extras além da 8a --> SALARIAL
    danos morais ---> INDENIZATÓRIA
    danos materiais ---> INDENIZATÓRIA
    periculosidade ----> NULL ( DEIXAR SEM CLASSIFICAR )

When a user is using precision all plots with their PlotType and if it has not been sorted, return only the plot < strong> but only this user !

If I do 

     select * 
     from Parcela p 
     left outer join Parcela_FamiliaDeParcelas pf ON pf.cod_parcela = p.cod_parcela 
     left outer join FamiliaDeParcelas f on f.cod_familia = pf.cod_familia and f.cod_usuario = @codusuario

With this query for each ranking that the user does it returns 1 extra occurrence for each ranking of the same portion that another user did (but with INDENIZATÓRIA in the data family )

If you add at the end a listar it returns only the plots of the user , but not the unclassified plots

What should I do?

    
asked by anonymous 09.03.2016 / 02:09

3 answers

0

The problem was this:

As I started the query from Parcel p and did a left outer join Parcel_FamiliaParcela using p.cod_parcela the query returned all records that had p.cod_parcela including those of the others users.

I realized this and then I started the query from FpFilter and made a left join using fp.cod_familia and @codusuario followed by a right join to list all plots > and so it worked perfectly!

The final query was: 

    SELECT *  FROM FamiliaDeParcelas 
    LEFT OUTER JOIN PARCELA_FAMILIAPARCELAS f ON f.cod_familia = FamiliaDeParcelas.cod_familia 
    AND FamiliaDeParcelas.cod_usuario = @codusuario 
    RIGHT OUTER JOIN Parcela ON f.cod_parcela = Parcela.cod_parcela
    
09.03.2016 / 05:02
1

The question is very confusing (rs), but from what I understand, I think there is a cardinality error. See in the image below the relational model of the tables:

If it is correct, you will write the cod_familia field in the Parcela table as an FK (Foreign Key), so there is no need for the Parcela_FamiliaParcela table. And it will also write the cod_usuario field in the Parcela table as an FK.

And your query might look like this: 

select *
from parcela p
left join FamiliaDeParcelas fp on (fp.cod_familia = p.cod_familia)
inner join Usuario u on (pf.cod_usuario = u.cod_usuario)
where pf.cod_usuario = @cod_usuario

I hope I have collaborated!

    
09.03.2016 / 02:53
0

See if this solves your problem: 

select * from 'Parcela_FamiliaParcela' 'pfp'
left join 'FamiliaDeParcelas' 'fdp' on ('fdp'.'cod_familia'='pfp'.'cod_familia') 
left join 'Usuario' 'u' on('u'.'cod_usuario'='fdp'.'cod_usuario')
left join 'Parcela' 'p' on ('p'.'cod_parcela'='pfp'.'cod_parcela')
where 'u'.'cod_usuario'=@cod_user
    
09.03.2016 / 05:07
Procedure execution with date type argument in a dblink Swift - How to make and execute an AssyncTask?