Need help forming SQL query
I have the following query which will get me all of the emails for my current membership: SELECT email.email_address FROM member, email WHERE member.member_primary_email_id = email.email_id AND member.member_standing != Dropped ORDER BY email.email_address I also have a query which will get me the email addresses of anyone who has updated their profile (and thus has a row in updatehistory) SELECT DISTINCT email.email_address FROM member, email, updatehistory WHERE member.member_primary_email_id = email.email_id AND member.member_standing != Dropped AND member.member_id = updatehistory.member_id_editor ORDER BY email.email_address; How would I get the emails for every member who does NOT have a row in updatehistory? ~ Mike - Mike Zornek Web Designer, Media Developer, Programmer and Geek Personal site: http://MikeZornek.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help forming SQL query
You should *ALWAYS* indicate which version of MySQL you are using when you ask this sort of question; the answers is almost always it depends: if you are using 3.x, do X, if you are using 4.0.x do Y, if you are using 4.1.x do Z. It's a lot of work to list all of those options and most people won't do it. It would also help if you provided information about what columns occurred in each table so that potential responders are clear on what information is in each of your tables. Including a few typical sample rows is also very helpful for some of us, although some people can envision the data clearly with only the column names. Rhino - Original Message - From: Mike Zornek [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, January 20, 2005 11:01 AM Subject: Need help forming SQL query I have the following query which will get me all of the emails for my current membership: SELECT email.email_address FROM member, email WHERE member.member_primary_email_id = email.email_id AND member.member_standing != Dropped ORDER BY email.email_address I also have a query which will get me the email addresses of anyone who has updated their profile (and thus has a row in updatehistory) SELECT DISTINCT email.email_address FROM member, email, updatehistory WHERE member.member_primary_email_id = email.email_id AND member.member_standing != Dropped AND member.member_id = updatehistory.member_id_editor ORDER BY email.email_address; How would I get the emails for every member who does NOT have a row in updatehistory? ~ Mike - Mike Zornek Web Designer, Media Developer, Programmer and Geek Personal site: http://MikeZornek.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help forming SQL query
On 1/20/05 11:16 AM, Rhino [EMAIL PROTECTED] wrote:
You should *ALWAYS* indicate which version of MySQL you are using when you
ask this sort of question; the answers is almost always it depends: if you
are using 3.x, do X, if you are using 4.0.x do Y, if you are using 4.1.x do
Z. It's a lot of work to list all of those options and most people won't do
it.
It would also help if you provided information about what columns occurred
in each table so that potential responders are clear on what information is
in each of your tables. Including a few typical sample rows is also very
helpful for some of us, although some people can envision the data clearly
with only the column names.
Rhino
Ok, noted. I'll include this info with any future questions.
i'm using MySQL 4.0.17-log
Email:
+---+--+--+-+-+-
---+
| Field | Type | Null | Key | Default | Extra
|
+---+--+--+-+-+-
---+
| email_id | smallint(5) unsigned | | PRI | NULL|
auto_increment |
| email_type| enum('Home','Work') | | | Work|
|
| email_address | varchar(50) | | UNI | |
|
| member_id | smallint(5) unsigned | | MUL | 0 |
|
| email_private | tinyint(1) | | | 0 |
|
+---+--+--+-+-+-
Update History:
++--+--+-+-+
+
| Field | Type | Null | Key | Default |
Extra |
++--+--+-+-+
+
| updatehistroy_id | smallint(5) unsigned | | PRI | NULL|
auto_increment |
| updatehistroy_datetime | datetime | YES | | NULL|
|
| member_id_edited | smallint(5) unsigned | | | 0 |
|
| member_id_editor | smallint(5) unsigned | | | 0 |
|
| updatehistory_details | text | | | |
|
++--+--+-+-+
- Original Message -
From: Mike Zornek [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, January 20, 2005 11:01 AM
Subject: Need help forming SQL query
I have the following query which will get me all of the emails for my
current membership:
SELECT email.email_address
FROM member, email
WHERE
member.member_primary_email_id = email.email_id
AND member.member_standing != Dropped
ORDER BY email.email_address
I also have a query which will get me the email addresses of anyone who
has
updated their profile (and thus has a row in updatehistory)
SELECT DISTINCT email.email_address
FROM member, email, updatehistory
WHERE
member.member_primary_email_id = email.email_id
AND member.member_standing != Dropped
AND member.member_id = updatehistory.member_id_editor
ORDER BY email.email_address;
How would I get the emails for every member who does NOT have a row in
updatehistory?
~ Mike
-
Mike Zornek
Web Designer, Media Developer, Programmer and Geek
Personal site: http://MikeZornek.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005
~ Mike
-
Mike Zornek
Web Designer, Media Developer, Programmer and Geek
Personal site: http://MikeZornek.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help forming SQL query
Mike Zornek [EMAIL PROTECTED] wrote on 01/20/2005 11:01:38 AM: I have the following query which will get me all of the emails for my current membership: SELECT email.email_address FROM member, email WHERE member.member_primary_email_id = email.email_id AND member.member_standing != Dropped ORDER BY email.email_address I also have a query which will get me the email addresses of anyone who has updated their profile (and thus has a row in updatehistory) SELECT DISTINCT email.email_address FROM member, email, updatehistory WHERE member.member_primary_email_id = email.email_id AND member.member_standing != Dropped AND member.member_id = updatehistory.member_id_editor ORDER BY email.email_address; How would I get the emails for every member who does NOT have a row in updatehistory? ~ Mike - Mike Zornek Web Designer, Media Developer, Programmer and Geek Personal site: http://MikeZornek.com First, I need to you recognize that listing tables with commas in the FROM clause creates an implicit INNER JOIN between the tables. That means that your query SELECT DISTINCT email.email_address FROM member, email, updatehistory WHERE member.member_primary_email_id = email.email_id AND member.member_standing != Dropped AND member.member_id = updatehistory.member_id_editor ORDER BY email.email_address; IS EQUIVALENT to this more explicitly defined query: SELECT DISTINCT email.email_address FROM member INNER JOIN email ON member.member_primary_email_id = email.email_id INNER JOIN updatehistory ON member.member_id = updatehistory.member_id_editor WHERE member.member_standing != Dropped ORDER BY email.email_address; In order to detect non-matches between two tables, you perform an outer join between them and look for the records that don't match. They will be easy to spot because the engine will put NULLs into every column of the optional table for those rows that don't match up with the required table. In a LEFT JOIN, the table on the left of the clause is the required table and the one on the right is the optional table. Reverse that for RIGHT JOINs. With that knowledge in hand, we will now change the query to detect which rows of member (required) do not match any rows in updatehistory (optional). We change the JOIN on that table from INNER JOIN to LEFT JOIN and look for NULL values where there shouldn't be any by adding another condition to the WHERE clause (they should only exist if rows between the two tables didn't meet your ON conditions(s), correct?). We don't need to change anything else. SELECT DISTINCT email.email_address FROM member INNER JOIN email ON member.member_primary_email_id = email.email_id LEFT JOIN updatehistory ON member.member_id = updatehistory.member_id_editor WHERE member.member_standing != Dropped AND updatehistory.member_id_editor IS NULL ORDER BY email.email_address; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Need help forming SQL query
That's a great start but you haven't shown the Member table. It would also
be very useful if you included a few sample rows from each table. For
example, I'm looking at the member_id_edited and member_id_editor columns so
that I can create tiny sample tables like yours and I have no idea what
values will typically go in those columns.
By the way, did you know that there are typos in the first two column names
of the Update_History table? updatehistroy should be spelled
updatehistory
Rhino
- Original Message -
From: Mike Zornek [EMAIL PROTECTED]
To: MySQL mysql@lists.mysql.com
Cc: Rhino [EMAIL PROTECTED]
Sent: Thursday, January 20, 2005 11:26 AM
Subject: Re: Need help forming SQL query
On 1/20/05 11:16 AM, Rhino [EMAIL PROTECTED] wrote:
You should *ALWAYS* indicate which version of MySQL you are using when
you
ask this sort of question; the answers is almost always it depends: if
you
are using 3.x, do X, if you are using 4.0.x do Y, if you are using 4.1.x
do
Z. It's a lot of work to list all of those options and most people
won't do
it.
It would also help if you provided information about what columns
occurred
in each table so that potential responders are clear on what information
is
in each of your tables. Including a few typical sample rows is also very
helpful for some of us, although some people can envision the data
clearly
with only the column names.
Rhino
Ok, noted. I'll include this info with any future questions.
i'm using MySQL 4.0.17-log
Email:
+---+--+--+-+-+-
---+
| Field | Type | Null | Key | Default | Extra
|
+---+--+--+-+-+-
---+
| email_id | smallint(5) unsigned | | PRI | NULL|
auto_increment |
| email_type| enum('Home','Work') | | | Work|
|
| email_address | varchar(50) | | UNI | |
|
| member_id | smallint(5) unsigned | | MUL | 0 |
|
| email_private | tinyint(1) | | | 0 |
|
+---+--+--+-+-+-
Update History:
++--+--+-+-+
+
| Field | Type | Null | Key | Default |
Extra |
++--+--+-+-+
+
| updatehistroy_id | smallint(5) unsigned | | PRI | NULL|
auto_increment |
| updatehistroy_datetime | datetime | YES | | NULL|
|
| member_id_edited | smallint(5) unsigned | | | 0 |
|
| member_id_editor | smallint(5) unsigned | | | 0 |
|
| updatehistory_details | text | | | |
|
++--+--+-+-+
- Original Message -
From: Mike Zornek [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, January 20, 2005 11:01 AM
Subject: Need help forming SQL query
I have the following query which will get me all of the emails for my
current membership:
SELECT email.email_address
FROM member, email
WHERE
member.member_primary_email_id = email.email_id
AND member.member_standing != Dropped
ORDER BY email.email_address
I also have a query which will get me the email addresses of anyone who
has
updated their profile (and thus has a row in updatehistory)
SELECT DISTINCT email.email_address
FROM member, email, updatehistory
WHERE
member.member_primary_email_id = email.email_id
AND member.member_standing != Dropped
AND member.member_id = updatehistory.member_id_editor
ORDER BY email.email_address;
How would I get the emails for every member who does NOT have a row in
updatehistory?
~ Mike
-
Mike Zornek
Web Designer, Media Developer, Programmer and Geek
Personal site: http://MikeZornek.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005
~ Mike
-
Mike Zornek
Web Designer, Media Developer, Programmer and Geek
Personal site: http://MikeZornek.com
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
