Hello.
Maybe this could help.
SELECT t1.Users_Code,
IF( t1.Affected_Member IS NULL, 'No entry in Table2',
IFNULL( t2.Member_Surname, t2.Member_Groupname)
) AS NAME
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.Affected_Member = t2.Member_Code;
+------------+--------------------+
| Users_Code | NAME |
+------------+--------------------+
| 1 | No entry in Table2 |
| 2 | Sur1 |
| 3 | Group1 |
| 4 | Sur2 |
+------------+--------------------+
Data in the tables:
mysql> select * from Table1;
+------------+--------------+-----------------+
| Users_Code | Users_action | Affected_Member |
+------------+--------------+-----------------+
| 1 | action1 | NULL |
| 2 | action2 | 1 |
| 3 | action3 | 2 |
| 4 | action4 | 3 |
+------------+--------------+-----------------+
mysql> select * from Table2;
+-------------+----------------+------------------+
| Member_code | Member_Surname | Member_Groupname |
+-------------+----------------+------------------+
| 1 | Sur1 | NULL |
| 2 | NULL | Group1 |
| 3 | Sur2 | NULL |
+-------------+----------------+------------------+
You can change LEFT JOIN to INNER JOIN if you don't want to process
NULL values in Affected_Member field.
"Martin Lancaster" <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> I am using MySQL 4.1.11nt
> I have two MyISAM tables:
>
> Table 1 holds usage information about users accessing various parts of =
> the
> database.
> Relevant fields are "Users_Code", "Users_action" and "Affected_Member".
>
> Table 2 holds personal information about the Member.
> Relevant fields are "Member_Code", "Member_Surname" and =
> "Member_Groupname"
>
> Table1.Affected_Member is the link to Table2.Member_Code.
> Table1.Affected_Member can be a null field.
>
> My application is coded so that if Table2.Member_Surname is null, =
> there
> will be a string value in Table2.Member_Groupname. The reverse is also =
> true.
> The application is also coded so that Table2.Member_Surname and
> Table2.Member_Groupname cannot both be null, nor both have a value.
>
> I am trying to code the following:
> 1. If Table1.Affected_Member is not null, then get =
> Table2.Member_Surname
> where Table1.Affected_Member =3d Table2.Member_Code, assigning this to =
> the
> output of the query as Member_Name.
>
> 2. If Member_Name is not a string then let Member_Name =3d
> Table2.Member_GroupName
>
> This will give the result that if Table1.Affected_Member is not null, =
> then
> the returned value of Member_Name will be either the Surname of the =
> Member
> or the Groupname of the Member.
>
> Although I am having success with coding part 1. of the query, I =
> cannot get
> Part 2 of the query to give the required result.
>
> I can take the resultset from just running a query to get the =
> information
> from table 1, and then go through this result set, running further =
> queries
> to get the information required from Table 2, but, as I see it, this =
> will
> have to be a new query for each record in the Table1.Resultset, which =
> will
> take up a lot of bandwidth, and make the application slow.
>
> Is it possible to code this into one single query that returns all of =
> the
> values required?
>
> Many thanks for your help!
>
> Martin
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
