Common Identifying Column, Different Date

[Geno521]

I am attempting to write an SQL which will return all instances of a certain column matching but which was posted at a different time.

 

SELECT tbl.cusip, date, color, price, FROM tbl INNER JOIN (SELECT name FROM name GROUP BY name HAVING count(name) > 1) dup ON tbl.name = dup.name;

 

That query will spit back all instances of entries with duplicates of the column "name" (which is what I want) but seems to break if I try to insert a WHERE clause such as, WHERE date > '2012-02-01' AND color='blue'.  So I'm basically stuck to viewing every single instance of a particular entry instead of only seeing the past month's.  Can anyone help me out? Thank you.

[requinix]

What query did you try?

[Geno521]

I've basically tried inserting that WHERE clause in every conceivable spot on the query. Let me explain how it breaks if I insert it at the end of the query. If I change it to:

 

SELECT tbl.cusip, date, color, price, FROM tbl INNER JOIN (SELECT name FROM name GROUP BY name HAVING count(name) > 1) dup ON tbl.name = dup.name WHERE date>'2013-02'01';

 

The query is just giving me a list of all distinct names that have occurred since that date.

 

 

If I insert it into the INNER JOIN's (), like this:  

 

SELECT tbl.cusip, date, color, price, FROM tbl INNER JOIN (SELECT name FROM name WHERE date>'2013-02'01' GROUP BY name HAVING count(name) > 1) dup ON tbl.name = dup.name;  

 

This query works, spitting out the duplicates I want to see, but it won't restrict the date. It's still giving me rows from the entire database.

[requinix]

If neither of those do what you want (I thought you might have a syntax error) then what do you want? The couple options I can think of should be answered by one of those queries.

[Jessica]

You keep doing '2013-02'01'

 

Which should in fact create a syntax error.

[Geno521]

I want the output to list every instance in which a name has been inserted more than 1 time within a particular time frame. Neither of those queries will give a syntax error. Anything that looks like it would is a typo.

[mikosiko]

The column "date" is present in both tables by chance?

[requinix]

Then try having two of those date comparisons, one in each place. The innermost query will then find duplicate names within your time period while the outermost query will find matching records with those names... within your time period.

[Geno521]

Thank you requinix, that seems to have solved the problem.