Query Question

I have a query that I am not sure how to write.
I need to get the value of a column where that column's value happens more
than once AND a date column has the same date.
for instance:
ColA ColB
--
ValA 1/1/2003
ValA 1/1/2003
ValX 1/1/2003
I need to find all values from ColA where there is another row with the same
value in ColA and ColB (in this case the query should return ValA).
?SELECT DISTINCT colA
FROM Sometable
GROUP BY colA, colB
HAVING COUNT(*)>1
--
David Portas
--
Please reply only to the newsgroup
--|||Thanks David,
Makes sense and did the trick
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:-PWdnfOhm6CPriCi4p2dnA@.giganews.com...
> SELECT DISTINCT colA
> FROM Sometable
> GROUP BY colA, colB
> HAVING COUNT(*)>1
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>|||select cola from table_name
group by cola,colb
having count(*) >1
_______________________
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message
news:OvXOcu6rDHA.1764@.TK2MSFTNGP10.phx.gbl...
> I have a query that I am not sure how to write.
> I need to get the value of a column where that column's value happens more
> than once AND a date column has the same date.
> for instance:
> ColA ColB
> --
> ValA 1/1/2003
> ValA 1/1/2003
> ValX 1/1/2003
> I need to find all values from ColA where there is another row with the
same
> value in ColA and ColB (in this case the query should return ValA).
> ?
>
>

query problem

Hi,
I have to two colums
first colum second colum
1 0
2 0
3 0
4 1
5 1
6 2
7 2
8 3
9 3
10 4
11 5
What I need to do is retrieving all the records from "second colum" which equal to the records in "first colum" and the records in that "first colum" must have 0 in "second colum."
In short: I need to retrieve the records in second colum: 1, 1, 2, 2, 3, 3
Please, help me with that queryCan I just check:
Select the unique numbers from the second column, check that that number exists in the first column, then check if those rows have 0 as the second column?
|||Ugly as sin...
SELECT col1
FROM testTable
WHERE (col1 IN
(SELECT col1
FROM testTable
WHERE (col1 IN
(SELECT DISTINCT col2
FROM testtable)))) AND (col2 = 0)
|||in your example, i don't see any values in the second column that equal the first column...could you verify that?
the query would look something like the following though:
select second_column
from [yourTableName]
where second_columsn <> 0 AND second_column = first_column
|||Hey Jayson, I think you missed his question just by a bit...It isn'tRows where first column = second column, but where second column [values] are infirst column...Sort of a parent id type of thing.
Simplistically, I think this is what he's looking for:
SELECT [second column] FROM table
WHERE [second column] IN (SELECT [first column] FROM table WHERE [second column] = 0)