January 28, 2015 at 11:42 pm
Hello! I have bit of a problem I have encountered. I have two tables, one with data for one type of ID (call it key1) and a table where this ID (call it key2) is transformed to another. It is not one-to-one match with these types of ID and I want to check those key2 cases that have two or more key1 linked to it.
It is simple enough and for the easiest check I don't even need table1 to run it as table2 has both key1 and key2 variables.
However, not all doubles are of identical worth. Table1 (that has only key1) has a year variable. I am interested in doubles that have same year variable, ie. in table1 there are two key1 cases with the same year variable that are linked to one key2 case in table2.
So in essence in table1 I have key1, year and in table2 I have key1, key2 and I am interested in those key2-cases that have more than one key1 linked to it where years are the same.
SELECT query.key2
FROM (
SELECT DISTINCT a.key1, b.key2
FROM table1 AS a JOIN table2 AS b ON a.key1=b.key1 JOIN table1 AS c ON
a.key1=c.key1
WHERE a.year=c.year)
AS query
GROUP BY query.key2
HAVING COUNT(*)>1
I tried it joining table1 twice and fiddling around with various JOIN and WHERE clauses (the one on show being the simplest and most naive one) but the query still returns key2-doubles whose key1 cases are linked to different years. It is simple enough if you give a distinct year value in where clause (and drop second table1 join as unnecessary) but I don't want to go through all years manually one by one. I was thinking some kind of iterative loop that changes the value of the year in where clause could do the trick (and be heavy computationally) but I don't really know how to go around doing it, haven't done any loops in SQL ever. (and what little Java I've done was couple years ago, forgotten most of it)
edit: could be this is the wrong subsection. Feel free to move! 😀
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply