locked
Is there any way to do? RRS feed

  • Question

  • can I do like below in some way,

    CREATE TABLE X (ROLLNO INT, NAME VARCHAR(50), SCHOOL VARCHAR(100))
    INSERT INTO X VALUES(1, 'AYAZD', 'XYZ')
    SELECT * FROM Z WHERE SCHOOL = SELECT SCHOOL FROM X 
    				AND NAME = SELECT NAME FROM X
    
    Friday, August 27, 2010 8:03 AM

Answers

  • One of these 2 should work - each will return slightly different results:

    CREATE TABLE X (ROLLNO INT, NAME VARCHAR(50), SCHOOL VARCHAR(100))
    INSERT INTO X VALUES(1, 'AYAZD', 'XYZ')
    GO
    
    SELECT * FROM Z 
    WHERE SCHOOL IN (SELECT SCHOOL FROM X )
    	AND NAME IN (SELECT NAME FROM X)
    -- OR
    SELECT * 
    FROM Z 
    WHERE EXISTS (SELECT * FROM X WHERE Z.SCHOOL = X.SCHOOL AND Z.NAME = X.NAME)
    
    

    every day is a school day
    Friday, August 27, 2010 8:17 AM
  • SELECT Z.* 
    FROM Z
    WHERE Z.SCHOOL IN (SELECT SCHOOL FROM X)
    AND Z.NAME IN (SELECT NAME FROM X)

    SELECT *
    FROM Z
    WHERE EXISTS (SELECT * FROM X WHERE Z.NAME = X.NAME AND Z.SCHOOL = X.SCHOOL)

    SELECT Z.* 
    FROM X INNER JOIN
    Z ON X.NAME = Z.NAME AND X.SCHOOL = Z.SCHOOL


    Jon
    • Marked as answer by Yazdani ISTS Friday, August 27, 2010 9:51 AM
    Friday, August 27, 2010 8:22 AM
  • SELECT * FROM Z WHERE SCHOOL IN (SELECT SCHOOL FROM X) AND NAME IN ( SELECT NAME FROM X)
    
    Hi..You Can Do In This Way.
    • Marked as answer by Yazdani ISTS Friday, August 27, 2010 9:51 AM
    Friday, August 27, 2010 9:02 AM

All replies

  • One of these 2 should work - each will return slightly different results:

    CREATE TABLE X (ROLLNO INT, NAME VARCHAR(50), SCHOOL VARCHAR(100))
    INSERT INTO X VALUES(1, 'AYAZD', 'XYZ')
    GO
    
    SELECT * FROM Z 
    WHERE SCHOOL IN (SELECT SCHOOL FROM X )
    	AND NAME IN (SELECT NAME FROM X)
    -- OR
    SELECT * 
    FROM Z 
    WHERE EXISTS (SELECT * FROM X WHERE Z.SCHOOL = X.SCHOOL AND Z.NAME = X.NAME)
    
    

    every day is a school day
    Friday, August 27, 2010 8:17 AM
  • SELECT Z.* 
    FROM Z
    WHERE Z.SCHOOL IN (SELECT SCHOOL FROM X)
    AND Z.NAME IN (SELECT NAME FROM X)

    SELECT *
    FROM Z
    WHERE EXISTS (SELECT * FROM X WHERE Z.NAME = X.NAME AND Z.SCHOOL = X.SCHOOL)

    SELECT Z.* 
    FROM X INNER JOIN
    Z ON X.NAME = Z.NAME AND X.SCHOOL = Z.SCHOOL


    Jon
    • Marked as answer by Yazdani ISTS Friday, August 27, 2010 9:51 AM
    Friday, August 27, 2010 8:22 AM
  • SELECT * FROM Z WHERE SCHOOL IN (SELECT SCHOOL FROM X) AND NAME IN ( SELECT NAME FROM X)
    
    Hi..You Can Do In This Way.
    • Marked as answer by Yazdani ISTS Friday, August 27, 2010 9:51 AM
    Friday, August 27, 2010 9:02 AM
  • thanx aLL
    Friday, August 27, 2010 9:52 AM