Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #15561 > unrolled thread

sql with 3 tables

Back to article view | Back to comp.lang.php

Contents

  sql with 3 tables alex <alex@nomailno.it> - 2015-07-05 11:49 +0200
    Re: sql with 3 tables adam <adam@nospam.net> - 2015-07-05 10:14 +0000
      Re: sql with 3 tables alex <alex@nomailno.it> - 2015-07-05 12:36 +0200
        Re: sql with 3 tables Luuk <luuk@invalid.lan> - 2015-07-05 19:06 +0200
      Re: sql with 3 tables alex <alex@nomailno.it> - 2015-07-05 16:41 +0200
        Re: sql with 3 tables Jerry Stuckle <jstucklex@attglobal.net> - 2015-07-05 11:18 -0400
        Re: sql with 3 tables adam <adam@nospam.net> - 2015-07-05 17:14 +0000
        Re: sql with 3 tables adam <adam@nospam.net> - 2015-07-05 17:28 +0000
          Re: sql with 3 tables alex <alex@nomailno.it> - 2015-07-14 11:07 +0200
        Re: sql with 3 tables Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2015-07-06 03:28 +0200
      Re: sql with 3 tables alex <alex@nomailno.it> - 2015-07-05 18:07 +0200

#15561 — sql with 3 tables

in this condition between two tables A and C

UPDATE  A AS A
INNER JOIN C AS C
ON      A.id = C.id
SET     A.totale = A.aa + C.bb

I must to add another condition
respect at one table B


for to have this
UPDATE  A AS A
INNER JOIN C AS C
ON      A.id = C.id
SET     A.totale = A.aa + C.bb
---> WHERE (B.x < C.y WHERE B.id=C.id) <--



how?
thank

[toc] | [next] | [standalone]

#15562

On 2015-07-05, alex <alex@nomailno.it> wrote:
> in this condition between two tables A and C
> [...]
> I must to add another condition
> respect at one table B
Something like this perhaps?

UPDATE
    a
SET
    a.totale = a.aa + c.bb
FROM
    a AS a,
    b AS b,
    c AS c
WHERE
    a.id = c.id
    AND c.id = b.id
    AND b.x < c.y

[toc] | [prev] | [next] | [standalone]

#15563

 > UPDATE
 >      a
 > SET
 >      a.totale = a.aa + c.bb
 > FROM
 >      a AS a,
 >      b AS b,
 >      c AS c
 > WHERE
 >      a.id = c.id
 >      AND c.id = b.id
 >      AND b.x < c.y
 >


I must to have this (semplified code)
UPDATE  A AS A
INNER JOIN C AS C
ON      A.id = C.id
SET     A.tot = 1
WHERE (B.x < C.y  WHERE B.id=C.id) <--- to solve

from 3 tables,
on  A to change the column tot depending comparision between B and C

A           C                  B
id tot      id  maxacquist     id acquist
10 0        8      10           8   3
9  0        6     null          6   5
8  0        5      6            5   6
7  0        4      1            4   2
6  0
5  0
4  0
3  0
2  0
1  0


after comaprision B and C
id  C  >  B        insert this value in A
8  10     3              0
6  null   5              0
5  6      6              1
4  1      2              1


notice that in column A there are records that there aren't in B and C

think your suggest is ok

[toc] | [prev] | [next] | [standalone]

#15567

On 5-7-2015 12:36, alex wrote:

>
> notice that in column A there are records that there aren't in B and C
>

If you use 'INNER JOIN ON A.id = C.id',
the result will be that you only have values that are in table a AND in 
table B,
values that are only in table A, but not in table C are no longe in your 
output.

To get the values that are in Table A, but not in Table C, you will have 
to do:
LEFT JOIN ON A.id = C.id

But, you seem to be updating Table A, based on values in B or C. So my 
conclusion will be that values in Table A, which are not in Table B or 
in Table C will not be updated.

[toc] | [prev] | [next] | [standalone]

#15564

> UPDATE
>      a
> SET
>      a.totale = a.aa + c.bb
> FROM
>      a AS a,
>      b AS b,
>      c AS c
> WHERE
>      a.id = c.id
>      AND c.id = b.id
>      AND b.x < c.y
>


