List:General Discussion« Previous MessageNext Message »
From:Chris Knipe Date:January 12 2003 4:05pm
Subject:Re: unique select between two tables...
View as plain text  
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" <savage@stripped>
To: <mysql@stripped>
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 <mysql-thread129687@stripped>
> To unsubscribe, e-mail
<mysql-unsubscribe-savage=savage.za.org@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>

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