Post reply

Need help with a WHERE statement

  • 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 8:31 am

    #1277240

    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

  • January 24, 2011 at 8:32 am

    #1277242

    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

  • January 24, 2011 at 8:36 am

    #1277248

    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

  • January 24, 2011 at 8:38 am

    #1277252

    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.

  • January 24, 2011 at 8:41 am

    #1277256

    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

  • January 24, 2011 at 8:54 am

    #1277261

    Heres some sample Data:

    661766 RAQA

    670425 TAC

    670425 RAQA

    674365 RAQA

    682568 RAQA

    682568 TAC

    682568 TAC

    682568 TAC

    682568 TAC

    682615 RAQA

    682615 TAC

    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!

  • January 24, 2011 at 8:56 am

    #1277263

    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

  • January 24, 2011 at 9:01 am

    #1277268

    <----- REEEEEEEEEEEALLLy new 🙂

    What exactly does that mean?

  • January 24, 2011 at 9:54 am

    #1277314

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

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

  • January 24, 2011 at 11:17 pm

    This was removed by the editor as SPAM

  • January 26, 2011 at 6:18 pm

    #1278888

    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.

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

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