none
Help in SQL query

    Question

  • Hi All,

    persons:

     

    Pid     lastname    fname           addr                 city

    1        Hansen           Ola         Timoteivnlo        sandnes

    2         svendson    Tove         Borgvn23            sandness

    The “orders “table (pid) column has foreign key relations with persons table.

    Oid         Ono         Pid

    1            22456      1

    2            24562      1

    3            53273      1

    4            67677      2

    Write a query to return all the persons having atleast 2 order.  

     

    Please let me know the answers. I am struk in a project.

     

     

    Thanks

    Bijay

     

     


    Thanks: Bijay Kumar Sahoo Personal Blog URL: http://www.fewlines4biju.com MCTS(MOSS 2007)
    Wednesday, September 15, 2010 7:49 PM

Answers

  • Try

    ;with cte as
    (
     select pid, count(oid)
     from orders as o
     group by pid
     having count(oid) >= 2
    
    )
    select p.lastname, p.firstname, p.id, p.city 
    from person as p innner join cte on cte.pid = p.pid

     

     

    Wednesday, September 15, 2010 8:07 PM
  • <pre>SELECT
    	 p.pid
     FROM
    	persons p
    	INNER JOIN orders o ON p.PID = o.PID
    GROUP BY
    	p.pid
    HAVING
    	COUNT(OID) >= 2
    	
    	
    

    This is just getting theID of any person with more than 1 order in the system.  You can use this to pull other info out like in:

    SELECT 
    	 fname
    	, lastname
    	, addr
    	, city 
     FROM 
    	persons
     WHERE 
    	PID IN (
    		SELECT
    			 p.pid
    		 FROM
    			persons p
    			INNER JOIN orders o ON p.PID = o.PID
    		GROUP BY
    			p.pid
    		HAVING
    			COUNT(OID) >= 2	
    	)
    

    Gulf Coast SQL
    Wednesday, September 15, 2010 8:08 PM

All replies

  • Try

    ;with cte as
    (
     select pid, count(oid)
     from orders as o
     group by pid
     having count(oid) >= 2
    
    )
    select p.lastname, p.firstname, p.id, p.city 
    from person as p innner join cte on cte.pid = p.pid

     

     

    Wednesday, September 15, 2010 8:07 PM
  • <pre>SELECT
    	 p.pid
     FROM
    	persons p
    	INNER JOIN orders o ON p.PID = o.PID
    GROUP BY
    	p.pid
    HAVING
    	COUNT(OID) >= 2
    	
    	
    

    This is just getting theID of any person with more than 1 order in the system.  You can use this to pull other info out like in:

    SELECT 
    	 fname
    	, lastname
    	, addr
    	, city 
     FROM 
    	persons
     WHERE 
    	PID IN (
    		SELECT
    			 p.pid
    		 FROM
    			persons p
    			INNER JOIN orders o ON p.PID = o.PID
    		GROUP BY
    			p.pid
    		HAVING
    			COUNT(OID) >= 2	
    	)
    

    Gulf Coast SQL
    Wednesday, September 15, 2010 8:08 PM
  • Hi Thanks for ur replies. It works for me !!!!!!!
    Thanks: Bijay Kumar Sahoo Personal Blog URL: http://www.fewlines4biju.com MCTS(MOSS 2007)
    Wednesday, September 15, 2010 8:43 PM