Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Regular Coder
    Join Date
    Oct 2004
    Location
    London E4 UK
    Posts
    320
    Thanks
    0
    Thanked 0 Times in 0 Posts

    select rows where a field value exists in another table

    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

  • #2
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    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);

  • #3
    Regular Coder
    Join Date
    Oct 2004
    Location
    London E4 UK
    Posts
    320
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Quote Originally Posted by Tynan
    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.

  • #5
    Regular Coder
    Join Date
    Oct 2004
    Location
    London E4 UK
    Posts
    320
    Thanks
    0
    Thanked 0 Times in 0 Posts
    noted

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.
    Last edited by guelphdad; 08-25-2006 at 09:40 PM.

  • #7
    Regular Coder
    Join Date
    Oct 2004
    Location
    London E4 UK
    Posts
    320
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #8
    Regular Coder
    Join Date
    Oct 2004
    Location
    London E4 UK
    Posts
    320
    Thanks
    0
    Thanked 0 Times in 0 Posts

    tried the posh query, this is the error I've been getting from the start

    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

  • #9
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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?

  • #10
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #11
    Regular Coder
    Join Date
    Oct 2004
    Location
    London E4 UK
    Posts
    320
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm on websitesource

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

    I'll see

    thanks again

  • #12
    Regular Coder
    Join Date
    Oct 2004
    Location
    London E4 UK
    Posts
    320
    Thanks
    0
    Thanked 0 Times in 0 Posts
    '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

  • #13
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #14
    Senior Coder
    Join Date
    Jul 2005
    Location
    New York, NY
    Posts
    1,084
    Thanks
    4
    Thanked 19 Times in 19 Posts
    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.

  • #15
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •