You can get all the lists a member belongs to like this:
SELECT lists_.Name FROM lists_ JOIN members_ ON lists_.Name = members_.List WHERE members_.EmailAddr LIKE '&em&' ORDER BY lists_.Name;
You can get all the lists a member does not belong to with a LEFT JOIN, like this:
SELECT lists_.Name FROM lists_ LEFT JOIN members_ ON lists_.Name = members_.List AND members_.EmailAddr LIKE '&em&' WHERE members_.EmailAddr IS NULL ORDER BY lists_.Name;
The LEFT JOIN gives you a row for each list, even when there is no corresponding member information on the right. When there is no member on the right, the right part of the row is filled with NULLs. So, we use the WHERE clause to select only the rows with NULL member info.
In fact, if you changed the "IS NULL" to "IS NOT NULL" in the second query, it would give the same result as the first. If you simply leave out the WHERE clause altogether, and add members_.EmailAddr to the SELECT clause, you will be very close to what you want. For example:
SELECT lists_.Name, members_.EmailAddr FROM lists_ LEFT JOIN members_ ON lists_.Name = members_.List AND members_.EmailAddr LIKE '[EMAIL PROTECTED]' ORDER BY lists_.Name;
+-------+----------------+ | Name | EmailAddr | +-------+----------------+ | List1 | [EMAIL PROTECTED] | | List2 | [EMAIL PROTECTED] | | List3 | NULL | | List4 | [EMAIL PROTECTED] | | List5 | NULL | +-------+----------------+ 5 rows in set (0.01 sec)
All we need to do now is clean up the second column. We can do that with an IF, like this:
SELECT lists_.Name List, IF(ISNULL(members_.EmailAddr), 'Not a Member', 'Member') membership FROM lists_ LEFT JOIN members_ ON lists_.Name = members_.List AND members_.EmailAddr LIKE '[EMAIL PROTECTED]' ORDER BY lists_.Name;
+-------+--------------+ | List | membership | +-------+--------------+ | List1 | Member | | List2 | Member | | List3 | Not a Member | | List4 | Member | | List5 | Not a Member | +-------+--------------+ 5 rows in set (0.01 sec)
If your tables are large and this will be run frequently, you may wish to try both ways to see which method, LEFT JOIN or UNION of subselects, is faster for you.
Michael
P.S. Underscores seemed to come and go in your column names (probably I didn't read closely enough). In any case, I kept the underscore at the end of your table names but left it out from your column names. You'll have to modify my example queries to fit your real table and column names.
John Berman wrote:
Rhino
This is great it works a treat Thanks
Regards John Berman
-----Original Message-----
From: Rhino [mailto:[EMAIL PROTECTED] Sent: 29 February 2004 13:40
To: [EMAIL PROTECTED]; 'Paul DuBois'
Cc: [EMAIL PROTECTED]
Subject: Re: Query Help
I hope you don't mind me butting in but your note was sent to the whole group....
The technique Paul is describing involves adding another expression to each of the SELECT clauses in the UNION. Up to now, you've probably just used column names or functions in a SELECT clause, for example:
select dept, avg(salary) from employee where job <> 'Manager' group by dept;
(NOTE: I'm using the term 'select clause' to mean just the part of the Select statement that precedes the 'from' clause. In other words, the first line of the above query as opposed to the whole query.)
It is also possible to include other expressions, like strings, in your queries. For example:
select dept, avg(salary), 'extra expression' from employee where job <> 'Manager' group by dept;
That query will have exactly the same result as the previous example (assuming no INSERTs, DELETEs, or UPDATEs to the table in the meantime!) except that each of the result rows will include a third column. In each case, the value in the third column will be the string 'extra expression'.
If you apply that technique to your union, you can use it to determine which table was the source of each of the rows in your result set. For example:
select dept, avg(salary), 'Manager' from employee where job = 'Manager' group by dept UNION select dept, avg(salary), 'Non-Manager' from employee where job <> 'Manager' group by dept;
The result set of this query will contain three columns, the third of which will have 'Manager' in all of the rows contributed by the first query and 'Non-Manager' in all of the rows contributed by the second query.
I'll leave you to apply this concept to *your* query ;-)
Rhino
----- Original Message ----- From: "John Berman" <[EMAIL PROTECTED]>
To: "'Paul DuBois'" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Sunday, February 29, 2004 3:54 AM
Subject: RE: Query Help
Paul
Sorry to be a pain. I'm not sure that I understand
Select an extra column in each SELECT. SELECT "member", ... UNION SELECT "non-member", ...
Regards
John Berman
-----Original Message----- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: 29 February 2004 03:11 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: Query Help
At 2:45 +0000 2/29/04, John Berman wrote:
Got it working at last
SELECT lists_.DescShort_ FROM lists_ WHERE (((lists_.Name_) Not In
(select
members_.List_ from members_ where members_.EmailAddr_ like ('"& em & "')))) union SELECT lists_.DescShort_ FROM members_ INNER JOIN lists_ ON members_.List_ = lists_.Name_ WHERE (members_.EmailAddr_ = ('"& em & "'))
My only problem being it now lists the lists Im not a member of and the
ones
I am a member of - how on earth do I show on screen which is which ?
Maybe:
Select an extra column in each SELECT. SELECT "member", ... UNION SELECT "non-member", ...
-- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
