A tricky select question

[docmattman]

Here's the basic scenerio.  I have a table of items.  This table has several fields that refer to other tables values (I use table JOINs for this).  Let's say I have the following:

 

 

Table: items

-------------------------

id    title    type_id    brand_id    dept_id

1    "Item 1"    1    1    2

2    "Item 2"    2    1    1

3    "Item 3"    1    3    1

-------------------------

 

 

Table: lists

-------------------------

id    list_id    list_title    list_item_key    list_item_val

1    1    "Brand List: Type 1"    0    "Sony"

2    1    "Brand List: Type 1"    1    "Philips"

3    1    "Brand List: Type 1"    2    "LG"

4    1    "Brand List: Type 1"    3    "Samsung"

5    2    "Brand List: Type 2"    0    "Old Navy"

6    2    "Brand List: Type 2"    1    "Abercrombie"

7    2    "Brand List: Type 2"    2    "Hanes"

8    2    "Brand List: Type 2"    3    "Armani"

9    3    "Depatment"    0    "Clothing"

10    3    "Department"    1    "Electronics"

11    3    "Department"    2    "Houseware"

-------------------------

 

 

Now I want to select the "list_item_val" value from the "lists" table for items.  But this value is dependent on the "type_id" of the item in the "items" table.  So I have something like this.  So if I wanted the brand for "Item 1" I would need to pull it from the items in the "lists" table that have a "list_id" of 1.  If I wanted the brand for "Item 2" I would need to pull it from the items in the "lists" table that have a "list_id" of 2.  In the "items" table above, the deparment will always be pulled for items of "list_id" 3.  My problem is creating a query that gets data (JOINed) from both tables, when the brand is dependent on the "type_id" of the item.  Here is what I have so far:

 

SELECT items.id, items.title, tmpTbl1.list_item_val AS brandName, tmpTbl2.list_item_val AS deptName

FROM items

LEFT JOIN lists AS tmpTbl1 ON items.brand_id=tmpTbl1.list_item_key

LEFT JOIN lists AS tmpTbl2 ON items.dept_id=tmpTbl2.list_item_key

 

As you can see, the query above doesn't make any checks for the "type_id" value when getting the "brandName".  This is the part I need to fill in to make it work.  I do know the relationship between the "type_id" and the "list_id" for the brands.  They go like this:

 

type_id    =>    list_id

-------------------------

1    =>    1

2    =>    2

3    =>    6

4    =>    11

 

Can someone help me complete the query about to get the results I'm looking for?

 

[quiettech]

It took me a while, but I seem to have gotten the hang of your post.

 

Ok... you really should be asking instead is how you can change that database design. Because, my friend, it is flawed

 

Unless you have a really good reason to do things that way - hard to convince me, I must tell you -  you should instead design your database by taking full advantage of the fact it is being done on a relational DBMS.

 

So...

 

You need a table for Departments and a table for Brands.

 

Table Items: ID, title, brandID, depID

Table Departments: ID, depName

Table Brands: ID, brandName

 

You create the relationships with the help of foreign key constraints. For instance:

 

ALTER TABLE `Items`
  ADD CONSTRAINT `items_depfk_id`
  FOREIGN KEY (`depID`)
    REFERENCES `Departaments`(`ID`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE;

 

And you do the same for brandID.

 

Now it becomes much easier (and quicker) to do your joins and get the relevant data