Use joins? or subquery? or ?

[mrrrl]

I have a table that has account_id, visit_type, encounter_id and others fields. The same account_id can have more than one type of visit, but with a different encounter_id. I am trying to build a query that will search all the orginal table for all account_id's that have a visit_type of 1. Then I need to search the resulting account_id's to see if they also have a visit_type of 2 in the orginal table.

So is there a way to do a search and build a temp table, then take the account_id from the temp table and do another search on the orginal table and then combine the results where the two temp tables match?

I was looking at joins, but they seem to use two orginal tables and joining these to get the results, where I have one table to work from.

TIA

 

[vidru]

There are several different ways to do this. Here's one:

SELECT A.account_id
FROM tablename A
WHERE A.visit_type = 1 
AND EXISTS(SELECT * FROM tablename WHERE account_id = A.account_id AND visit_type = 2)

-dave

 

[oj75]

I wouldn't use SELECT * within the EXISTS clause. The WHERE clause within the SELECT statement of the EXISTS clause is the important part.

OJ
DB/.Net/CR Developer

 

[mrrrl]

I can't get this to work as it the above example looks like it is for two seperate tables.

SELECT A.account_id
FROM tablename A
WHERE A.visit_type = 1
AND EXISTS(SELECT * FROM tablename WHERE account_id = A.account_id AND visit_type = 2)

I am working with one table, but doing two searches the first to find all the account_id's that have visit_type that equals 1 and then take those account_id's and see if they also have visit_type that equals 2.

So it seem that the first search would have to build a temp table from the orginal table for the second search to use just those account_id's to search the orginal table.

 

[mrrrl]

OK I got it. I used a self join and used a alias for the table to make it look like two tables.