Perform SQL query with two foreign keys from the same table?

Question

I’m creating a recognition system for the company and it was almost over, it’s been some time that I’m trying to carry out this consultation but not getting.

I have the following tables:

Collaborator: id_colab, nome_colab and other data that I will not use in this query.

Ex: (5, "Gabriel de Almeida"); (6, "Jose da Silva");

In this table I have registered values of recognition keys

keyfix: id_key,name_key

Ex: (12, "Kindness");

And last I have a table where I only have a foreign key:

key_users: id_keyu,value_keyu (foreign key from keyfix), user_create_keyu (foreign key from Contributor), user_recieve_keyu(foreign key from Contributor).

Ex: (1, 12, 5, 6);

In the query I needed to bring the name of the key (in the example "Gentileza") the name of the collaborator who receives and what is delivering but when I run this query does not return me anything:

SELECT id_keyu,name_key,nome_colab FROM keys_users,keyfix,colaborador WHERE id_key = value_keyu AND id_colab =  user_recieve_keyu AND id_colab = user_create_keyu;

Answer 1

From what I saw of your query, you search by the field "id_key" (comparing with the field "value_keyu") and by the field "id_colab" (comparing with the fields "user_recieve_keyu" and "user_create_keyu"). It may be that in this search for "id_colab" is the problem, since you want the value to exist in the two fields, I imagine that what you want is that the value exists in one of the fields.

Try to make the following query:

SELECT id_keyu,name_key,nome_colab FROM keys_users,keyfix,colaborador WHERE id_key = value_keyu AND (id_colab =  user_recieve_keyu OR id_colab = user_create_keyu)

Answer 2

Well I want to thank everyone who was willing to help.

I decided after a help from my brother and had to use two subquery, may not be the fastest but it works.

Here are the solutions:

select k.name_key,(select c.nome_colab from colaboradores c where ku.user_create_keyu=c.id_colab) as criador,(select c.nome_colab from colaboradores c where ku.user_recieve_keyu=c.id_colab) as recebe from keys_users ku join keyfix k on ku.value_keyu=k.id_key;

Edit: My Database teacher gave me a much more optimized solution.

SELECT id_keyu,name_key,c1.nome_colab , c2.nome_colab
FROM keys_users,keyfix,colaborador c1 , colaborador c2
WHERE id_key = value_keyu AND c1.id_colab =  user_recieve_keyu AND c2.id_colab = user_create_keyu;