...

View Full Version : select rows where a field value exists in another table



Tynan
08-25-2006, 06:40 PM
hello, very basic mysql knowledge

I have a table with a column of email addresses

I have a second table with a column of email addresses

I want to select all the rows in the first table whose email value exists in the second table

Can't get a query to do this without an error, I'd be grateful for the correct syntax, thank-you

GJay
08-25-2006, 07:30 PM
SELECT a.email FROM table1 a JOIN table2 b ON (a.email=b.email);
or
SELECT a.email FROM table 1 a WHERE a.email IN (SELECT b.email FROM table2 b);

Tynan
08-25-2006, 07:37 PM
I was trying something close to the second one without sucess

just run this one and it seemed to have worked

SELECT All_Members . * , HB . *
FROM All_Members, HB
WHERE All_Members.Email = HB.Email
LIMIT 0 , 30

is that the same thing?

and thank-you

guelphdad
08-25-2006, 08:59 PM
hello, very basic mysql knowledge ...

Can't get a query to do this without an error, I'd be grateful for the correct syntax, thank-you

A good habit to get into then is to post the query you are trying and the syntax error message you are getting so people can help you with a solution and also determine what was wrong with the query you tried.

Tynan
08-25-2006, 09:00 PM
noted

guelphdad
08-25-2006, 09:38 PM
you are also doing a join, but it is in list syntax, where the tables are all named first and then the columns being joined second. For some reason mysql sticks to that rather than using explicit ON clauses. It is much easier to make a mistake in the way you are doing the joins. you will also find that in mysql 5 your conditions are much more strict so using sloppy code now will hurt you down the road.

also, your limit 30 shows only 30 rows that would be returned even if there were 500.

also a good habit to get into is actually naming your columns rather than using * it involves a little more typing but is clearer as to the columns being returned in a query.

Tynan
08-26-2006, 10:09 AM
thank-you, the code I've pasted is the utter limit of my knowledge at the moment, the limit was stuck on my phpmyadmin

I think I'm cool for the moment, sounder code will follow in time

hopefully

Tynan
08-26-2006, 10:20 AM
SQL query: Documentation

SELECT `Email`
FROM All_Members
WHERE `Email`
IN (

SELECT 'EmailHB'
FROM HB
)

MySQL said: Documentation
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT 'EmailHB' FROM HB ) LIMIT 0, 30' at line 1

Fumigator
08-26-2006, 03:36 PM
I got caught on this one, you're probably using a version of MySQL that doesn't support subqueries like what you're trying there. I think the version has to be 4.1+ for subquery support.

Did you try GJay's examples?

guelphdad
08-26-2006, 04:07 PM
If mysql is on your own computer upgrade to 4.1 or I'd suggest 5.0
if you are paying a host and they aren't on 4.1 find a new host.

Tynan
08-27-2006, 05:49 PM
I'm on websitesource

Found them fine so far but what would I know I suppose?

I'll see

thanks again

Tynan
08-27-2006, 05:56 PM
'Welcome to phpMyAdmin 2.6.4-pl4

MySQL 4.0.26-standard-log'

not 4.1 then I guess?

why would they be so far behind?

I'll ask them

guelphdad
08-27-2006, 06:42 PM
4.1 is a major upgrade from 4.0 it was the mysql production standard in october of 2004 since supplanted by full release 5.0 in october 2005 so if they are worried about stability 4.1 is fine. they just haven't bothered to put in the time for an upgrade.

mysql releases upgrades about once per month.

Beagle
08-28-2006, 03:42 PM
So why not just go with the join notation:

select a.email from email1 as a JOIN email2 AS b on a.email=b.email

done.

Fumigator
08-29-2006, 01:21 AM
I bought hosting from Godaddy.com and they're still on 4.0.... those buggars. I emailed and asked what's their schedule for upgrading and they said there isn't one-- they have no plans to upgrade. WHAT?!?

So yeah as soon as the year is up I'm moving over to some other hosting company, probably Lunar Pages.

Tynan
08-30-2006, 09:35 PM
they run various versions on their shared servers, they offered to move me to another server that runs v4.5 ish

pondering if I need to

6 months sinto a two year stretch

guelphdad
08-30-2006, 10:51 PM
well there is no version 4.5, perhaps 4.1.5? either or it will be a decent upgrade over what they have you using now. most hosts don't make mysql 5 available yet.

kenwvs
09-02-2006, 08:22 PM
I am on LunarPages and they are on 4.1.21 and just upgraded the PHP to version 4.4.4 earlier this week.

I love their support, only drawback is they don't offer InnoDB table type.

Ken

Tynan
09-15-2006, 05:51 AM
So why not just go with the join notation:

select a.email from email1 as a JOIN email2 AS b on a.email=b.email

done.

sorry to be such a newb but can somene explain what a, b, email1 and email2 signify?

my email fields are both titled 'Email'
and my tables are 'All_Members' and 'NewEmails'

a and b are tables aliases I assume?

thank-you

Tynan
09-15-2006, 05:54 AM
SQL query:

UPDATE All_Members SET All_Members.Email FROM All_Members,
NewEmails WHERE All_Members.`FirstName` = NewEmails.`FirstName` AND All_Members.`LastName` = NewEmails.`LastName`

MySQL said: Documentation
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM All_Members, NewEmails
WHERE All_Members.`First

....
I'm on mysql4.0.26
I'm trying to copy the value of 'Email' from NewEmails to Email in All_members where LastName and FirstName both match

Tynan
09-15-2006, 06:53 AM
gawd, what a morning

this seems to do it



UPDATE All_Members,NewEmails
SET All_Members.Email=NewEmails.Email2
WHERE All_Members.LastName=NewEmails.LastName
AND All_Members.FirstName=NewEmails.FirstName

guelphdad
09-15-2006, 01:42 PM
sorry to be such a newb but can somene explain what a, b, email1 and email2 signify?

my email fields are both titled 'Email'
and my tables are 'All_Members' and 'NewEmails'

a and b are tables aliases I assume?

thank-you

yes a and b are aliases to the table names, email1 and email2 are just example names to refer to your tables you would use All_Members and NewEmails, but you didn't include the names of your tables in the original post.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum