SELECT statement in SELECT statement?

[rookiesql]

Hi!
I am trying to extract data from a Problem DB in SQL. The Problems in the database pass through different states as they are treated. The states are named and there is an associated time stamp for each state. In my query I want to SELECT the Problem id and then the time stamp for 3 different states, i.e. I want there to be only one resulting record for each problem id and the associated time stamps in 3 separate columns.
I can't figure out how to write an SQL statement to acheive this (maybe it is not even possible). I made some attempts in Crystal SQL Designer, which allowed me to put together a query string (it had a 3 SELECT statement inside the main SELECT statement) but when the query was run I get ODBC SQL Server Driver Error saying error close to the 2nd(?) SELECT statement.
Maybe I am complicating things, but is there anyone who could lead me on the right path it would be appreciated!!
Regards
Anders

 

[cheerio]

You can have one select where you join the table to itself. The gist of it would be something like

SELECT A.*, B.*, C.*
FROM
PROBLEMS A
JOIN
PROBLEMS B
ON A.ID = B.ID
AND B.STATE = 1
JOIN
PROBLEMS C
ON A.ID=C.ID
AND C.STATE=2
WHERE A.STATE=0

IF STATE 0 could exist without the others you replace each JOIN with a LEFT JOIN.

I have assumed that the ID and STATE uniquely identify a row.

 

[skypanther]

Are these different problem states being stored in separate tables or all in one? Why can't you just do this?

SELECT problemID, state1timestamp, state2timestamp, state3timestamp
FROM problemsTable
WHERE problemID = someID

If this won't work, please post a description of your table(s) and the SQL command that didn't work so that I can see what's going on.

Tim

Skypanther Studios

http://www.skypanther.com

 

[rookiesql]

Hi!
Wow very fast replies! Sorry I was not specific, but I am a SQL rookie. Let me try to be more specific:
What I can easily get with a standard query is a result like this:

PROBLEMID State time_stamp
#1 start 2003-04-01
#1 finish 2003-05-23
#1 close 2003-06-14

...but what I would like to get out is:

PROBLEMID t_start t_finish t_close
#1 2003-04-01 2003-05-23 2003-06-14

...and t_start,t_finish, t_close are not field existing in the database
Does this make my problem more understandable?
I really appreciate the help and I am amazed over the response time! Thanks!

 

[swampBoogie]

select problemid, 
max(case when state = 'start' then time_stamp end)
 as t_start,
max(case when state = 'finish' then time_stamp end)
 as t_finish,
max(case when state = 'close' then time_stamp end) 
as t_close 
 from t
group by problemid

You never stated which DBMS you are using, so you may need to modify the statement accordingly.

 

[rookiesql]

Hi again,
Back again in the forum after attending to some other stuff. I did try the construction suggested by swampBoggie (having a case statement within a max function in the select statement), but it does not seem to work on my system. swampBoggie wrote "You never stated which DBMS you are using, so you may need to modify the statement accordingly." As a rookie in sql I am not sure what DBMS means, but if it is my system, then I beleive we are running on Microsoft SQL server 2000.
Again, I would be really grateful to find a solution to my problem.

 

[swampBoogie]

The syntax I used should work in SQL server. What problem do you encounter? Just saying "does not work" is not very helpful.

 

[rookiesql]

Sorry for my phrasing. Indeed your suggested syntax did work!! Thanks very much!
I have been iterating my SQL statement using MS Query, which is the tool at hand. The resulting statement is like this:

SELECT T1.id, T1.submit_date, T1.severity, T1.verification,
MAX(case when T2.action_name='Followed_up' then T2.action_timestamp end) ,
MAX(case when T2.action_name='Finish' then T2.action_timestamp end) ,
MAX(case when T2.action_name='Close' then T2.action_timestamp end) ,
T1.patch_revision
FROM DB.Defect T1, DB.history T2
WHERE T1.dbid = T2.entity_dbid AND 16777224 = T2.entitydef_id AND (T1.dbid<>0) AND (T1.product_project In ('Project 1'))
GROUP BY T1.id, T1.submit_date, T1.severity, T1.verification,T1.patch_revision
ORDER BY T1.submit_date

This gives indeed the data that I want, there are 2 things I don't understand:
1. If I write... MAX(case when T2.action_name='Finish' then T2.action_timestamp end) as t_finish ... then MS Query seems to truncate the &quot;as t-finish&quot; and I don't get any column heading. Not a big problem but I am qurious.
2. When iterating the statement I found that I must have all the columns in the SELECT statement also in the GROUP statement (except the &quot;MAX(...&quot otherwise I would get the error &quot;Incorrect coulumn expression: 'MAX(case when T2.action_name='Followed_up' then T2.action_timestamp end)' and I don't grasp why (probably because I am a very beginner in SQL)