Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2003
    Location
    OK
    Posts
    10

    Question Unanswered: Where clause problem

    Hi,

    I have a table with some tkt numbers.
    I have another table where I need to pull those records out based on the previous tktno. How can I create the where statement to loop thru the first table to look for each ticket number??

    Thank you for your help,

    Steve

  2. #2
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Re: Where clause problem

    A data sample would help a lot...
    If all the records from the first table can be extracted based on the same condition, then you can use the where clause. If each record must be filtered using a different condition(s) then you'd rather use a cursor (if this is a one time extraction it wouldn't be a problem, otherwise you should reconsider it 'cause a cursor can affect your performance drastically.
    ...waiting for a sample...

    Originally posted by stfarm
    Hi,

    I have a table with some tkt numbers.
    I have another table where I need to pull those records out based on the previous tktno. How can I create the where statement to loop thru the first table to look for each ticket number??

    Thank you for your help,

    Steve
    Steve

  3. #3
    Join Date
    Jul 2003
    Location
    OK
    Posts
    10

    Re: Where clause problem

    Thank you for your answer. A data sample would be:

    M031288
    CUD6698
    M776677
    That would be the ticket number. It is in the tickets table, and I will need to lookup all the numbers from this table. I need to pull detail records out of other tables based on the first one. Please let me know if you need anything else.

    Thank you again,

    Steve



    Originally posted by dbadelphes
    A data sample would help a lot...
    If all the records from the first table can be extracted based on the same condition, then you can use the where clause. If each record must be filtered using a different condition(s) then you'd rather use a cursor (if this is a one time extraction it wouldn't be a problem, otherwise you should reconsider it 'cause a cursor can affect your performance drastically.
    ...waiting for a sample...

  4. #4
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    what do you want to do with the data. If you want to get results displayed from a sql statement, you can also join the two tables on the tkt number from both tables.
    If you want to use the where clause it would be like this:
    select
    columns
    from
    second_table
    where
    tktnr in
    (select
    tktnr
    from
    first_table)
    Johan

  5. #5
    Join Date
    Jul 2003
    Location
    OK
    Posts
    10
    I am sorry, I guess I wasn't very clear.

    I have about 400 records in the tickets table.
    Based on those records (the ticket number), I need to pull out some records from a different table (tickets_detail) for ALL the records in the first table.

    I do not know how to loop thru each ticket number to pull all matched records from the second table.

    Thank you,

    Steve

    Originally posted by jora
    what do you want to do with the data. If you want to get results displayed from a sql statement, you can also join the two tables on the tkt number from both tables.
    If you want to use the where clause it would be like this:
    select
    columns
    from
    second_table
    where
    tktnr in
    (select
    tktnr
    from
    first_table)

  6. #6
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    that part I understood. What do you want to do with it next. Just display them in some application, or do you need to perform some actions. If you want to display them, use one of the two options from my previous post. If not, maybe you can explain what your intention is with the results.
    Johan

  7. #7
    Join Date
    Jul 2003
    Location
    OK
    Posts
    10
    Sorry, looks like I missunderstodd you.

    I will populate a table with the results pulled from the other table.
    I don't really understand how all the tickets will be pulled based on your select statement. How does it loop thru all the records?

    Steve

    Originally posted by jora
    that part I understood. What do you want to do with it next. Just display them in some application, or do you need to perform some actions. If you want to display them, use one of the two options from my previous post. If not, maybe you can explain what your intention is with the results.

  8. #8
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    so you:
    - have one table with ticket numbers (tbNumbers)
    - have one table with ticket details (tbDetails)
    - want to populate a third table with the details from tbDetails based
    on a link to table tbNumbers.

    In that case you can perform the select statement I wrote before.
    select columns_you_want_to_export
    from tbDetails
    where tbDetails.ticket_number in
    (select ticket_number
    from tbNumbers)

    In this statement you will get all records from tbDetails that have a ticket number that occurs in table tbNumbers.
    Johan

  9. #9
    Join Date
    Jul 2003
    Location
    OK
    Posts
    10
    Thank you, I am going to try this now and see if it works. I will let you know.

    Steve

    Originally posted by jora
    so you:
    - have one table with ticket numbers (tbNumbers)
    - have one table with ticket details (tbDetails)
    - want to populate a third table with the details from tbDetails based
    on a link to table tbNumbers.

    In that case you can perform the select statement I wrote before.
    select columns_you_want_to_export
    from tbDetails
    where tbDetails.ticket_number in
    (select ticket_number
    from tbNumbers)

    In this statement you will get all records from tbDetails that have a ticket number that occurs in table tbNumbers.

  10. #10
    Join Date
    Jul 2003
    Location
    OK
    Posts
    10

    Thumbs up

    Thank you again, that worked great

    Steve

    Originally posted by stfarm
    Thank you, I am going to try this now and see if it works. I will let you know.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •