I am trying to write a query statement, and I'm not sure how to do (or if it can be done...) Here's the scenario:
I have two tables, one is the primary table that I'm trying to extract records from. The other one is an 'attribute' table that maps attributes to the primary records. There can be multiple attribute records for one primary record (and there are all of the mapping key columns, etc). I want to extract records from table 1 that have BOTH an attribute "A" record AND an attribute "B" record in table 2.
I don't think I can do this with a standard select * from table1, table2 where........ table2.attribute="A" AND table2.attribute="B". Unless I'm missing something, that will never work since I'm basically saying one attribute is two different values.
I know I could make it work by cloning table2 and using all three tables in the query: select * from table1, table2, table2clone where........ table2.attribute="A" AND table2clone.attribute="B"
But that seems incredibly inefficient, plus I have to maintain the clone to make sure it is identical with the original.
What I'm really looking for is a syntax that allows me to call out a table twice in the statement similar to a clone. Is there something in SQL that allows me to do something like: select * from table1, table2, table2[again] where........ table2.attribute="A" AND table2[again].attribute="B"
Does this make any sense? I gotta believe there is some way in SQL to do this. But I don't know the terminology enough to know how to google search it.