[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: Query Help - Thanks
From:       Michael Stassen <Michael.Stassen () verizon ! net>
Date:       2004-02-29 17:18:24
Message-ID: 40421EE0.6030502 () verizon ! net
[Download RAW message or body]

I'd like to add an alternate solution.  All these subselects and unions 
seem overly complicated to me, when you could accomplish the same thing 
with a single join.  Also, union requires mysql 4.0.x or higher and 
subselects require mysql 4.1 or higher.  (Of course, you are obviously 
using 4.1+ since they work for you.)

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 'steve@home.net'
   ORDER BY lists_.Name;

   +-------+----------------+
   | Name  | EmailAddr      |
   +-------+----------------+
   | List1 | steve@home.net |
   | List2 | steve@home.net |
   | List3 | NULL           |
   | List4 | steve@home.net |
   | 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 'steve@home.net'
   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:rhino1@sympatico.ca] 
> Sent: 29 February 2004 13:40
> To: jberman@jewishgen.org; 'Paul DuBois'
> Cc: mysql@lists.mysql.com
> 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" <john_berman@blueyonder.co.uk>
> To: "'Paul DuBois'" <paul@mysql.com>
> Cc: <mysql@lists.mysql.com>
> 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:paul@mysql.com]
>>Sent: 29 February 2004 03:11
>>To: jberman@jewishgen.org
>>Cc: mysql@lists.mysql.com
>>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/mysql?unsub=mysql@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]