Difficulty in formulating query

Question

In a system there are plots registrations . The users can classificar as plots for familyDeParcela each of which user has its own familyDeParcela.

As plots are common to all.

I have 4 tables:

User that has cod_usuario PK
Parcel that has cod_parcela PK
Familiadeparcelas that has cod_usuario FK and cod_familia PK
Parcel that has cod_familiaFK , cod_parcela FK , and a PK composed of cod_familia and cod_parcela

Example of use :

We have 5 plots registered

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

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

    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 families SALARIAL and INDENIZATÓRIA that will belong only to him and then can classify

    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 accurate listar all the plots with your Familiadeparcela and if it has not been classified , return only the data from installment, but only that 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 classification user makes it returns 1 extra occurrence for each classification of the same portion as another user did ( but with null in the data of family )

Add at the end a where f.cod_usuario = @codusuario he returns only the plots of user , but not the plots unclassified

What should I do ?

Answer 1

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

If you are correct, you will record the field cod_familia on the table Parcela as a FK (Foreign Key), so there will be no need for the table Parcela_FamiliaParcela. And will also record the field cod_usuario on the table Parcela like a FK.

And your consultation can stay 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’ve cooperated!

Answer 2

The problem was this:

How I started the query from Parcel p and made a left Outer Join Parcel using p.cod_parcela the query returned all the records they had p.cod_parcela including those of other users .

I realized this and then started the query from Familiadeparcela Fp and made the one left Join using fp.cod_familia and @codusuario followed by a right Join to list all the plots and so it worked well !

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

Answer 3

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