Convert a Query with a Subquery to a Query with a Join

Asked

Viewed 104 times

0

I’m trying to optimize a Query that the system I’m working on makes it have a SubQuery inside, only this SubQuery is referencing another table that has relation to the main table and by what I’ve been researching maybe if the Query used the Join instead of a SubQuery would be faster, the Query is this:

SELECT COUNT(v.id_voto) FROM radar.rad_voto AS v
    WHERE (
        SELECT COUNT(vi.id_votoitem) FROM radar.rad_votoitem AS vi
            WHERE (
                vi.id_alternativa IN (1068, 1061)
            ) AND vi.id_voto = v.id_voto
    ) = 2;

My attempt to convert to Join glitch:

SELECT count(*) FROM radar.rad_voto AS v
    JOIN radar.rad_votoitem AS vi ON vi.id_voto = v.id_voto
        WHERE (
            (vi.id_alternativa IN (1068, 1061)) AND vi.id_voto = v.id_voto
        );

The schematic of the two tables is this: Tabela 1 Tabela 2

Someone could give me a light?

(Note: I don’t have much experience in SQL, I know the basics to survive)

Edit 1: What I want to do is count the number of times that on the table rad_votoitem has two lines with two id_alternativa different (with value I will specify, those there are merely to exemplify) and these two lines have the same id_voto

sql database postgresql query optimizing

  • Not much time to explore, but I think you can use the clase HAVING SQL to make this filter.

  • Explain better what you want to tell. At least this: AND vi.id_voto = v.id_voto you don’t need because you are already in the joining condition.

  • @anonimo I am wanting to count the number of times that in the table rad_votoitem has two lines with two id_alternativa different (with value that I will quote, I used those there merely to exemplify) and these two lines have the same id_voto

  • @Jeffersonquesado how could I use to get my desired result? Can you illustrate?

  • But you need some more data from the rad_vote table or the count/selection is all done in the rad_votoitem table?

  • @anonimo I don’t know if I understand your question but what I want is just count how many times q in the table rad_votoitem has two lines with id_alternativa distinct and id_voto equal

Show 1 more comment

2 answers

1

If you only need the data of rad_votoitem try:

SELECT count(DISTINCT id_voto) FROM rad_votoitem
WHERE id_alternativa IN (1068, 1061)
GROUP BY id_voto
HAVING count(id_voto) > 1;

  • Opa mano @anonimo, first thank you so much for your reply, your SQL worked in parts, his problem is that he is returning me a column ("Count") with the number of rows being the result of the vote count (that’s what I want) and each row with the number "1", I realized this by taking this data as a list/array of numbers and returning the size of this list/array, it worked only that it would have a way to return the size of this list directly by SQL?

0

Buddy, good afternoon,

select
    count(v.id_voto)
from rad_voto v
    left join
rad_votoitem vi on v.id_voto = vi.id_voto
where vi.id_alternativa IN (1068, 1061)
group by vi.id_alternativa
having count(v.id_voto) = 2

I believe this would suit you. But be careful with HAVING, because it is processed after the return of the consultation. It works as if it were a last filter, after everything has been filtered initially.

  • Opa amigo @Pauloafonsolimagatti, first vlw by the answer, unfortunately this SQL did not work, in fact it did not return anything, did not understand the pq but it was as if it had returned null or something like that

  • as I said in a comment, what I’m looking to do is count the number of times that on the table rad_votoitem has two lines with two id_alternativa different (with value I will specify, I used those there are merely to exemplify) and these two lines have the same id_voto

Browser other questions tagged sql database postgresql query optimizing

You are not signed in. Login or sign up in order to post.