Need help with a WHERE statement

Question

Post reply

Need help with a WHERE statement

davemcsheffrey

SSC Enthusiast

Points: 158
More actions
January 24, 2011 at 8:14 am

#244175

Here is my current SQL statement:
SELECT
a.request_id,
a.req_type,
a.status,
b.event_type,
b.person_id_from,
a.check_for_billing,
a.billing_allowed,
c.text_line_code
FROM
Request a left outer join request_event b on a.request_id = b.request_id left outer join request_text c on a.request_id = c.request_id
WHERE
a.req_type in ('auto', 'tac')
and a.status <> 'cl'
and a.status <> 'ca'
and b.event_type = 'open'
and c.text_line_code <> 'RAQA'
ORDER BY
b.person_id_from,
a.request_id
The result I am getting back brings the information but not exactly what I am looking for. What I want and I am sure I am not writing it correctly is that when on table c contains a text_line_code of "RAQA" I do not want the report to report back with an request_id. What I am getting is that the report will bring back multiple lines of the same RID that do not contain the text line code of RAQA. Can someone point me in the right direction on how to write this correctly?
Thanks again you guys are so very helpful to me who is just starting out using SQL.
January 24, 2011 at 8:22 am

This was removed by the editor as SPAM
January 24, 2011 at 11:17 pm

This was removed by the editor as SPAM

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

davemcsheffrey SSC Enthusiast Points: 158 More actions · Answer 1

I tried that but now I am not getting any results coming back.

SELECT

a.request_id,

a.req_type,

a.status,

b.event_type,

b.person_id_from,

a.check_for_billing,

a.billing_allowed,

c.text_line_code

FROM

Request a left outer join request_event b on a.request_id = b.request_id left outer join request_text c on a.request_id = c.request_id

WHERE

a.req_type in ('auto', 'tac')

and a.status <> 'cl'

and a.status <> 'ca'

and b.event_type = 'open'

--and c.text_line_code <> 'RAQA'

and NOT EXISTS (SELECT d.request_id FROM request_text d WHERE d.text_line_code = 'RAQA')

ORDER BY

b.person_id_from,

a.request_id

koustav_1982 SSCommitted Points: 1788 More actions · Answer 2

try this as well

SELECT

a.request_id,

a.req_type,

a.status,

b.event_type,

b.person_id_from,

a.check_for_billing,

a.billing_allowed,

c.text_line_code

FROM

Request a left outer join request_event b on a.request_id = b.request_id left outer join request_text c on a.request_id = c.request_id and c.text_line_code <> 'RAQA'

WHERE

a.req_type in ('auto', 'tac')

and a.status <> 'cl'

and a.status <> 'ca'

and b.event_type = 'open'

ORDER BY

b.person_id_from,

a.request_id

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Whichever way you do it, this line:

and b.event_type = 'open'

needs to come out of the WHERE block and go into the join predicate. Otherwise, your left outer join will turn into an inner join since anything from b with NULLs in it will be eliminated. I hope I've explained that well!

John

davemcsheffrey SSC Enthusiast Points: 158 More actions · Answer 4

Thanks for the try but that too is not working. Its giving me the same data where it will bring up a request id multiple different times as each request id may have multiple text line codes.

However what I need the report to do is bring back only distinct request ids that do not have the text line code of RAQA.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

I think we've come to the point where we need some sample data. Please provide table DDL in the form of CREATE TABLE statements, data in the form of INSERT statements, and the result set you expect to be returned.

Thanks

John

davemcsheffrey SSC Enthusiast Points: 158 More actions · Answer 6

Heres some sample Data:

661766 RAQA

670425 TAC

670425 RAQA

674365 RAQA

682568 RAQA

682568 TAC

682615 RAQA

682615 TAC

So for example in request 682568 you can see it has 5 text line codes associated with it. What I want the report to do is when it sees an 'RAQA' text line code I do not want it to appear in my search results. Further more I only want to see the RID come up once as well. So for example with request 682614 which would qualify for my search findings but I only want the request to come up once and not for every text line code that is available.

Hope this helps thanks for the help so far!

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

CREATE TABLE and INSERT statements, please - for each of the three tables. If you want people to help you, you need to make it easy for them.

Thanks

John

davemcsheffrey SSC Enthusiast Points: 158 More actions · Answer 8

<----- REEEEEEEEEEEALLLy new 🙂

What exactly does that mean?

JackIntheBox SSCrazy Points: 2022 More actions · Answer 9

Follow this article and it should be able to guide you.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

david.watkins SSC Journeyman Points: 93 More actions · Answer 10

The problem is that you are doing a left outer join to REQUEST_TEXT.

If you do an inner join, the condition (WHERE TEXT_LINE_CODE <> 'RAQA') will result in these lines being excluded.

A SELECT DISTINCT will drop duplicates.