SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Visible Ninja bronze trophy
    JeffWalden's Avatar
    Join Date
    Sep 2002
    Location
    Los Angeles
    Posts
    1,709
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Sub Query - Need Help with Scope

    Generally I would accomplish this by breaking the SQL query into multiple smaller queries, but I'm trying to learn and do this the "right" way so here goes...
    Code SQL:
    Concat(Customers.FirstName, ' ', Customers.LastName) AS ListenerName,
    			Customers.EmailAddress AS EmailAddress,
    			Customers.PhoneNumber AS PhoneNumber,
    			Customers.BuyerPoints AS BuyerPoints,
    			(SELECT COUNT(*) FROM CartContents, Orders, Customers WHERE CartContents.Cart=Orders.Cart AND Orders.Customer=Customers.ID AND CartContents.Quantity>'0' AND (Orders.STATUS='New' OR Orders.STATUS='Pending' OR Orders.STATUS='Archived')) AS CertsOrdered,
    			(SELECT SUM(CartContents.Price * CartContents.Quantity) FROM CartContents, Orders, Customers WHERE CartContents.Cart=Orders.Cart AND Orders.Customer=Customers.ID AND CartContents.Quantity>'0' AND (Orders.STATUS='New' OR Orders.STATUS='Pending' OR Orders.STATUS='Archived')) AS TotalPaid
    		FROM
    			Customers,
    			Orders
    		WHERE
    			Customers.BusinessUnit='$_SESSION[SESS_BIZUNIT]'
    		AND
    			Orders.Customer=Customers.ID
    		ORDER BY
    			Customers.LastName ASC

    That's the full query. The problem is that in each of my sub queries, let's take the first COUNT one for example, I'm counting the number of items ordered from the CartContents table. However, it's counting the total number of items ordered by all customers, not just the one customer and then looping. The syntax isn't correct, but this is how I *think* it should be written:
    Code SQL:
    (SELECT COUNT(*) FROM CartContents, Orders, Customers WHERE CartContents.Cart=Orders.Cart AND Orders.Customer=Customers.ID AND CartContents.Quantity>'0' AND (Orders.STATUS='New' OR Orders.STATUS='Pending' OR Orders.STATUS='Archived') AND Customers.ID=ParentQuery.Customers.ID) AS CertsOrdered,

    See the last AND statement in there? I need to equate the local Customers.ID with the Customers.ID from the parent query. How do I tell MySQL to go reference the ID field from the parent query?
    TAKE A WALK OUTSIDE YOUR MIND.

  2. #2
    Visible Ninja bronze trophy
    JeffWalden's Avatar
    Join Date
    Sep 2002
    Location
    Los Angeles
    Posts
    1,709
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    This seems to be referencing the same problem, probably worded better that i have above. Still not understanding the solution.

    http://stackoverflow.com/questions/1...a-parent-query
    TAKE A WALK OUTSIDE YOUR MIND.

  3. #3
    Visible Ninja bronze trophy
    JeffWalden's Avatar
    Join Date
    Sep 2002
    Location
    Los Angeles
    Posts
    1,709
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Ok, I think I've got it. I just added an alias to the column in the parent query and was then able to reference that alias in the sub query. Seems too simple.

    Is there a way to set an alias in the parent query without actually selecting the column?
    TAKE A WALK OUTSIDE YOUR MIND.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Put an alias on the table, and then reference that in the subquery


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
  •