Professional Web Applications Themes

correlated subquery problem - MySQL

Sorry if this is a newbie question, but I cannot find a satisfactory answer elsewhere... I have a series of tables, related by keys. + tblUsers - contains usernames, passwords, and foreign key from tblRolodex + tblRolodex - contains names, addresses. key = RolodexID + tblRolodexEmail - contains email addresses. foreign key from tblRolodex. Table contains 0 or more email addresses per tblRolodex entry. + tblRolodexPhone - contains phone numbers. foreign key from tblRolodex. Table contains 0 or more phone numbers per tblRolodex entry. Here is the query I am trying to execute. It tells me that there is an ...

  1. #1

    Default correlated subquery problem

    Sorry if this is a newbie question, but I cannot find a satisfactory
    answer elsewhere...

    I have a series of tables, related by keys.
    + tblUsers - contains usernames, passwords, and foreign key from
    tblRolodex
    + tblRolodex - contains names, addresses. key = RolodexID
    + tblRolodexEmail - contains email addresses. foreign key from
    tblRolodex. Table contains 0 or more email addresses per tblRolodex
    entry.
    + tblRolodexPhone - contains phone numbers. foreign key from
    tblRolodex. Table contains 0 or more phone numbers per tblRolodex
    entry.

    Here is the query I am trying to execute. It tells me that there is an
    error in the sub-select query. This query runs ok on SQL Server, so is
    there a syntax difference writing this for MySQL?

    SELECT
    U.UserName,
    U.Password,
    R.FName,
    R.LName,
    (SELECT E.Email FROM tblRolodexEmail E WHERE E.RolodexID=R.RolodexID
    AND PrimaryEmail='Y') AS Email,
    (SELECT P.PhoneNo FROM tblRolodexPhone P WHERE P.RolodexID=R.RolodexID
    AND PrimaryNo='Y') AS PhoneNo
    FROM tblUsers U INNER JOIN tblRolodex R
    ON U.RolodexID=R.RolodexID
    JOIN
    WHERE UserName='admin'
    AND Password=''

    Thanks in advance!
    Timothy

    SethosLA@gmail.com Guest

  2. #2

    Default Re: correlated subquery problem

    com wrote: 

    You should rewrite these as a series of LEFT JOINS


    Paul Guest

  3. #3

    Default Re: correlated subquery problem


    Paul Lautman wrote: 

    That's what I was afraid of. *sigh* I was hoping that I could just use
    the subquery and be done with it. Thanks anyways...

    SethosLA@gmail.com Guest

  4. #4

    Default Re: correlated subquery problem

    com wrote: 

    Subqueries, including correlated subqueries in the select-list, are
    supported on MySQL 4.1 and later. If you use MySQL 4.0 or earlier, you
    cannot use subqueries.

    Here's an alternative using syntax that works in MySQL 4.0:

    SELECT
    U.UserName,
    U.Password,
    R.FName,
    R.LName,
    E.Email,
    P.PhoneNo
    FROM tblUsers U INNER JOIN tblRolodex R
    ON U.RolodexID=R.RolodexID
    LEFT JOIN tblRolodexEmail E ON E.RolodexID = R.RolodexID AND
    E.PrimaryEmail = 'Y'
    LEFT JOIN tblRolodexPhone P ON P.RolodexID = R.RolodexID AND
    P.PrimaryNo = 'Y'
    WHERE R.UserName='admin'
    AND R.Password=''

    Note that this gives multiple rows in the result set if you have
    multiple rows in the email and phone tables where PrimaryEmail = 'Y' or
    PrimaryNo = 'Y'. But I would assume that only one entry per rolodex is
    allowed to be the primary entry.

    Also, what happens if there are entries but _none_ is marked as the
    primary one?

    Regards,
    Bill K.
    Bill Guest

Similar Threads

  1. Subquery
    By Ruszaj in forum Coldfusion Database Access
    Replies: 2
    Last Post: November 2nd, 04:50 PM
  2. mySQL subquery problem
    By rmorgan in forum Coldfusion Database Access
    Replies: 7
    Last Post: October 30th, 07:06 AM
  3. using list in subquery
    By spacehog in forum Coldfusion - Advanced Techniques
    Replies: 3
    Last Post: March 9th, 11:23 PM
  4. SQL Server 2000 Correlated Query Question
    By MDW in forum ASP Database
    Replies: 2
    Last Post: June 13th, 05:13 PM
  5. Correlated Subquery or Distinct
    By stabbert in forum IBM DB2
    Replies: 0
    Last Post: July 31st, 02:07 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139