Re: Foreign key on multiple columns

2013-03-21 Thread Abhishek Choudhary 
      CREATE TABLE test2 (
        ID INT NOT NULL AUTO_INCREMENT,
        col1 INT NOT NULL,
        col2 INT NOT NULL,
        PRIMARY KEY (ID),
        CONSTRAINT fk FOREIGN KEY (col1, col2)
                      REFERENCES test1(ID, ID)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
      ) ENGINE=InnoDB;


i think error is because of referencing the same column in test1 table (ID,ID) .
try to change the column name  then run the code hope ur problem will solve out 
.

Abhishek choudhary,
www.tech4urhelp.blogspot.com




- Original Message -
From: Peter Brawley peter.braw...@earthlink.net
To: mysql@lists.mysql.com
Cc: 
Sent: Thursday, 21 March 2013 8:14 PM
Subject: Re: Foreign key on multiple columns

On 2013-03-21 8:12 AM, Norah Jones wrote:
 I'm trying to create a foreign key on two columns but getting error...

 Here's what I tried:

      CREATE TABLE test2 (
        ID INT NOT NULL AUTO_INCREMENT,
        col1 INT NOT NULL,
        col2 INT NOT NULL,
        PRIMARY KEY (ID),
        CONSTRAINT fk FOREIGN KEY (col1, col2)
                      REFERENCES test1(ID, ID)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
      ) ENGINE=InnoDB;

 But I get `ERROR 1005 (HY000): Can't create table 'DB.test2' (errno: 150)`

Dupe reference column.

PB




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql

Fw: Simple Query Question

2012-04-14 Thread Abhishek Choudhary 


Hi ,
count() function is a group function so use group by clause  in your select 
statement if you are selecting more than one column data..

here is some sample query related to help you,

select count(*)   from trndisburse:

output:1467

select count(*),pkdisburseid from trndisburse_TMP m Group by
 pkdisburseid;

output:
COUNT(*)PKDISBURSEID

100011120414090807001226
100011120414090807001228
100011120414090807001246
100011120414090807001252
100011120414090807001173
100011120414090807001187
100011120414090807001230
100011120414090807000859
10001112041409080742
10001112041409080751
10001112041409080797
100011120414090807001309
100011120414090807001314
100011120414090807001333
100011120414090807001290
10001112041409
080701
..
..some more  data...

last equivlent to your  problem:

select count(pkdisburseid) from trndisburse_TMP m
where grossamt=6000
  Group by pkdisburseid;

select count(pkdisburseid),grossamt from trndisburse_TMP m
where grossamt=6000
  Group by pkdisburseid,grossamt;

Thanks ,
abhisehk choudhary
www.tech4urhelp.blogspot.com



 From: Stefan Kuhn stef...@web.de
To: mysql@lists.mysql.com 
Sent: Saturday, 14 April 2012 4:51 PM
Subject: Re: Simple Query Question
 
On Saturday 14 April 2012 09:51:11 Willy Mularto wrote:
 Hi,
 Please help what is wrong with this simple query SELECT COUNT(key_agent)
 total FROM agents_consolidated  WHERE total = 180 Thanks.
You need to use having instead of where, see the documentation.
Stefan



 Willy Mularto
 F300HD+MR18DE (NLC1725)



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql