#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    1
    Rep Power
    0

    Question Bank exemple doubt


    I'm studying mysql and I have implemented a simple bank database that I saw in a book. The scheme is:

    agency (name,assets,city)
    client (name,street,city)
    account (agency_name,number,client_name,balance)
    loan (agency_name,number,client_name,value)


    So I tried to do a mysql query to obtain the follow:
    "All clients that have an account in all "Brooklyn" agencies."

    in the book has a solution using the no standart sql MINUS set operation:

    Code:
    SELECT DISTINCT S.client_name
    FROM account AS S
    WHERE NOT EXISTS (
        (
         SELECT name
         FROM agency
         WHERE city = "Brooklyn"
        )
         MINUS
        (
         SELECT T.agency_name
         FROM account AS T
         WHERE S.client_name = T.client_name
        )
    )
    The first subquery finds all Brooklyn agencies.
    The second one finds all agencies where S.client_name has an account.
    If FIRST - SECOND is an empty set S.client has account in all Brooklyn agencies, and so the client is in the result set.

    However there isn't a MINUS set operation in MYSQL.
    I had tried use an workaround query to MINUS using subqueries but I have problems with the visibility of the Alias "S", because Alias aren't visible in deep WHERE subqueries.

    This is the workaround I tried:

    Code:
    SELECT DISTINCT S.client_name
    FROM account AS S
    WHERE NOT EXISTS (
    	SELECT * FROM (
    		(SELECT name FROM agency WHERE city = 'Brooklyn')
    		UNION ALL
    		(SELECT T.agency_name FROM account AS T WHERE S.client_name = T.client_name)
    	) AS U WHERE U.agency_name IN (SELECT name FROM agency WHERE city = 'Brooklyn') GROUP BY name HAVING COUNT(*) = 1 	
    )
    but this give the error:
    "Unknown column 'S.nome_cliente' in 'where clause'"

    Someone has an idea how I can do this query in mysql?
    Last edited by kcnpnew; October 29th, 2013 at 02:46 PM. Reason: More information
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Code:
    SELECT cl.client_name
         , cl.city
      FROM client AS cl
    INNER
      JOIN account AS ac
        ON ac.client_name = cl.client_name
    INNER
      JOIN agency AS ag
        ON ag.name = ac.agency_name
       AND ag.city = 'Brooklyn'
    GROUP
        BY cl.client_name
         , cl.city 
    HAVING COUNT(DISTINCT ag.name) =
           ( SELECT COUNT(*)
               FROM agency
              WHERE city = 'Brooklyn' )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo