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

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