List:General Discussion« Previous MessageNext Message »
From:Chris Knipe Date:November 2 2002 6:56pm
Subject:merging data with a query
View as plain text  
Lo everyone,

mysql> DESCRIBE UserAttributes;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| ID        | int(11) unsigned |      | PRI | NULL    | auto_increment |
| UserID    | int(11)          |      | MUL | 0       |                |
| Attribute | varchar(32)      |      |     |         |                |
| Value     | varchar(253)     |      |     |         |                |
| op        | char(2)          | YES  |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM UserAttributes;
+----+--------+---------------+------------+------+
| ID | UserID | Attribute     | Value      | op   |
+----+--------+---------------+------------+------+
|  2 |      1 | User-Password | fl4shp1x13 | =    |
|  3 |      1 | Auth-Type     | PAP        | =    |
+----+--------+---------------+------------+------+
2 rows in set (0.00 sec)

mysql> DESCRIBE UserAccounts;
+---------------+------------------------------+------+-----+------------+--
--------------+
| Field         | Type                         | Null | Key | Default    |
Extra          |
+---------------+------------------------------+------+-----+------------+--
--------------+
| UserID        | tinyint(4) unsigned          |      | PRI | NULL       |
auto_increment |
| AccessLevel   | enum('1','2','3','4')        |      |     | 1          |
|
| ServiceID     | tinyint(4)                   |      | MUL | 0          |
|
| TokenUsage    | tinyint(4)                   |      |     | 1          |
|
| ZoneID        | int(11)                      |      |     | 1          |
|
| StatusID      | int(11)                      |      | MUL | 0          |
|
| RealmID       | int(11)                      |      | MUL | 1          |
|
| DebtCode      | varchar(6)                   |      | UNI |            |
|
| FullName      | varchar(250)                 |      |     |            |
|
| IDNumber      | varchar(15)                  |      | MUL |            |
|
| Language      | enum('Eng','Afr')            |      |     | Eng        |
|
| FirstTimeUser | enum('n','y')                |      |     | n          |
|
| BusinessOwner | enum('n','y')                |      |     | n          |
|
| BusinessName  | varchar(250)                 | YES  |     | NULL       |
|
| BusinessSize  | tinyint(2) unsigned          |      |     | 0          |
|
| ServiceUsage  | enum('Home','Work','Family') |      |     | Home       |
|
| HasChildren   | enum('n','y')                |      |     | n          |
|
| Username      | varchar(10)                  |      | UNI |            |
|
| Password      | varchar(16)                  |      |     |            |
|
| WebSpace      | tinyint(4)                   |      |     | 15         |
|
| Balance       | double(6,2)                  |      |     | 0.00       |
|
| PayDate       | date                         |      |     | 0000-00-00 |
|
| ReferredBy    | varchar(250)                 |      |     |            |
|
+---------------+------------------------------+------+-----+------------+--
--------------+
23 rows in set (0.01 sec)

mysql> SELECT * FROM UserAccounts WHERE UserID='1';
+--------+-------------+-----------+------------+--------+----------+-------
--+----------+-------------+---------------+----------+---------------+-----
----------+--------------+--------------+--------------+-------------+------
----+------------------+----------+---------+------------+------------+
| UserID | AccessLevel | ServiceID | TokenUsage | ZoneID | StatusID |
RealmID | DebtCode | FullName    | IDNumber      | Language | FirstTimeUser
| BusinessOwner | BusinessName | BusinessSize | ServiceUsage | HasChildren |
Username | Password         | WebSpace | Balance | PayDate    | ReferredBy |
+--------+-------------+-----------+------------+--------+----------+-------
--+----------+-------------+---------------+----------+---------------+-----
----------+--------------+--------------+--------------+-------------+------
----+------------------+----------+---------+------------+------------+
|      1 | 4           |         3 |          4 |      1 |        5 |
1 | CHR001   | Chris Knipe | 8006205055089 | Eng      | n             | n
| NULL         |            0 | Home         | n           | cknipe   |
2bf765c82587544a |       15 |    0.00 | 2002-09-27 |            |
+--------+-------------+-----------+------------+--------+----------+-------
--+----------+-------------+---------------+----------+---------------+-----
----------+--------------+--------------+--------------+-------------+------
----+------------------+----------+---------+------------+------------+
1 row in set (0.00 sec)

And the query in question...

  SELECT UserAccounts.UserID,
         UserAccounts.Username,
         UserAttributes.Attribute,
         UserAttributes.Value,
         UserAttributes.op
    FROM UserAccounts
    LEFT JOIN UserAttributes ON UserAccounts.UserID=UserAttributes.UserID
   RIGHT JOIN RadiusRealms ON UserAccounts.RealmID=RadiusRealms.RealmID
   WHERE UserAccounts.Username = LEFT('cknipe@stripped', (LOCATE('@',
'cknipe@stripped') -1)) AND
         UserAccounts.StatusID < 10 AND
         RadiusRealms.RealmName = SUBSTRING('cknipe@stripped',
(LOCATE('@', 'cknipe@stripped') +1)) AND
         RadiusRealms.RealmActive='1'
ORDER BY UserAccounts.UserID

Will it be possible to select the password from the UserAccounts table and
replace the with the password in UserAttributes with that, but still return
all the Attributes from the UserAttributes table?  The UserAttributes table
can have a whole bunch of rows returned setting various settings on the
Radius Authentication, now, I want all these attributes to be returned (they
must be), but I want to make a except of User-Password which value must come
from the UserAccounts table...

Is this possible at all?

--
me



Thread
merging data with a queryChris Knipe2 Nov