Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2006
    Posts
    10

    Question Unanswered: How do I?? Maybe a recursive query??

    Hello Gang,

    Take a look at my query below (that doesn't work..). Hopefully someone will see and understand what I am trying to accomplish without me having to go into a long explanation.

    Code:
    SELECT Accounts.Name, Accounts.Add1, Accounts.City, Accounts.State, Accounts.Zip, 
    	   Accounts.Latitude, Accounts.Longitude, Accounts.Phone, Accounts.As400ID 
    FROM   AccMachines INNER JOIN Accounts INNER JOIN ZipCodes INNER JOIN Agents ON 
    	   ZipCodes.AgentID = Agents.AgentID ON Accounts.Zip = ZipCodes.Zip ON 
    	   AccMachines.As400OwnerID = Accounts.As400ID 
    WHERE  (Agents.Agent LIKE '%') AND (Accounts.State LIKE '%') AND (Accounts.Latitude IS NOT NULL) 
    	   AND (Accounts.Longitude IS NOT NULL) AND ((AccMachines.Model LIKE 'Machine1%') 
    	   AND (AccMachines.Model LIKE 'Machine2%')) 
    GROUP BY Accounts.Name, Accounts.Add1, Accounts.City, Accounts.State, Accounts.Zip, 
    		 Accounts.Latitude, Accounts.Longitude, Accounts.Phone, Accounts.As400ID
    I am trying to return customer info where the customer has multiple matching machines in the AccMachines table however each machine has its own record in the table. How can I go about achieving this? If further explanation is needed please let me know. Thanks in advance for any assistance!

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    The HAVING clause of the SELECT statement is the tool you seek. HAVING (Transact-SQL)
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    RE: How do I??

    From a cursory glance, one modification to try is...

    Code:
    WHERE AccMachines.Model LIKE 'Machine[12]%'
    Included in the query (modified) as...

    Code:
    SELECT
      Accounts.Name
     ,Accounts.Add1
     ,Accounts.City
     ,Accounts.State
     ,Accounts.Zip
     ,Accounts.Latitude
     ,Accounts.Longitude
     ,Accounts.Phone
     ,Accounts.As400ID 
    FROM AccMachines
    INNER JOIN Accounts
      ON AccMachines.As400OwnerID = Accounts.As400ID 
      AND Accounts.State IS NOT NULL
      AND Accounts.Latitude IS NOT NULL
      AND Accounts.Longitude IS NOT NULL
    INNER JOIN ZipCodes
      ON Accounts.Zip = ZipCodes.Zip
    INNER JOIN Agents
      ON ZipCodes.AgentID = Agents.AgentID
      AND Agents.Agent IS NOT NULL
    WHERE
      AccMachines.Model LIKE 'Machine[12]%'
    GROUP BY
      Accounts.Name
     ,Accounts.Add1
     ,Accounts.City
     ,Accounts.State
     ,Accounts.Zip
     ,Accounts.Latitude
     ,Accounts.Longitude
     ,Accounts.Phone
     ,Accounts.As400ID

  4. #4
    Join Date
    Oct 2006
    Posts
    10
    Quote Originally Posted by Thrasymachus View Post
    The HAVING clause of the SELECT statement is the tool you seek. HAVING (Transact-SQL)
    Thanks for the reply but this does not work, the query below should return results but it comes back empty:

    Code:
    SELECT Accounts.Name, Accounts.Add1, Accounts.City, Accounts.State, Accounts.Zip, 
    	   Accounts.Latitude, Accounts.Longitude, Accounts.Phone, Accounts.As400ID
    FROM   AccMachines INNER JOIN Accounts INNER JOIN ZipCodes INNER JOIN Agents ON 
    	   ZipCodes.AgentID = Agents.AgentID ON Accounts.Zip = ZipCodes.Zip ON 
    	   AccMachines.As400OwnerID = Accounts.As400ID 
    WHERE  (Agents.Agent LIKE '%') AND (Accounts.State LIKE '%') AND (Accounts.Latitude IS NOT NULL) 
    	   AND (Accounts.Longitude IS NOT NULL) 
    GROUP BY Accounts.Name, Accounts.Add1, Accounts.City, Accounts.State, Accounts.Zip, Accounts.Latitude, Accounts.Longitude, Accounts.Phone, Accounts.As400ID, AccMachines.Model
    HAVING (AccMachines.Model LIKE 'VALUSPIN%' AND AccMachines.Model LIKE 'G1000%')

  5. #5
    Join Date
    Oct 2006
    Posts
    10
    Quote Originally Posted by homerow View Post
    From a cursory glance, one modification to try is...

    Code:
    WHERE AccMachines.Model LIKE 'Machine[12]%'
    Thanks for the reply however the machine types are model specific such as G1000 or VALUSPIN, I had just used "Machine1" and "Machine2" as fillers.

  6. #6
    Join Date
    Oct 2006
    Posts
    10
    ok, not sure if this is the most efficient way but persistence pays off, this works and it will match 3 machines to each customer:

    Code:
    SELECT Accounts.Name, Accounts.Add1, Accounts.City, Accounts.State, Accounts.Zip, 
    	   Accounts.Latitude, Accounts.Longitude, Accounts.Phone, Accounts.As400ID
    FROM   AccMachines INNER JOIN Accounts INNER JOIN ZipCodes INNER JOIN Agents ON 
    	   ZipCodes.AgentID = Agents.AgentID ON Accounts.Zip = ZipCodes.Zip ON 
    	   AccMachines.As400OwnerID = Accounts.As400ID 
    WHERE  (Agents.Agent LIKE '%') AND (Accounts.State LIKE '%') AND (Accounts.Latitude IS NOT NULL) 
    	   AND (Accounts.Longitude IS NOT NULL) AND ((AccMachines.Model LIKE 'G1000%')
    AND (AccMachines.As400OwnerID IN (SELECT AccMachines.As400OwnerID FROM AccMachines WHERE (AccMachines.Model LIKE 'G2000%')))
    AND (AccMachines.As400OwnerID IN (SELECT AccMachines.As400OwnerID FROM AccMachines WHERE (AccMachines.Model LIKE 'VALUSPIN%'))))
    GROUP BY Accounts.Name, Accounts.Add1, Accounts.City, Accounts.State, Accounts.Zip, Accounts.Latitude, 
    		 Accounts.Longitude, Accounts.Phone, Accounts.As400ID, AccMachines.Model
    Thanks for the replies!

Posting Permissions

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