Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Sep 2010
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    need sql to condense request and add an and/or statement

    in sql each pt_type can several different lines of information, each with a different pt_reference

    so word 1 and 2 might be in the same row, but word 3 might be in a different row but have the same "d_id", but if all 3 values are matched, then I want to know the unique "d_id" for each unique matching result.

    in the case below, 1941 and 2991 would be displayed

    is it also to have an and/or type of excel statement? I noted in the query below where I need it.


    THANK YOU


    id d_id pt_type pt_ref pt_value
    3278764 69765 11 999 J0SB
    4658482 67811 11 8 screen
    2892238 47499 0 1 CRL Custom Finish P7 Series 36" End Post Swivel Fitting Railing Kit
    4069940 1941 11 4 this
    2793773 8756 0 1 CRL Black Die Cast Sliding Screen Door Pull With 2-3/8" Screw Holes
    2892239 47500 0 1 CRL Custom Finish P7 Series 36" Corner Post Swivel Fitting Railing Kit
    4161303 14657 11 1 TRANSACTION
    4176252 29539 11 2 railing
    4184049 1941 11 9 that
    4188079 33720 11 3 railing
    2762021 1941 0 1 theother
    2762022 34055 1 1 C1DP42BRZ_34055.gif
    3254121 3236 5 1 Face Diameter::2 in (50.8 mm)
    5001800 31146 16 1 CRL Abrazadera de manguito a presión para cristal, 90 grados, en cobre cepillado antiguo
    2762024 2991 1 2 this is a test
    4191045 2991 11 2 and that is two
    2794393 2991 1 1 other one



    declare @word1 varchar(501)
    declare @word2 varchar(501)
    declare @word3 varchar(501)
    set @word1 = '%this%'
    set @word2 = '%that%'
    set @word3 = '%theother%'
    select
    productheader.ph_id,
    productheader.ph_product
    from productheader
    inner join producttext
    on productheader.ph_id = producttext.pt_productid
    where (producttext.pt_type = '11' and producttext.pt_type = '0')
    --want and and/or type statement on the line right above
    and
    (producttext.pt_value like
    @word1
    and producttext.pt_value like
    @word2
    and producttext.pt_value like
    @word3
    )

  • #2
    New to the CF scene
    Join Date
    Sep 2010
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Please let me clarify
    http://www.crlaurence.com/adv/a-z/sql.gif

    Each pt_id in this table is a unique record.

    Each pt_productid in this table may not be unique, and may have multiple entries

    On the attached Word doc, there are several records with a pt_productid of 67811 and 5817

    I need a query that will return any matching unique pt_productid that matches the one, two, or three word input.

    The input would look at all pt_type's of 0 or 11

    So in this case if I looked for

    stiff elegant railing

    it would return a pt_productid record of 5817

    or a search for screen transaction hardware

    it would return a pt_productid record of 67811

    ---------

    side note: all three searched for words need to be matched, an option for finding two of the three words would also be good if something this complex is possible.


  •  

    Posting Permissions

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