Posts by n_kip

have been helped, below is the script:

UPDATE tbl2, 
       (SELECT * 
        FROM   (SELECT tbl1.eid, 
                       tbl1.qnt, 
                       tbl3.tid, 
                       COUNT(DISTINCT tlid) cnt 
                FROM   tbl1, 
                       tbl3, 
                       tbl2 
                WHERE  tbl1.eid = tbl3.eid 
                       AND tbl2.eid = tbl3.eid 
                       AND pb = 1 
                       AND tbl2.eid NOT IN (SELECT tbl2.eid 
                                            FROM   tbl2 
                                            WHERE  fx = 1) 
                GROUP  BY tbl1.eid, 
                          tbl3.tid, 
                          tbl1.qnt) a 
        WHERE  cnt < qnt) b 
SET    so = 0 
WHERE  tbl2.tid = b.tid

Hi,
I have three tables as below.

tbl1

eid qnt   pb
12     3   1
13     1   1
23     3   1

tbl2

tid   eid  fx   so
1      12    0    1
2      13    0    1
3      23    1    1
4      23    0    1

tbl3

tlid   tid  eid  fx
1      1    12    0
2      1    12    0
3      2    13    0
4      3    23    1
5      4    23    0

Need an update query that counts the number of 'tlid' in 'tble3'
who's 'pb' in 'tb1' is '1'
who's 'eid' in 'tbl2' does not have '1' as one if it's 'fx'

and update 'so' in 'tbl2' to '0' if the total counted 'tlid' in 'tble3' is 'LESS' than the 'qnt' in 'tbl1'

The final result will be that 'so' for 'tid 1' in 'tbl2' will be '0'

The others will not get updated because:
The sum of 'tlid' in 'tbl3' for 'tid 2' or 'eid 13' in 'tble3' = 'qnt' in 'tbl1'
'tlid' for 'eid 23' in 'tbl3' will not be counted becuse 'eid 23' has '1' in one of the 'fx' in 'tbl2'

Appreciate you help
------------------------------------------------------------------

drop table if exists tbl1;
create table tbl1 (eid integer, qnt integer, pb integer);
insert into tbl1 values (2,3,1),(3,1,1),(23,3,1);

drop table if exists tbl2;
create table tbl2 (tid integer, eid integer, fx integer, so integer);
insert into tbl2 values (1,12,0,1),(2,13,0,1),(3,23,1,1),(4,23,0,1);

