29 Replies Latest reply on Mar 30, 2008 9:48 AM by TiGGi

    Advanced Sarch

    TiGGi Level 1
      Hi,

      I would appreciate some help with a query...
      I have a table with 3 rows companyid, categoryid and categoryType
      This is basically bunch of different category ids in one row and each company gets a set of categoryIDs
      Looks something like this
      companyID CatID CatType
      5225 10 LOC
      5225 12 LOC
      5225 150 TYPE
      5225 214 DUR
      So i need to make a search with different options to find companies that matches these options
      My dilemma is how can I search same row for all these options
      If I do find where (( catID = 12 or catID = 10) and (catType = 'LOC')) then I can't match the other category types since LOC type has already narrow down the results?????
        • 1. Re: Advanced Sarch
          paross1 Level 2
          Well, if you use AND, then each parameter will restrict the result set further. If you use OR, then you will return rows matching any of the parameters.

          If you did this:

          SELECT companyID,CatID,CatType
          FROM your_table
          WHERE CatType = 'LOC'
          OR CatID = 150

          then you would get these rows:
          5225 10 LOC
          5225 12 LOC
          5225 150 TYPE

          but this:

          SELECT companyID,CatID,CatType
          FROM your_table
          WHERE CatType = 'LOC'
          AND CatID = 150

          would return nothing. So, what is it that you want to do? Do you want the results to be "narrowed down" for each parameter, or do you want them to be additive?

          Phil
          • 2. Re: Advanced Sarch
            TiGGi Level 1
            Thanx for the reply,

            what I am trying to get is the companyID

            and if I do something like

            where ((catID = 10 or catID = 14) and (catType = 'LOC'))
            and ((catid= 150 or catid= 151) and (catType = 'TYPE'))
            and it's not working

            my prob is since everything is in one row how do I cross match using the cattype.
            • 3. Advanced Sarch
              TiGGi Level 1
              Thought I had an answer but I don't
              • 4. Re: Advanced Sarch
                coffeedrinker56 Level 1
                Your query is "and"ing all of your requirements together.

                If I've counted my parentheses correctly, this statement should get the data you specified in your original post:

                SELECT companyID
                FROM your_table
                WHERE ((catType = 'LOC') AND ((catID=10) OR (catID=14)) ) OR
                ((catType='TYPE') AND ((catID=150) OR (catID=151))) OR
                ((catType='DUR') AND (catID=214))
                • 5. Re: Advanced Sarch
                  TiGGi Level 1
                  I've already tried that but that gives me full list, I need to be able to cross match between categories like,

                  select employerid where (catid = 1 and catType = 'loc' ) and (catID = 150 and catType = 'type')
                  • 6. Re: Advanced Sarch
                    TiGGi Level 1
                    here's the actual query that I have right now

                    select distinct employerid where ACTIVE = 1 AND ((( CATID = 1 or CATID = 3 or CATID = 17 ) AND (catTYPE = 'cat')) OR (( CATID = 1) AND (catTYPE = 'DUR')) OR (( CATID = 53 or CATID = 54 or CATID = 56 or CATID = 59 or CATID = 61 or CATID = 1589 or CATID = 1590 or CATID = 1591) AND (catTYPE = 'LOC')) )

                    this brings back a list of any of these categories which I don't want. I just want the ones that match all of these categories
                    • 7. Re: Advanced Sarch
                      Dan Bracuk Level 5
                      What is the relationship between catid and cattype?
                      • 8. Re: Advanced Sarch
                        coffeedrinker56 Level 1

                        I've rearranged the order of the criteria to make it more readable.
                        "condition 1" selects cattype='cat' and catid=1,3 or 17
                        "condition 2" selects cattype='DUR' and catid=1
                        "condition 3" selects cattype='LOC' and catid=53,54,56,59,61,1589 or 1590
                        "condition 4" is obvious.

                        ANY row matching "condition 1" OR "condition 2" OR "condition 3" that is active fulfills the criteria. If you really want employerid's that match ALL of these criteria, simply change the "OR ((catTYPE=" TO "AND ((catTYPE=" and leave the other "OR" statements in place.

                        SELECT DISTINCT employerid
                        FROM table_name
                        WHERE

                        condition 1:
                        ((catTYPE='cat') AND ((CATID=1) OR (CATID=3) OR (CATID=17)))

                        condition 2:
                        OR ((catTYPE='DUR') AND (CATID=1))

                        condition 3:
                        OR ((catTYPE='LOC') AND ((CATID=53) OR (CATID=54) OR (CATID=56) OR (CATID=59) OR (CATID=61) OR (CATID=1589) OR (CATID=1590) OR (CATID=1591)))

                        condition 4:
                        AND (ACTIVE=1)
                        • 9. Re: Advanced Sarch
                          paross1 Level 2
                          quote:

                          I just want the ones that match all of these categories
                          I think the point is that there aren't any that match ALL of the categories without including the companyID in the "test". In other words, you want only those rows that match your specified conditions and have the same companyID, which you haven't specified in a WHERE clause.

                          How about something that looks like this?

                          SELECT DISTINCT x.companyID
                          FROM your_table x
                          WHERE x.ACTIVE = 1
                          AND EXISTS (SELECT 1
                          FROM your_table y
                          WHERE y.CATID IN (1, 3, 17)
                          AND y.catTYPE = 'cat'
                          AND y.companyID = x.companyID)
                          AND EXISTS (SELECT 1
                          FROM your_table y
                          WHERE y.CATID = 1
                          AND y.catTYPE = 'DUR'
                          AND y.companyID = x.companyID)
                          AND EXISTS (SELECT 1
                          FROM your_table y
                          WHERE y.CATID IN (53, 54, 56, 59, 61, 1589, 1590, 1591)
                          AND y.catTYPE = 'LOC'
                          AND y.companyID = x.companyID)

                          Each one of the EXISTS sub-selects tests your conditions in addition to ensuring that they must have the same companyID.

                          Phil
                          • 10. Re: Advanced Sarch
                            TiGGi Level 1
                            I think that will work great, thanks allot bro.

                            For the future reference so I make things easier, how should I format database so advanced search like this would be easier and faster????


                            • 11. Advanced Sarch
                              paross1 Level 2
                              I'm assuming that you are already normalized to 3rd normal form. This is one of those special cases where you have a very specific search critera that requires more demanding SQL than you normally would use.

                              Phil
                              • 12. Re: Advanced Sarch
                                TiGGi Level 1
                                but is there a better way to create the set of fields like I have in database so they can be searched easier.

                                Maybe create set of unique id for each category and placing the IDs of all categories into 1 field as comma delimited list?
                                so maybe I have
                                DUR ID's are 101, 102, 103
                                CAT ID's are 201, 202 ..
                                LOC ID's are 500 - 2000

                                and just have one field for each employers that wold have IDs to all categories that this employer matches
                                (101, 103, 205, 504, 795, ...)
                                ????
                                • 13. Re: Advanced Sarch
                                  paross1 Level 2
                                  quote:

                                  .....placing the IDs of all categories into 1 field as comma delimited list?
                                  Oh no, this is a very bad idea. From what you said, are you saying that category is not a separate table with unique CatID values? If so, then your database is NOT in 3rd normal form. You should migrate your categories to a category table, and if there is a many to many relationship between company and category, you should resolve it with a associative entity (link table) between category and company. In other words if a company can be inked to more than one category, and a category can be linked to more than one company, then you should have a table between the two that contains companyID and catID pairs where they link. Then your query should be a snap.

                                  Phil
                                  • 14. Re: Advanced Sarch
                                    TiGGi Level 1
                                    ty again for posting....

                                    well let me start from the beginning...
                                    my employer table has lots of columns and among them i got columns:
                                    employer_loc, employer_cat, employer_dur which have comma delimited list of ID's that correspond to other tables such as Location, Category.
                                    In the process of entering data for employer employer_cat table gets populated also, and this is the table that we were doing the search from.

                                    does this makes sense?
                                    • 15. Advanced Sarch
                                      paross1 Level 2
                                      The statement "...employer_dur which have comma delimited list of ID's..." is a clue to me that you have an improperly normalized data model which may be at the root of most of your troubles. Quite often it is necessary to perform wildly unusual SQL work arounds in order to return desired results when a properly normalized database would vastly simplify the process. If you don't know what normalization is, then I suggest that you do a little research on what you would need to do to normalize your database.

                                      Couple of places you might want to browse:
                                      Relational Databases 101
                                      Introduction to Data Modeling

                                      Phil
                                      • 16. Re: Advanced Sarch
                                        Level 7
                                        TiGGi,
                                        iirc, in at least 3 of your recent threads you have been advised by
                                        numerous people, many of who happen to be experts in the field, to
                                        normalize your database.

                                        just that would make anyone else stop for a second and think carefully
                                        about what they are doing. it's not just pushing air around - there is a
                                        very good reason for this.

                                        but you have been ignoring this advice, instead just repeating your
                                        question about your "comma-delimited list of values in my field" and how
                                        you are having trouble doing this and that with it.
                                        stop. do not make your life any harder. take the time to
                                        a) learn about data modelling and 3rd normal form
                                        b) implement it in your database

                                        there is a book called 'database design for mere mortals' or something
                                        similar to that. get it. read it. keep it next to you at all times until
                                        you know it by heart.

                                        paross1 has given you good instructions on how to arrange your data so
                                        that it conforms to the 3rd normal form. do it.

                                        if you do not, you will find yourself wasting your time trying to do
                                        something that would take just a minute to do in a normalized database.

                                        normalize your db. it's not an option. it's a must.

                                        hth

                                        Azadi Saryev
                                        Sabai-dee.com
                                        http://www.sabai-dee.com/
                                        • 17. Re: Advanced Sarch
                                          TiGGi Level 1
                                          paross1, took your advice and I normalized the dB, I think. I took all those different categories that had comma delimited lists in it and made it's own table which has CompanyID and then the id of category.
                                          So now how do I optimize my query to work with this new setup?

                                          • 18. Re: Advanced Sarch
                                            Level 7
                                            can you please re-post you current query and explain again what output
                                            you want? there are a lot of bits and pieces of code in this thread -
                                            it's hard to put together what you might have now...

                                            Azadi Saryev
                                            Sabai-dee.com
                                            http://www.sabai-dee.com/
                                            • 19. Re: Advanced Sarch
                                              TiGGi Level 1
                                              What I am trying to acomplish is have a user search where user can pick different categories such as CAT, LOC, DUR.. and each of these categories have multiple selections. User save its search and his selections go into tables that have been normalized now. So I have a user table then I have a table for each category with userID and then the value of the each selection. On the other side I have the employer table which has related tables for it's set of categories.

                                              What I need is to take a user saved selections and find employers that match those selections.

                                              Right now what I have is pretty much what paross suggested
                                              SELECT DISTINCT x.companyID
                                              FROM your_table x
                                              WHERE x.ACTIVE = 1
                                              AND EXISTS (SELECT 1
                                              FROM your_table y
                                              WHERE y.CATID IN (1, 3, 17)
                                              AND y.catTYPE = 'cat'
                                              AND y.companyID = x.companyID)
                                              AND EXISTS (SELECT 1
                                              FROM your_table y
                                              WHERE y.CATID = 1
                                              AND y.catTYPE = 'DUR'
                                              AND y.companyID = x.companyID)
                                              AND EXISTS (SELECT 1
                                              FROM your_table y
                                              WHERE y.CATID IN (53, 54, 56, 59, 61, 1589, 1590, 1591)
                                              AND y.catTYPE = 'LOC'
                                              AND y.companyID = x.companyID)
                                              • 20. Re: Advanced Sarch
                                                Level 7
                                                from your query sample it still looks to me like you database is not
                                                normalized... can you post your table structure for al tables involved
                                                in your query?

                                                Azadi Saryev
                                                Sabai-dee.com
                                                http://www.sabai-dee.com/
                                                • 21. Re: Advanced Sarch
                                                  sadbjk453o
                                                  Yeah like Azadi says...only if you could just post your entire table structure...we could possibly understand what you have...so that we could help you out here.

                                                  sadb,
                                                  http://RapidshareFilms.com

                                                  • 22. Re: Advanced Sarch
                                                    TiGGi Level 1
                                                    The query that I posted is the one I used before the normalizing tables. Now that the tables are normalized I need help with the new query.

                                                    I don't know if there's an easy way to get tables structure but this is the basic layout:

                                                    [dbo].[Users]
                                                    UserID(int), UserMail,UserPassword....

                                                    [dbo].[User_LOC]
                                                    UserID(int), LOC(int)

                                                    [dbo].[User_CAT]
                                                    UserID(int), CAT(int)

                                                    [dbo].[User_DUR]
                                                    UserID(int), DUR(int)

                                                    Then I have the employer tables:

                                                    [dbo].[employer]
                                                    employerID(int), ....

                                                    [dbo].[employer_LOC]
                                                    employerID(int), LOC(int)

                                                    [dbo].[employer_CAT]
                                                    employerID(int), CAT(int)

                                                    [dbo].[employer_DUR]
                                                    employerID(int), DUR(int)


                                                    so for each user I need to take the values in User_LOC, User_CAT, User_DUR and find a match in the employer_LOC, employer_CAT, employer_DUR and return the employerID as result
                                                    • 23. Advanced Sarch
                                                      paross1 Level 2
                                                      Given your current structure, what does this query give you?

                                                      SELECT DISTINCT e.employerID, u.UserID
                                                      FROM employer e
                                                      INNER JOIN employer_CAT ec ON e.employerID = ec.employerID
                                                      INNER JOIN User_CAT uc ON ec.CAT = uc.CAT
                                                      INNER JOIN employer_LOC el ON e.employerID = el.employerID
                                                      INNER JOIN User_LOC ul ON el.LOC = ul.LOC
                                                      INNER JOIN employer_DUR ed ON e.employerID = ed.employerID
                                                      INNER JOIN User_DUR ud ON ed.DUR = ud.DUR
                                                      INNER JOIN Users u ON uc.UserID = u.UserID
                                                      AND ul.UserID = u.UserID
                                                      AND ud.UserID = u.UserID

                                                      NOTE: You would design your WHERE clause to specify the particular combinations of values for CAT, LOC, and DUR if necessary.

                                                      Phil
                                                      • 24. Re: Advanced Sarch
                                                        TiGGi Level 1
                                                        Hey paross, thanks for your help again...
                                                        in the SELECT DISTINCT e.employerID, u.UserID
                                                        FROM employer e
                                                        don't I need the USER table in FROM also?
                                                        • 25. Advanced Sarch
                                                          paross1 Level 2
                                                          It is... it is INNER JOINed ...

                                                          ....
                                                          INNER JOIN Users u ON uc.UserID = u.UserID
                                                          AND ul.UserID = u.UserID
                                                          AND ud.UserID = u.UserID


                                                          Phil
                                                          • 26. Advanced Sarch
                                                            paross1 Level 2
                                                            I suppose this equivalent would do the same thing.

                                                            SELECT DISTINCT e.employerID, u.UserID
                                                            FROM employer e, employer_CAT ec, User_CAT uc, employer_LOC el, User_LOC ul, employer_DUR ed, User_DUR ud, Users u
                                                            WHERE e.employerID = ec.employerID
                                                            AND ec.CAT = uc.CAT
                                                            AND e.employerID = el.employerID
                                                            AND el.LOC = ul.LOC
                                                            AND e.employerID = ed.employerID
                                                            AND ed.DUR = ud.DUR
                                                            AND uc.UserID = u.UserID
                                                            AND ul.UserID = u.UserID
                                                            AND ud.UserID = u.UserID

                                                            Phil
                                                            • 27. Re: Advanced Sarch
                                                              TiGGi Level 1
                                                              Hey Phil, thank you again for your help. Can I bother you once more?
                                                              I am gonna use the query you suggested last, I was wondering what happens if user hasn't selected one of the categories? Isn't this going to bring back incorrect result?


                                                              quote:

                                                              Originally posted by: paross1
                                                              I suppose this equivalent would do the same thing.

                                                              SELECT DISTINCT e.employerID, u.UserID
                                                              FROM employer e, employer_CAT ec, User_CAT uc, employer_LOC el, User_LOC ul, employer_DUR ed, User_DUR ud, Users u
                                                              WHERE e.employerID = ec.employerID
                                                              AND ec.CAT = uc.CAT
                                                              AND e.employerID = el.employerID
                                                              AND el.LOC = ul.LOC
                                                              AND e.employerID = ed.employerID
                                                              AND ed.DUR = ud.DUR
                                                              AND uc.UserID = u.UserID
                                                              AND ul.UserID = u.UserID
                                                              AND ud.UserID = u.UserID

                                                              Phil


                                                              • 28. Re: Advanced Sarch
                                                                paross1 Level 2
                                                                All of the conditions in the query would have to be "true" to return a given row. If you restrict the values further in the where clause with specific values of DUR, CAT, LOC, or UserID, then you would get even fewer "matches". However, the maximum number of rows would be returned where all of the existing conditions in the where clause are met.

                                                                I don't know how else to answer your question at this point.

                                                                Phil
                                                                • 29. Re: Advanced Sarch
                                                                  TiGGi Level 1
                                                                  Thanks Phil,

                                                                  So do I need to loop through every user and check if they have something selected for each category, and then based on that I include the condition?