SQL Query in Management Studio (1 Viewer)

[cfp76]

Not sure if this 100% the right board to post this question BUT...

I have 1 table that has multiple entries for the ID. Here is a general example:

ID Event
001 Application Started
001 Application Printed
002 Application Started
002 Application Printed
002 Application Submitted
003 Application Started
003 Application Printed
003 Application Submitted

What I am trying to do in my query in Management Studio is get it to select the IDs where there is an event for printed AND submitted

When writing my queries in SPUFI, I can use a join and trick it into joining the table to itself by ID#; however, the query in Management Studio will not permit it. It puts a red ~ under the table declared in the FROM statement.

This is kinda what it looks like

SELECT DISTINCT ID, {other field}, {other field}, ....

FROM DATABASE.TABLENAME A

JOIN DATABASE.TABLENAME B ON A.ID = B.ID

WHERE A.EVENT like '%print%' and B. Event like '%submit%'


I am sorry I cannot provide the actual query I've written (confidentiality and all)

In the JOIN statement, the "A.ID" has a red squiggle under it and I'm getting the error: The multi-part identifier "AL.QuoteId" could not be bound.

Thanks in advance for any help given. I appreciate it!

 

[Guus2005]

Create a query (query1) to select all records where the event is "printed"

select * table1 where event = "printed"

Create another query (query2) to select all records where the event is "submitted"

select * table1 where event = "submitted"

Create a third query (query3) to join query1 and query2

select distinct query1.ID query1 inner join query2 on query1.ID = query2.ID

Enjoy!