Join Question

Results 1 to 3 of 3

Thread: Join Question

  1. #1
    Join Date
    Dec 1969

    Default Join Question

    Im working on an ecommerce site and am having a problem with a search feature that the company wants. There are 3 tables involved, tblProducts, tblDepartments, and tblDepartmentIndex. They look like:<BR><BR>tblProducts<BR>-----------<BR>ID - int<BR>Name - varchar<BR>Description - text<BR>etc, etc<BR><BR>tblDepartments<BR>--------------<BR>ID - int<BR>Name - varchar<BR><BR>tblDepartmentIndex<BR>------------------<BR>ProductID - int<BR>DeptID - int<BR><BR>For each department that a product belongs to a row in tblDepartmentIndex exists, so if a product with ID 100 is in departments with id&#039;s 4,5, and 6 there would be 3 rows in tblDepartmentIndex... the problem is the client wants to keywords in the search to not only look in the products table but also find similarities in the department table and return all the products associated with that department. I can think of a way to do this but only if it uses 2 separate sql statements and a weird loop, any ideas on getting it done all at once?

  2. #2
    Join Date
    Dec 1969

    Default RE: Join Question

    I&#039;m assuming you know which field you want ot search on, then just make your SQL like this,<BR><BR>select [list of field to return]<BR>from tblProducts p<BR>join tblDepartmentIndex di<BR>on p.ID = di.ProductID<BR>join tblDepartments d<BR>on d.ID = di.DeptID<BR>where<BR>[all criterias using LIKE or = to satisfy the search values send by the user on all the fields in tblProducts and tblDepartments]

  3. #3
    Join Date
    Dec 1969

    Default Try this, gives me results!!

    select, from dept_indx a, prod b where a.did = (select c.did from dept_indx c where = 1 )<BR>and =;<BR><BR><BR>here is the table structure that i have:<BR><BR>prod:<BR><BR>id number(4)<BR>name varchar<BR><BR>dept_temp:<BR><BR>did number(4)<BR>name varchar<BR><BR>dept_indx:<BR>id number(4)<BR>did number(4)<BR><BR>based on these 3 tables the above sql gave me the results that you might be looking for..<BR><BR>try and see...

Posting Permissions

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