SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2002
    Location
    Atlanta
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Sub-Query Problem?

    Hello everyone-

    I have a basic SQL tutorial that's making me bang my head against a wall...I'm ok don't worry.

    What do you suppose is causing this ERROR 1064 in MySQL? Just assume the material in the actual tables is all correct:

    mysql>
    SELECT title FROM authors,books WHERE ( books.author_id = (SELECT author_id FROM authors WHERE firstname = 'Jason') AND books.author_id = authors.author_id);

    BUT, this works!?

    mysql>
    SELECT title FROM authors,books WHERE
    (books.author_id = 1 AND books.author_id = authors.author_id);

    -->I'm not suppose to know that books.author_id = 1, I need to sub-query it like the former, but every variation I can think of I get that bloody 1064.

    Besides the fact that I'm making an error that's beyond me, could it be a settin in a file somewhere?

    Thanks for any help

  2. #2
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    MySQL doesn't support sub-queries. this looks like it will do the same thing:

    Code:
    SELECT title FROM authors a, books b
    	WHERE b.author_id=a.author_id AND b.author_id=1 AND a.firstname='Jason'
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2002
    Location
    Atlanta
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for your help-

    That's absolutely correct. Can you explain to me what you did exactly? So, you named the tables a and b?

    How is a relationship created using a and b I guess is what I wanted to know because when I tried the same code with the actual authors.author_id in place of "a" it simply didn't work.

    Thanks again.

  4. #4
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i just gave the tables shorter aliases. here's how you could do it using the full names:

    Code:
    SELECT title FROM authors, books
    	WHERE books.author_id=authors.author_id AND books.author_id=1 AND authors.firstname='Jason'


Bookmarks

Posting Permissions

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