Query in MySQL joining several tables [duplicated]

Question

Query in MySQL joining several tables [duplicated]

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (1 votes)

0

I'm trying to do a query in mysql in several tables at once but the results that I get do not end up serving me.

This is the code I'm using:

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;

Let's see if I can explain exactly what I need. There are 3 different tables. Articles: Where I keep the data of the article. Addresses & codes: Where I keep Ean codes, sizes and colors and reference. Configurations: Where I keep the names of things type Materials, sex, sizes, etc.

Right now, as the query is mounted, I get a mixture of Articles and Codes as results. As each article can have 3000 codes it takes me out of each article 3000 results.

The fact is that I just want to take the result of the article in question, but of course I have to look in directions & codes in case what I'm looking for is the reference or the ean code (which in that case if you take me out only one result). For example, if $ buski = belts that I take out all the results that as a name have belts, not that I take out all that are called belts and all their possible codes.

I have also tried to filter the results with a array_values(array_unique($DAutoCom)); but then I only get a single result.

What can I do so that the array only displays the results of the Articles table?

Let's say that I only have to eliminate all those in the articles table that have the duplicate id and only show one of them.

the loop I build with 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)) {

that the same thing to show only 1 result per id is done there.

mysql php

asked by Killpe 15.06.2017 в 19:52

source

3 answers

upload an image in PHP and save the result in database I do not understand why I think I already use this type of query and this gives me an error

score 1 · Answer 1

An alternative to show you only the data of the article, but that allows you to filter by ean is to make a sub query and apply the filter there.

SELECT 
*,
art.id AS IdArt,
art.nombre AS NombrArt,
conf.Conf1 AS Sexo
-- ean.id AS IdEan 
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 
AND 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;

Applying this will not show the following column:

-- ean.id AS IdEan

score 1 · Answer 2

try 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 
LEFT JOIN 'Configuraciones' conf ON art.id = 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;

score 1 · Answer 3

First, can you show me how you relate the tables? What do you want to do after obtaining the query from the 3 tables? Do you want to add an article in question?

I am more in favor of putting it in a list and adding with linq ex: lista.sum (e < = e.objetoasumar) .Tolist (); at first it costs but it is much more friendly and so you do your business by codebehind and not by sql.

Try 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 
LEFT JOIN 'Configuraciones' conf ON art.id = 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;