How do I write a query in SQL syntax that allows me to call out a table twice in the statement

wasedu2
wasedu2 used Ask the Experts™
on
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.  
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:

Commented:
1 . Use inner join / equi join

 2. what kind of database  are you using??

3. if this is oracle

select * from tab1 A ,tab2  B where A.primarycol = B.forenkeycol

4. Sql server /Ansi

select * from tabl1 t1 inner join tab2 t2 on t1.primarykeycol = t2.forenkeycol

5. follow the above links for more info..
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

You can join one table several times, you just have to use different local aliases:

SELECT t.*, t1.colX, t2.colY
  FROM YourTable t
   INNER JOIN JoinTable t1 ON t1.JoinCol = t.JoinCol AND t1.Attribute = 'A'
   INNER JOIN JoinTable t2 ON t2.JoinCol = t.JoinCol2 AND t2.Attribute = 'B'

or

SELECT t.*, t1.colX, t2.colY
  FROM YourTable t, JoinTable t1, JoinTable t2
 WHERE t1.JoinCol = t.JoinCol AND t1.Attribute = 'A'
    AND t2.JoinCol = t.JoinCol2 AND t2.Attribute = 'B'
You can even join table to itself. It is used in cases where you need to list employees and managers from one table:

SELECT e.ID, e.Last_Name, e.First_Name, e.ManagerID, m.Last_Name, m.First_Name
  FROM Employee e, Employee m
  WHERE e.ManagerID = m.ID

And I agree, above SQL links should tell more.

Commented:
strange!!...you said two tables in your question and self join is for one table ..you must equally distribute the points for those who provide the links and more info..seems like unfair to me.. anyways ..glad if you are answered

Author

Commented:
Sorry, somewhat new to the points distribution.  I will keep that in mind next time.
I would say only the solution ID:26426613 is showing how to join one table twice with different attribute. Such solution is not mentioned in above links, so it was the main reason why I've been posting it. To select different answer from the same author does not mean problem.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial