Hello thanks for your help
But I have the same problem with the SELECT that doesn't do what I need.
And I need SELECT all values of factura_id from the table factura with the
cliente_id = 1 and it doesn't have the value "total" of valor_liquido on the
table recibo_conteudo.
-----Original Message-----
From: SGreen@stripped [mailto:SGreen@stripped]
Sent: segunda-feira, 27 de Setembro de 2004 16:21
To: Andre
Cc: mysql@stripped
Subject: Re: SQL Problems
If I rewrite your query to use explicit INNER JOIN statements and not the
commas you used in your original query, I get:
SELECT t1.factura_id
FROM factura t1
INNER JOIN factura_conteudo t2
ON t1.factura_id = t2.factura_id
INNER JOIN recibo_conteudo t4
ON t4.documento_id <> t1.factura_id
AND t4.valor_liquidado = "total"
WHERE t1.cliente_id = 1
GROUP BY t1.factura_numero
LIMIT 0, 30;
This means that there are going to be LOTS of duplicate records created
between tables factura and recibo_conteudo. Look at the ON conditions for
the t4 JOIN...
INNER JOIN recibo_conteudo t4
ON t4.documento_id <> t1.factura_id
AND t4.valor_liquidado = "total"
the <> means that for each combination of records from
(factura+factura_conteudo) there could be MANY MANY records in
recibo_conteudo that meet those conditions. This is probably not what you
had in mind. Double-check your logic on that JOIN...
To exclude results where t1.factura_id is either 1 or 49 you can add this
piece to your WHERE clause
AND t1.factura_id NOT IN (1,49)
but I suspect that you were trying to do that with your join conditions.
Again, I come back to checking your logic.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Andre " <andre_caridade@stripped> wrote on 09/27/2004 10:54:37 AM:
> Hello I need some help on this SQL.
>
> SELECT t1.factura_id
> FROM factura t1, factura_conteudo t2, recibo_conteudo t4 WHERE
t1.cliente_id
> = 1 AND t1.factura_id = t2.factura_id AND ( t4.documento_id <>
t1.factura_id
> AND t4.valor_liquidado = "total" ) GROUP BY t1.factura_numero LIMIT 0,
30;
>
> This SQL do this result:
> factura_id
> 1
> 2
> 49
> 50
> 51
> 52
> 53
> 54
> 55
> 56
>
> And the result I need is this
>
> factura_id
> 2
> 50
> 51
> 52
> 53
> 54
> 55
> 56
>
> How can I do the SQL
> And I want the factura_id => 1 and 49 don't appear because they have
the
> value 'total'
>
