List:General Discussion« Previous MessageNext Message »
From:Chris Knipe Date:January 12 2003 3:19pm
Subject:unique select between two tables...
View as plain text  
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

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
| 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!!



unique select between two tables...Chris Knipe12 Jan
  • Re: unique select between two tables...Chris Knipe12 Jan