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('[EMAIL PROTECTED]', (LOCATE('@',
'[EMAIL PROTECTED]') -1)) AND
UserAccounts.StatusID < 10 AND
RadiusRealms.RealmName = SUBSTRING('[EMAIL PROTECTED]',
(LOCATE('@', '[EMAIL PROTECTED]') +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
---------------------------------------------------------------------
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
