Query Query 1: N

Question

Query Query 1: N

Navigation

#1 by (2 votes)
#2 by (0 votes)

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 attributes of name X and name Y linked to Z stock. And I wonder if there is any way to do this without doing 2 INNER JOIN . Currently I do the following form but I believe 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 just and inner join, but if you wanted to know by 5 would you have to do 5 joins? Does anyone know of any simpler way to perform this type of query? There must be the 2 attributes associated with the stock, it is not necessary to have only X or only Y associated, there must be 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

asked by anonymous 03.06.2015 / 15:41

2 answers

How to always keep 1 record in DB after inserting with entityframework? How to reference a DLL using DllImport and a variable in the path?

score 2 · Answer 1

Do you insist that the query be an "e" and not an "or"? If yes, the only way is to INNER JOIN per attribute same.

If you want to make it clear that id_estoque is the key that links the two tables, you can use INNER JOIN <tabela> USING (<coluna>, <coluna>, …) ; your query 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 the WHERE, so I could leave only the keys in USING)

One thing strikes me: does the same attribute apply to more than one stock? If so, your design has a problem - at the time someone adds an attribute but spells the name incorrectly, it will not appear when the person searches again for the right written attribute accent, upper / lower case, ...). The solution is to use three tables:

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

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

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

There the query would look 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 < a href="https://en.wikipedia.org/wiki/Second_normal_form"> second normal form )

score 0 · Answer 2

0

I think it would work like this:

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'

03.06.2015 / 15:47