Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2007
    Posts
    12

    Unanswered: passing information

    What i am trying to do is have the contact information to be displayed also. The contact information is in dbo.CONTSUPP under column 'contact'. Is there anyway to pass that 'contact' value up to the parent select statement. So the result will show Company, Address1,...,Source, contact.

    Code:
    SELECT Company, Address1, Address2,
           Address3, City, State, Zip,
           Country, Phone1, Fax, Source
    FROM dbo.CONTACT1
    WHERE dbo.CONTACT1.ACCOUNTNO IN (
      SELECT ACCOUNTNO
      FROM dbo.CONTSUPP 
      WHERE	contact LIKE '%test1%' OR
    	contact LIKE '%test2%' OR
    	contact LIKE '%test3%' OR
    	contact LIKE '%test4%'
      GROUP BY ACCOUNTNO
      HAVING COUNT(*) <= 1 
    )

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    yup, derived table:

    Code:
    SELECT Company, Address1, Address2,
           Address3, City, State, Zip,
           Country, Phone1, Fax, Source
       ,t1.CONTSUPP
    FROM dbo.CONTACT1 INNER JOIN
    (
      SELECT ACCOUNTNO, CONTSUPP
      FROM dbo.CONTSUPP 
      WHERE	contact LIKE '%test1%' OR
    	contact LIKE '%test2%' OR
    	contact LIKE '%test3%' OR
    	contact LIKE '%test4%'
      GROUP BY ACCOUNTNO
      HAVING COUNT(*) <= 1 
    ) As t1 ON t1.ACCOUNTNO = CONTACT1.ACCOUNTNO

    You get the benefit of only generating the derived table once as well, as opposed to being evaluated once for each record when placed in the WHERE clause.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jun 2007
    Posts
    12
    Thank you, you pointed me in the right direction. There was one issue with the code you wrote because you cant group by ACCOUNTNO because the select has ACCOUNTNO and CONTACT. Anyway this is what the currently working code looks like. Thank you again, without your help I would not have been able to do this.
    Code:
    SELECT Company, Address1, Address2,
           Address3, City, State, Zip,
           Country, Phone1, Fax, Source,
           t1.contact AS 'Device'
    FROM dbo.CONTACT1 
    INNER JOIN (
    	SELECT accountno, contact
    	FROM dbo.CONTSUPP
    	WHERE accountno IN (
    		SELECT accountno
    		FROM dbo.CONTSUPP
    		WHERE contact LIKE '%test1%' OR
    	  	    contact LIKE '%test2%' OR
    	  	    contact LIKE '%test3%' OR
    	  	    contact LIKE '%test4%'
    		GROUP BY accountno
    		HAVING COUNT(*) <= 1
    	) AND (
    		contact LIKE '%test1%' OR
    		contact LIKE '%test2%' OR
    		contact LIKE '%test3%' OR
    		contact LIKE '%test4%'
    	)
    ) AS t1
    ON dbo.CONTACT1.accountno = t1.accountno

Posting Permissions

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