Conditional SELECT statement

[xsw1971]

I have a single table that captures the working time of 3 different groups of people: A, B and C. For any given project, I always want to select the time for the A and B groups, but I only want to report the C group time if time exists for either the A or B group. Confused? Good, so am I!

Sample Data:

PROJ_ID GROUP HOURS
1111 A 10
1111 C 12
2222 B 5
3333 C 8

In this case, I want the query to return 1111 and 2222 only. Since neither A nor B exists for 3333, it should not be returned by the query.

Please help!! THANKS!

Jennie

 

[swampBoogie]

select * from t t1
where group in ('A','B')
or ( group = 'C'
and exists ( select * from t
where proj_id = t1.proj_id
and group in ('A','B'))


Group is not an apropriate column name as it is a reserved word in SQL.

 

[rrrkrishnan]

Try this.

select * from test a
where grp <> 'C'
or exists (select * from test b
where grp in ('A','B')
and b.proj_id=a.proj_id)

Krishnan

 

[xsw1971]

Thank you for your suggestions; they were very helpful, but it doesn't work for me.

For the purposes of this posting, I simplified my query to a single table with dummy column names (GROUP is not one of my column names.) I actually have 4 tables with several AND statements; I am having difficulty applying your suggestions.

It's a good start, though, and I will keep plugging away at it.

Thanks,
Jennie

 

[fluteplr]

You know, you could just post the actual table schemas and the actual query. Simplifying the question is great but not when you simplify it so it is no longer useful.

I don't know about the others and I am late getting into this thread but personally it always annoys me when I spend my time trying to answer a question only to be told that my answer did not work due to x,y and z which were left out of the question in the first place.

 

[PruSQLer]

flutepir,

Keep in mind that it sometimes takes several shots and angles to arrive at a solution to a problem. I don't always ask a question that knifes right to the heart of a quandary. But hopefully coworkers can jump in and supply what's needed. That's part of the problem solving process. That being said, I understand the frustration that's felt when certain clues are not disclosed in the hunt for a resolution. But, it's all part of the effort.

 

[xsw1971]

I apologize to anyone annoyed by simplification, that was certainly not my intent. I personally find it annoying when people post lines and lines of rambling code, when the heart of their problem lies in 1 or 2 lines; I was trying to make the concept easier to understand.

Regardless, I have posted 2 queries below.

QUERY 1:
SELECT DISTINCT t.gr_id
FROM time_entry t
, gl_requests g
, gl_engr_staff e
WHERE t.gr_id = g.gr_id
AND e.empl_id = t.person_id
AND g.billing_type = 'I'
AND t.date_worked BETWEEN '01-Dec-02' AND '31-Dec-02'
AND t.gr_id != 1000

QUERY 2:
SELECT DISTINCT t.gr_id
FROM time_entry t
, gl_requests g
, gl_projectlead p
WHERE t.gr_id = g.gr_id
AND p.empl_id = t.person_id
AND g.billing_type = 'I'
AND t.date_worked BETWEEN '01-Dec-02' AND '31-Dec-02'
AND t.gr_id != 1000

As described in my initial posting, I always want to report from query 1, but I only want to report the items from query 2 where items exist in query 1 for the same project (gr_id).

As always, constructive feedback is greatly appreciated.

Thank you.

 

[rrrkrishnan]

Try this if for very engg_staff there is exists a project lead.

SELECT DISTINCT t.gr_id
FROM time_entry t
, gl_requests g
, gl_engr_staff e
, gl_projectlead p
WHERE t.gr_id = g.gr_id
AND e.empl_id = t.person_id
AND p.empl_id = e.empl_id
AND g.billing_type = 'I'
AND t.date_worked BETWEEN '01-Dec-02' AND '31-Dec-02'
AND t.gr_id != 1000
AND exists (select * from time_entry t1
, gl_requests g
, gl_engr_staff e
WHERE t1.gr_id = g.gr_id
AND e.empl_id = t.person_id
AND g.billing_type = 'I'
AND t1.date_worked BETWEEN '01-Dec-02' AND '31-Dec-02'
AND t1.gr_id = t.gr_id)

If project lead doesn't exist for all engg_staff try this.

SELECT DISTINCT t.gr_id
FROM time_entry t
, gl_requests g
, gl_engr_staff e left outer join gl_projectlead p on
p.empl_id = e.empl_id
WHERE t.gr_id = g.gr_id
AND e.empl_id = t.person_id
AND AND g.billing_type = 'I'
AND t.date_worked BETWEEN '01-Dec-02' AND '31-Dec-02'
AND t.gr_id != 1000
AND exists (select * from time_entry t1
, gl_requests g
, gl_engr_staff e
WHERE t1.gr_id = g.gr_id
AND e.empl_id = t.person_id
AND g.billing_type = 'I'
AND t1.date_worked BETWEEN '01-Dec-02' AND '31-Dec-02'
AND t1.gr_id = t.gr_id)