SQL Query

Last Post 30 Dec 2010 07:54 AM by gunneyk. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
river1
New Member
New Member

--
29 Dec 2010 09:54 AM
Hi Masters,


I have this table:


COD; NAME; TYPE; STATUS


1 ; Pedro ; 1 ; A
2 ; Pedro ; 2 ; C
3 ; Pedro ; 2 ; A
4 ; Pedro ; 2 ; A
5 ; Marta ; 2 ; C
6 ; Marta ; 2 ; A
7 ; Marta ; 2 ; C
8 ; Paul ; 2 ; A
9 ; Ana ; 2; C


i wanto to make a query that retrive all the rows in this table wich have the same name and are from type 2 and have status equal to A.

The results, considering the data records above, would be:

3 ; Pedro ; 2 ; A
4 ; Pedro ; 2 ; A


Can someone help me with this query? how to do it? thank you


gunneyk
New Member
New Member

--
29 Dec 2010 02:24 PM
SELECT * FROM Table AS a
WHERE a.Name IN (SELECT b.Name FROM Table AS b WHERE COUNT(*) > 1 AND b.Type = 2 AND b.Status = 'A' GROUP BY b.Name))
AND a.Type = 2 AND a.Status = 'A'
river1
New Member
New Member

--
30 Dec 2010 01:39 AM

With this:

SELECT
* FROM table AS a

WHERE a.Name IN

(SELECT b.Name FROM table AS b WHERE COUNT(*) > 1 AND b.Type = 2 AND b.Status = 'A' GROUP BY b.Name)

AND a.Type = 2 AND a.Status = 'A'


I received this, from SQL Server:

Msg 147, Level 15, State 1, Line 1 An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

gunneyk
New Member
New Member

--
30 Dec 2010 07:54 AM
Sorry that was my mistake. Try this instead:

create table fred ([id] int NOT NULL identity(1,1), [name] varchar(10), [type] int, [status] char(1)) ;

INSERT INTO fred ([name], [type], [status]) VALUES ('Pedro',1,'A')
INSERT INTO fred ([name], [type], [status]) VALUES ('Pedro',2,'C')
INSERT INTO fred ([name], [type], [status]) VALUES ('Pedro',2,'A')
INSERT INTO fred ([name], [type], [status]) VALUES ('Pedro',2,'A')
INSERT INTO fred ([name], [type], [status]) VALUES ('Marta',2,'C')
INSERT INTO fred ([name], [type], [status]) VALUES ('Marta',2,'A')
INSERT INTO fred ([name], [type], [status]) VALUES ('Marta',2,'C')
INSERT INTO fred ([name], [type], [status]) VALUES ('Paul',2,'A')
INSERT INTO fred ([name], [type], [status]) VALUES ('Ana',2,'C')

SELECT * FROM fred AS a
WHERE a.Name IN
(SELECT b.Name FROM fred AS b WHERE b.Type = 2 AND b.Status = 'A' GROUP BY b.Name HAVING COUNT([name]) > 1 )
AND a.Type = 2 AND a.Status = 'A'

You are not authorized to post a reply.

Acceptable Use Policy