I'm trying to recover two fields from a subquery but I do not give with the key to show them to me.
this is the code I have:
SELECT
*,
art.id AS IdArt,
art.nombre AS NombrArt,
conf.Conf1 AS Sexo
FROM 'Articulos' art
INNER JOIN 'Configuraciones' conf ON art.sexo = conf.Conf_id
WHERE UPPER(CONCAT(art.nombre,art.temporada)) LIKE UPPER('%".$buski."%') AND 'borrado' = 0
OR art.id in (
SELECT ean.idcosa
FROM 'Direcciones&Codigos' ean
WHERE ean.'3' LIKE '%".$buski."%' OR ean.'4' LIKE UPPER('%".$buski."%')
)
ORDER BY art.temporada DESC, art.material DESC;
the data that I need to take out is in the sub-directory of Addresses & code and the fields are called 1 and 2.
the array took it out like this:
$R_auto_Articulos = mysqli_query($Conectar, $C_auto_Articulos);
if ($R_auto_Articulos && mysqli_num_rows($R_auto_Articulos)) {
while($row = mysqli_fetch_array($R_auto_Articulos, MYSQLI_ASSOC)) {
I tried to create an alias in the subquery with this: ean.2 AS Eanta and then retrieve it with $ row ["Eanta"] but at the moment I put the alias it stops returning the query results: (
Is there any way to get the value of fields 1 and 2 in some way?
The structure of the articles table is:
id: int
nombre: varchar
sexo: int
precio: varchar
temporada: int
borrado: int
The structure of the Address & Code tables is:
id: int
incosa: int (id de articulo)
1: int (talla)
2: int (color)
3: int (código ean)
4: int (código de uso interno)
the union of the two tables is done by matching the id of articles with the table of addresses & codes.
Really with the subquery if I look for the code ean appears, if I look for the name of the article it appears, if I look for the code of internal use it appears ... which makes me great for lack of being able to recover from that table the size and the color.
Before I had a query with an inner to that table but it showed me duplicate results from the Articles table since each ean code is assigned to a Cartesian product created from the name of the article + size + color. with what in the table addresses and codes there are of the same article as 20 references. As the array threw me the list of 20 references I opted to make the subquery.
The code I had before is this:
SELECT
*,
art.id AS IdArt,
art.nombre AS NombrArt,
conf.Conf1 AS Sexo,
ean.id AS IdEan
FROM 'Articulos' art
INNER JOIN 'Direcciones&Codigos' ean ON art.id = ean.idcosa
INNER JOIN 'Configuraciones' conf ON art.sexo = conf.Conf_id
WHERE UPPER(CONCAT(art.nombre,art.temporada)) LIKE UPPER('%".$buski."%') OR ean.'3' = '".$buski."' OR ean.'4' LIKE UPPER('%".$buski."%') AND 'borrado' = 0
ORDER BY art.temporada DESC, art.material DESC
In the table addresses & codes I save the sizes and colors of each article with which it is seen that with this query it deals with each id of addresses & codes as one more record of Articles and I really want that in the array I only get the result of the records that are in articles.
I do not know if there will be any way of filtering with this code that only shows one result per id of the articles table, if that was the case, it would also be worth my solution.
Thank you very much everyone for your help ...