You can use two columns of the same table in a WHERE

Asked

Viewed 704 times

1

In a select I can use in Where to filter the results, two columns of the same table?

Ex:

Select id, idpai, nome FROM tabela Where id=idpai
php mysql

  • but how will you know the value? rsrsrsr

  • I thought about it too! But I’m learning! So I wanted to be sure! Have a ninja know every trick! Maybe there was one!

  • No need for another select in this case yours and complicated, since you do not know what you are going to get. Got confused what you want. And your query you used for explanation will resume what you asked.

  • 2

    I also didn’t quite understand what you meant. I thought you wanted something like this: select * from tabela_a where id in ( select id from tabela_b ) That’s totally correct. That’s not it?

  • 2

    I did not understand either the question or the answer accepted, because they talk about two very different things. It is perfectly possible to use all columns in select itself. SELECT id,nome,idpai WHERE id=idpai, using two columns of select itself, no? Another example, with two different tables in the same select: SELECT idbatata FROM batata, idbanana FROM banana WHERE idbanana=idbatata

  • I had accepted the question because the comments implied that it was not possible to do what you put in the comment! That’s really what I wanted to know! If it was possible to compare two columns of the same table! In fact the question is badly asked!

  • @Wouldn’t ivanveloso have a practical case, with example data? It is easier for you to explain in the question with the actual structure of the banks involved, and what you want to do in practice. Many times by simplifying the example you end up taking parts that would be fundamental to solve your problem. Try posting the real case you want to solve, not an example. I only made a counterpoint, but I can elaborate the question better explaining in more detail.

  • If you do something like Select id, idpai, nome FROM tabela Where id=idpai, you will take all lines that the ID is equal to the IDPAI, or something like "idpai: 200 and id: 200", is that what you want? Could you give an example of the final desired result? For this is the point to understand your need.

Show 3 more comments

3 answers

2

Have you answered your own question.

would have some way where at the time the select run I take the value of a specific column and already apply direct mo WHERE?

"At the time you select to rotate it"... The select will try to rotate the where but what you really need is to run another select applying the where according to the return of this first.

If the solution you seek is to return only fields that fall into a set of values, then you can do the select using the keyword IN.

select ID, valor 
from tabela 
where valor in (8902, 3789, 8452, 7172, 1239);

If you still "need" to perform this query, you are learning the concept a little wrong. In SQL, it is common to return Rows, in accordance with values inside Columns.

Anyway, I hope I helped.

  • No! No need! I just wanted to know if it was possible! , which by your answer and by the comments was obvious that it is not! I thank you!

0

You can generate a sub query. An example:

SELECT ID, valor FROM tabela WHERE valor IN (SELECT valor FROM tabela2 WHERE valor = [parametro]);

The sub query should return only one column, which will be used as a filter in the main column. In the sub query I put again the field/column value, but could be another, the caveat is with the return of the sub query, should only return a single column.

0

Yes, in general there is how to effect the relationship with the same table but if you have to do this, review the data structure because there is something wrong.

You can use a JOIN in the same table as follows:

SELECT 
t1.id as id_categoria_pai,
t1.nome as nome_categoria_pai,
t2.id,
t2.nome
FROM teste t1
LEFT JOIN teste t2 ON t2.id = t1.idpai

See working on sqlfiddle

  • There is no need to do this! My question aims to learn about. Just!

  • You know! It was a question! I had to consider her!

  • No problem, @ivanveloso.

Browser other questions tagged php mysql

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