Query Query 1:N

Asked

Viewed 64 times

1

I have the following tables:

Tabela: estoque
id_estoque
nome

Tabela: atributos
id_atributo
id_estoque
nome

And for example I need to know if there are 2 name attributes X and the name Y stocked Z. And I wonder if there’s any way to do that without doing 2 INNER JOIN. Currently I do the following way but I believe that there must be some simpler way that I do not know:

SELECT e.nome 
FROM estoque e 
INNER JOIN atributos a1 ON e.id_estoque = a1.id_estoque AND a1.nome = 'X'
INNER JOIN atributos a2 ON e.id_estoque = a2.id_estoque AND a2.nome = 'Y'
WHERE e.id_estoque = 'Z'

Because to know with 2 are only and Inner Join, but if I wanted to know for 5 would have to do 5 joins? Does anyone know any simpler way to perform this type of query? There must be the 2 attributes associated with the stock X or just Y associated, there must be the 2.

Data example:

Tabela: estoque
id_estoque|nome
'A'|'A'
'B'|'B'
'C'|'C'
'Z'|'Z'

Tabela: atributos
id_atributo|id_estoque|nome
'X'|'A'|'X'
'Y'|'A'|'Y'
'N'|'A'|'N'
'X'|'Z'|'X'
'Y'|'Z'|'Y'
'M'|'Z'|'M'
php mysql select

  • I believe it would work using WHERE a.nome like '%X' OR a.nome like '%Y'

  • does not serve because it is not to be or but to have the 2 attributes obligatorily associated to the stock

2 answers

2

Do you make a point that the query is an "and" and not an "or"? If so, the only way is to make one INNER JOIN by attribute even.

If you want to make it clear that id_stock is the key that links the two tables, you can use INNER JOIN <tabela> USING (<coluna>, <coluna>, …); your consultation would be

SELECT e.nome 
FROM estoque e 
     INNER JOIN atributos a1 USING (id_estoque)
     INNER JOIN atributos a2 USING (id_estoque)
WHERE e.id_estoque = 'Z' AND
      a1.nome = 'X' AND
      a2.nome = 'Y'

(i moved the conditions on the list name to WHERE, to be able to leave only the keys in USING)

One thing strikes me: does the same attribute apply to more than one stock? If yes, yours design has a problem - when someone adds an attribute but misspells the name, it will not appear when the person searches again for the right written attribute (or with accent/no accent, uppercase/lowercase, ...). The solution is to use three tables:

Tabela: estoque
-------
id_estoque
nome

Tabela: atributo
-------
id_atributo
nome

Tabela: atributo_estoque
-------
id_atributo
id_estoque

Then the consultation would be something like

SELECT e.nome 
FROM estoque e 
     INNER JOIN atributo_estoque a1 USING (id_estoque)
     INNER JOIN atributo_estoque a2 USING (id_estoque)
WHERE e.id_estoque = 'Z' AND
      a1.id_estoque = (SELECT id_estoque FROM atributo WHERE nome = 'X') AND
      a2.id_estoque = (SELECT id_estoque FROM atributo WHERE nome = 'Y')

(speaking in terms of data normalization, This puts the tables in the second normal form)

  • In my bank are already these 3 tables, I only used it here for example. But then there is no other way but INNER JOIN? Yes, I want it to be AND

  • If you were using a database system other than Mysql, you could query with INTERSECT, cf. Postgresql documentation. You are concerned about the performance of the consultation?

  • With performance too, but the amount of Join with the indexed bank using foreign key, I know that is not what will worsen the performance of the query, but I thought there was something simpler, with better performance or equal to this. Because I come across the problem of sometimes having to build 5 INNER JOIN, this is cost to php, if there was something simpler would be better.

  • Look at the table on this article by Peter Norvig — the time that Mysql will need to pull your table on disk (8000000 ns) is much longer than the time that PHP will take to concatenate the strings in memory (1000 ms if PHP is too dirty); the time that PHP will take is simply not measurable. If one day your app has the size of Facebook, then yes you will need to think about it (but then you will have become at least multi-millionaire in the process. :))

  • If there is no other way I will continue like this, it was more curious even if there was another way, for performance sake I knew it was good.

0

I believe it would work that way:

SELECT e.nome 
FROM estoque e 
INNER JOIN atributos a ON e.id_estoque = a.id_estoque
WHERE e.id_estoque = 'Z' AND a.nome like '%X' AND a.nome like '%Y'

  • does not serve because it is not to be or but to have the 2 attributes obligatorily associated to the stock

  • Post some examples of data you would have in the two tables and an example that would satisfy your need, this logic is a bit confusing

  • I added in the question the examples

  • Right, now on your select what would be a valid return example?

  • Return only, the name of the stock that satisfies the rule, in case the Z, because the need is to know whether or not there is a bank record that satisfies this rule.

  • That is, in your example it would return the 'X' 'Y' and 'Z' records of the attributes table, correct?

  • a.nome like '%X' AND a.nome like '%Y' is a contradiction; this query will never return any line.

Show 2 more comments

Browser other questions tagged php mysql select

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