OK, this just doesn't make sense to me.... mysql> SELECT COUNT(DISTINCT table1.DebtCode) AS table1, COUNT(DISTINCT table2.DebtCode) AS table2 FROM table1, table2 WHERE (table1.DebtCode LIKE 'CHR%' OR table2.DebtCode LIKE 'CHRI%'); +--------+--------+ | table1 | table2 | +--------+--------+ | 3 | 79 | +--------+--------+ 1 row in set (0.02 sec)
mysql> SELECT COUNT(DISTINCT DebtCode) AS table2 FROM table2 WHERE DebtCode LIKE 'CHR%'; +------+ | table2 | +------+ | 0 | +------+ 1 row in set (0.00 sec) -- me ----- Original Message ----- From: "Chris Knipe" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, January 12, 2003 5:19 PM Subject: unique select between two tables... > lo everyone, > > I have two tables.... Both have a DebtCode VARCHAR(6) in them, and both has > UNIQUE Indexes on them. How can I select DebtCode as being unique in both > tables? > > DebtCode in both tables, will be three alphabetical characters, followed by > three numbers, like ABC001 ... ABC999, etc etc etc. Now, let's say I want > to see the next available *UNIQUE* DebtCode I can assign, provided that I > have ABC002 in table1 already. > > mysql> SELECT COUNT(table1.DebtCode) + COUNT(table2.DebtCode) + 1 AS DebtID > FROM table1, table1 WHERE table1.DebtCode LIKE 'ABC%' OR table2.DebtCode > LIKE 'ABC%'; > +--------+ > | DebtID | > +--------+ > | 245 | > +--------+ > 1 row in set (0.01 sec) > > Which, is the total number of ROWS on Table1 + Table2 + 1. Alas, not what I > was expecting... I'm sure this should be possible, it's obviously my query > that is lacking... If there's any one with ideas / a quick fix, please let > me know!! > > -- > me > > <sql,query> > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