drop table if exists tbl3;
create table tbl3 (tlid integer, tid …

A BIG THANKS to you friend. I could not figure this out. You've helped me much. All that best,
Nich.

Now I see what you are talking about. I was under the assumption that the tid in tbl2 and tbl3 would do the join, but now I see that they don't connect.

Basically, at the end of the day I need the status for tid in tbl2 to be updated to 0 based on X-Y > 0

Is there another way of doing this? E.g. by adding another column?
E.g.
This script:

SELECT tbl1.eid, (tbl1.quantity - sum(tbl2.qnty)) AS X, tbl2.tid
FROM tbl1, tbl2
WHERE tbl1.eid = tbl2.eid
AND TYPE =1
GROUP BY eid

Produces:

eid X tid
3 1 1
4 2 3

And this script:

SELECT tbl2.tid, COUNT( tbl3.id ) AS Y, tbl2.eid
FROM tbl2, tbl3
WHERE tbl2.tid = tbl3.tid
AND tbl2.type =0
GROUP BY tbl2.tid

Produces:

tid Y eid
2 1 3
4 1 4

Could the eid be used to link the two?

Thanks.

Hi,
I though that the tid that is in tbl2 & tbl3 was for the join, and is the one that will be used to do the ultimate update of the status column in tbl2, of which I do not know how to do.
If I'm wrong, would you kindly advice?
I now see those mistakes.
Thanks.

Thank you for the post.

On finding X, add "GROUP BY eid", so that the script will be:

/* find x */
select tbl1.eid, (tbl1.quantity - sum(tbl2.qnty)) as X
from tbl1, tbl2
where tbl1.eid = tbl2.eid AND type = 1
GROUP BY eid;

And we'll have the needed results for X

The script for finding Y does bring the correct results

Now, the two queries will produce

X
+-----+---+
| eid | X |
+-----+---+
| 3 | 1 |
| 4 | 2 |
+-----+---+

Y
+-----+---+
| tid | Y |
+-----+---+
| 2 | 1 |
| 4 | 1 |
+-----+---+

What needs to happen is to subtract Y from X and the results should be

tid Y-X
2 0
4 1

Finally, update the status of tbl2 to 0 WHERE the result of Y-X > 0

In this case, the status in tbl2 for tid 4 will be updated to 0

All this needs to be in one script.

I appreciate you help.

Nich

Hi, I need some help with this MySQL Update

I've got three tables as below.

tbl1
id eid quantity
1 3 2
2 4 3

tbl2
tid eid qnty status type
1 3 1 1 1
2 3 0 1 0
3 4 1 1 1
4 4 0 1 0

tbl3
id tid type
1 1 1
2 2 0
3 3 1
4 4 0

I need an sql update to:

Find X

Find Y

Then Update status in tbl2 to 0 if X - Y > 0

X = tbl1.quantity - sum(tbl2.qnty) where tbl1.eid = tbl2.eid AND type = 1

In other words:

X = tbl1.quantity in that eid - sum(tbl2.qnty) in that eid WHERE type = 1

Thus X for eid 4 = 2 and X for eid 3 = 1

Y = SELECT COUNT(tbl3.id) FROM tbl3 WHERE tbl2.tid = tbl3.tid AND type = 0 GROUP BY tid

Thus tid 2 = 1 and tid 4 = 1

Update status in tble2 to 0 if X - Y > 0

The final result will be that the status for tbl2.tid:4 will be updated to 0 because 2 - 1 = 1

You contribution(s) are appreciated.

Hi,
If you mobile device supports PDF, then:

CF8 Ref:
http://livedocs.adobe.com/coldfusion/8/cf8_cfml_ref.pdf

CF8 Ref:
http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/coldfusion_9_cfmlref.pdf

Hi,
You'll find instructions in Coldfusions documentation:

Coldfusion 8
http://livedocs.adobe.com/coldfusion/8/htmldocs/UseSMSGateway_01.html

Coldfusion 9
http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec22c24-77f6.html

You may want to have a look at this:

http://stackoverflow.com/questions/3384625/must-one-use-an-mvc-framew-to-produce-clean-urls

You can add a check like this:

WHERE child_tbl2.xid NOT IN (SELECT xid FROM child_tbl2 WHERE ...)

Fill in the blanks.

Hi,
I'm not sure where this check should go, is it at the end of the query?

Hi,
There is a tutorial in CF6.1 documentation on how to do this.

http://livedocs.adobe.com/coldfusion/6.1/htmldocs/builda12.htm

All the best.

Greetings, I've got a query that I'm struggling with, this is the first time that I am encountering this type of query. I have two table as shown below.

xid is the primary key in parent_tbl1, while xid is the foreign key in child_tbl2

parent_tbl1

xid pub 
1    1    
2    1    
3    0    
4    1

child_tbl2

id ttype fno xid  qnty
1  A       0    1    0
2  A       1    1    3
3  B       1    1    4
4  A       1    2    1  
5  A       1    3    2
6  A       1    4    3
7  A       1    4    1
8  A       1    1    1

Below is the exlanation of the query in parts, which will then need to make up the whole query.

I need the SUM of qnty in child_tbl2:

1) Who's parent's pub is '1' Therefore, id 5 is eliminated from child_tbl2, this is because xid 3 is 0 in parent_tbl1

Results: child_tbl2

id ttype fno xid qnty
1  A       0    1    0
2  A       1    1    3
3  B       1    1    4
4  A       1    2    1
6  A       1    4    3
7  A       1    4    1
8  A       1    1    1

2) AND who's parent table has ttype 'A' in the child table Therefore, id 3 is eliminated from the existing results because id 3's ttype is B

Results: child_tbl2

id ttype fno xid qnty
1  A       0    1    0
2  A       1    1    3
4  A       1    2 …