I have the following tables:
- Category table which has an ID column, a description column, and category parent ID columns as follows:
cat_id | cat_description | cat_pid -------+-----------------+-------- 1 | State | 0 2 | Texas | 1 3 | California | 1 4 | Title | 0 5 | Engineer | 4 6 | Lawyer | 4 7 | Farmer | 4 8 | Credit Card | 0 9 | Visa | 8 10 | Master Card | 8 ...2. Customer table which has customer ID and name as follows:
cust_id | cust_name3. Category_Has_Customer which is many to many relationship between Customer and Category.
--------+----------- 111111 | John 222222 | David 333333 | Chris 444444 | Mark ...
chc_cust_id | chc_cat_id ------------+----------- 111111 | 2 111111 | 5 111111 | 9 222222 | 7 222222 | 3 333333 | 6The category has only two levels depth.
In my application, a customer could have zero or more categories. I would like to display some or all of the categories, which the customer has. For example if I choose to display all categories I would like to have the following table:
cust_name | State | Title | Credit Card ----------+------------+----------+------------ John | Texas | Engineer | Visa David | California | Farmer | Chris | | Lawyer | Mark | | |I should also be able to display certain categories, for example Title and Credit Card only:
cust_name | Title | Credit Card ----------+----------+------------ John | Engineer | Visa David | Farmer | Chris | Lawyer | Mark | |
I tried to do it with LEFT JOIN's, something like:
SELECT c1.cust_id, c1.cust_name, t1.cat_desc as State
FROM Customer c1, Category_has_Customer chc
LEFT JOIN Category t1 ON t1.cat_pid = '1' AND chc.chc_cat_id = t1.cat_id
WHERE c1.cust_id = chc.chc_cust_id
But it didn't help since I got duplicated rows.
I'll give this a go, should work on all sql implemtations but I've done in in t-sql:
With this data
This query
Gives
If you want to take out the State just remove it from the select statement.