Re: SQL Query Syntax
From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 08/29/04
- Next message: John Bell: "Re: security templates"
- Previous message: David Browne: "Re: Proper table structure and SQL Server limits"
- In reply to: Kalen Delaney: "Re: SQL Query Syntax"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 29 Aug 2004 09:46:58 -0500
Thanks for adding this, Kalen. I obviously assumed that the combination of
Name and Value uniquely identified a row. Your solution will return the
expected results when non-unique as well.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Kalen Delaney" <replies@public_newsgroups.com> wrote in message
news:OOF%236wYjEHA.2412@TK2MSFTNGP15.phx.gbl...
> This looks close, but would only be guaranteed if (name, value) was
> unique.
> If there were 2 rows with ('John', 2501) and none with ('John', 2503), the
> count would still be 2.
>
> Instead, you could try:
>
> SELECT Name
> FROM MyTable
> WHERE Value IN(2501, 2503)
> GROUP BY Name
> HAVING COUNT(distinct Value) = 2
>
>
> --
> HTH
> ----------------
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
>
> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
> news:ejSl24XjEHA.536@TK2MSFTNGP11.phx.gbl...
>> Try:
>>
>> CREATE TABLE MyTable
>> (
>> Name varchar(10) NOT NULL,
>> Value int NOT NULL
>> )
>>
>> INSERT INTO MyTable VALUES('John',2501)
>> INSERT INTO MyTable VALUES('John',2502)
>> INSERT INTO MyTable VALUES('John',2503)
>> INSERT INTO MyTable VALUES('Bill',2501)
>> INSERT INTO MyTable VALUES('Bill',2504)
>> INSERT INTO MyTable VALUES('Jim',2501)
>> INSERT INTO MyTable VALUES('Jim',2503)
>> INSERT INTO MyTable VALUES('Jim',2504)
>>
>> SELECT Name
>> FROM MyTable
>> WHERE Value IN(2501, 2503)
>> GROUP BY Name
>> HAVING COUNT(*) = 2
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> "gstoa" <gstoa@discussions.microsoft.com> wrote in message
>> news:004A357A-E42B-4AA1-B55D-A24BB3ADFCE0@microsoft.com...
>> > Hello,
>> > I am working with a very simple table structure and am tring to figure
> out
>> > the best way to structure a query to get the results I am looking for.
> My
>> > table structure looks like this:
>> >
>> > Name Value
>> > John 2501
>> > John 2502
>> > John 2503
>> > Bill 2501
>> > Bill 2504
>> > Jim 2501
>> > Jim 2503
>> > Jim 2504
>> >
>> > I want to be able to query this table to return all people that have,
> for
>> > example, values of 2501 AND 2503. In this case, both John and Jim
>> > would
>> > be
>> > returned since they both have values of 2501 and 2503.
>> >
>> > Obviously, "Select * from table where Value = 2501 and value = 2503"
> will
>> > return zero records.
>> >
>> > Any suggestions are greatly appreciated.
>>
>>
>
>
- Next message: John Bell: "Re: security templates"
- Previous message: David Browne: "Re: Proper table structure and SQL Server limits"
- In reply to: Kalen Delaney: "Re: SQL Query Syntax"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