I tested but not work syntax error

also this (more simple not work for syntass error)
UPDATE a
SET a.total = 33
FROM  tabA AS a, tabC AS c
WHERE a.id = c.id




warning: syntax error in the query SQL near at 'FROM tabA AS a, tabC AS 
c WHERE a.id = c.id' line 3 query: UPDATE a SET a.total = 33 FROM tabA 
AS a, tabC AS c WHERE a.id = c.id

[toc] | [prev] | [next] | [standalone]

#15565

On 7/5/2015 10:41 AM, alex wrote:
>> UPDATE
>>      a
>> SET
>>      a.totale = a.aa + c.bb
>> FROM
>>      a AS a,
>>      b AS b,
>>      c AS c
>> WHERE
>>      a.id = c.id
>>      AND c.id = b.id
>>      AND b.x < c.y
>>
> 
> 
<snip>

This is not a PHP issue - it is a database one.  You will get much
better help if you ask in a newsgroup related to the database you are using.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex@attglobal.net
==================

[toc] | [prev] | [next] | [standalone]

#15568

On 2015-07-05, alex <alex@nomailno.it> wrote:
> I tested but not work syntax error
:)

What database is it?

A.

[toc] | [prev] | [next] | [standalone]

#15569

On 2015-07-05, alex <alex@nomailno.it> wrote:
> I tested but not work syntax error
So I'm guessing it's a MySQL then, try below statement.

UPDATE
    a AS a
INNER JOIN
    c AS c
    ON (c.id = a.ic)
INNER JOIN
    b AS b
    ON (b.id = c.id AND b.x < c.y)
SET
    a.totale = a.aa + c.bb

[toc] | [prev] | [next] | [standalone]

#15583

Il 05/07/2015 19:28, adam ha scritto:
> UPDATE
>      a AS a
> INNER JOIN
>      c AS c
>      ON (c.id = a.ic)
> INNER JOIN
>      b AS b
>      ON (b.id = c.id AND b.x < c.y)
> SET
>      a.totale = a.aa + c.bb

hello
your soution is this
UPDATE a AS a
INNER JOIN c AS c  ON (c.id = a.ic)
INNER JOIN b AS b  ON (b.id = c.id AND b.x < c.y)
SET  a.totale = a.aa + c.bb


I founded and tested and work, this
UPDATE a AS a
INNER JOIN c AS c  ON a.id = c.ic
INNER JOIN b AS b  ON c.id = b.id
SET  a.totale = a.aa + c.bb
WHERE b.x < c.y


your solution is the same?
is more performance seen that the where you use directky in the join?

is always better use after ON () the brackets?


thank

[toc] | [prev] | [next] | [standalone]

#15570

alex wrote:

> UPDATE a
> SET a.total = 33
> FROM  tabA AS a, tabC AS c
> WHERE a.id = c.id
> 
> warning: syntax error in the query SQL near at 'FROM tabA AS a, tabC AS
> c WHERE a.id = c.id' line 3 query: UPDATE a SET a.total = 33 FROM tabA
> AS a, tabC AS c WHERE a.id = c.id

RTFM.  But if your English was better, you would already realize that the 
sentence “UPDATE … *FROM* …” does not make sense (SQL is intentionally 
designed to resemble English).

It should be obvious that (My)SQL as such is off-topic here in 
comp.lang.*php*.

You also want to get a real name and an e-mail address.

-- 
PointedEars
Zend Certified PHP Engineer
Twitter: @PointedEars2
Please do not cc me. / Bitte keine Kopien per E-Mail.

[toc] | [prev] | [next] | [standalone]

#15566

> UPDATE
>      a
> SET
>      a.totale = a.aa + c.bb
> FROM
>      a AS a,
>      b AS b,
>      c AS c
> WHERE
>      a.id = c.id
>      AND c.id = b.id
>      AND b.x < c.y
>

with double inner join is the solution?
so I haven't error
but must verified the result

UPDATE  a AS a
             INNER JOIN c AS c
             ON      a.id = c.id
             INNER JOIN b AS b
             ON      c.id = b.id
             SET     a.total = 33
             WHERE b.x < c.y

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.php

csiph-web