2

i have an really old database with a really bad table structure and I'm trying to create these tables with a better structure. For doing this, I need to match two tables, to get the id of a category.

Here are my two old tables:

table categorys:

| ID |  catname     |  cat1  |    cat2     |   cat3  | cat4 |
+----+--------------+--------+-------------+---------+------+
|  1 |  bike        |  bike  | NULL        | NULL    | NULL |
|  2 |  accessories |  bike  | accessories | NULL    | NULL |
|  3 |  helmets     |  bike  | accessories | helmets | NULL |
|  4 |  lights      |  bike  | accessories | lights  | NULL |
|  5 |  led         |  bike  | accessories | lights  | led  |

table products:

| ID |  productnr  |  productname  | cat1  |    cat2     |   cat3  | cat4 |
+----+-------------+---------------+-------+-------------+---------+------+
|  1 |  451157     |  productya    |  bike | accessories | NULL    | NULL |
|  2 |  555523     |  product11    |  bike | accessories | helmets | NULL |
|  3 |  234432     |  helmetxqa    |  bike | accessories | helmets | NULL |
|  4 |  666623     |  lightblue    |  bike | accessories | lights  | NULL |
|  5 |  542123     |  foobarlight  |  bike | accessories | lights  | led  |

At first I would like to get rid of the columns cat1, 2, 3 and 4 from the products table.

So that I get a result like this:

| ID |  catId  | productnr  |  productname  |
+----+---------+------------+---------------+
|  1 |  2      | 451157     |  productya    |
|  2 |  3      | 555523     |  product11    |
|  3 |  3      | 234432     |  helmetxqa    |
|  4 |  4      | 666623     |  lightblue    |
|  5 |  5      | 542123     |  foobarlight  |

Could some one tell me, how I should make a query which checks if all 4 cat's are matching, and then give's me the cat'id? I've tried it this way, but I think it's the wrong way, because everytime a product only has 2 or 3 cat's I don't get the associated catId. So it only works for products with all 4 cat's defined.

SELECT
    cat.`id`,
    prod.`productnr`,
    prod.`productname`
FROM
    products as prod
LEFT JOIN
    categorys as cat
ON
    cat.`cat1` = prod.`cat1`
AND
    cat.`cat2` = prod.`cat2`
AND
    cat.`cat3` = prod.`cat3`
AND
    cat.`cat4` = prod.`cat4`

If someone has also useful tipps for me, please tell me about. ;-)

Thanks for helping me out :)

Improve this question
3
  • What do you want exactly? Commented Oct 20, 2014 at 21:21
  • Does your empty cells are null or '' ? Commented Oct 20, 2014 at 21:22
  • I changed my Example Tables. Empty cells are NULL! Commented Oct 20, 2014 at 21:30

4 Answers 4

Reset to default
6

You can approach this by doing a separate join for each combination of categories. Of course, this is a hierarchical ranking and you don't want duplicates. So, the following does these checks:

SELECT prod.id, coalesce(c4.id, c3.id, c2.id, c1.id) as catid
       prod.`productnr`, prod.`productname`
FROM products prod left join
     categorys c4
     on c4.cat1 = prod.cat1 and c4.cat2 = prod.cat2 and
        c4.cat3 = prod.cat3 and c4.cat4 = prod.cat4 left join
     categorys c3
     on c3.cat1 = prod.cat1 and c3.cat2 = prod.cat2 and
        c3.cat3 = prod.cat3 and c3.cat4 is null and
        c4.id is null left join
     categorys c2
     on c2.cat1 = prod.cat1 and c2.cat2 = prod.cat2 and c2.cat3 is null and
        c3.id is null and c4.id is null left join
     categorys c1
     on c1.cat1 = prod.cat1 and c1.cat2 is null and
        c2.id is null and c3.id is null and c4.id is null;

It is possible that this will produce duplicate rows in some cases (although it works to avoid that situation). If that occurs, then a group by might still be necessary.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

this is working as described. :) There are no duplicate lines, so it's all I wanted. :) Thanks, now I can create better tables. :)
Could you please tell me why you check "c4.id is null", "c3.id is null" and so on? Why the column id?
@ernjay . . . I am trying to avoid product 2 matching categories 1, 2, and 3.
1

First, you need the proper data model.

Categories:

| ID |  catname     |
+----+--------------+

Products: 

| ID |  productnr  |  productname  |
+----+-------------+---------------+

And the oh so magic junction table:

|ID | product_id | category_id|
+---+------------+------------+

Now you can properly ask your database to provide you with data

SELECT
    cat.`id`,
    prod.`productnr`,
    prod.`productname`

FROM categories as cat

INNER JOIN products2categories p2c
ON p2c.category_id = cat.id

INNER JOIN products prod 
ON p2c.product_id = prod.id

Since I really dislike handing out complete copy paste examples, I'll let you play with the query above to check how you can find a product that exists in 4 categories. Remember - the easiest way is usually always the correct way.

Improve this answer

1 Comment

Thanks so much for your answer N.B. :) The problem is, I'm trying to build new tables from the result, because the old tables have a little about 1000 entry's. So it's not possible to create the new tables. I need to create the Data with the result. (I'm doing it in PHP). With the output I will create a new INSERT for every Product. Do you know what I mean? Maybe you could give me a hint, why my SQL query is just working with products that have all cat's defined. The matching doesn't work with empty cat's. Thanks so much!
1

The problem is likely the null in your table, because null != null in sql

To go around that problem, you can coalesce your data

SELECT
    cat.`id`,
    prod.`productnr`,
    prod.`productname`
FROM
    products as prod
LEFT JOIN
    categorys as cat
ON
    coalesce(cat.`cat1`,'No Value') = coalesce(prod.`cat1`,'No Value')
AND
    coalesce(cat.`cat2`,'No Value') = coalesce(prod.`cat2`,'No Value')
AND
   coalesce(cat.`cat3`,'No Value') = coalesce(prod.`cat3`,'No Value')
AND
    coalesce(cat.`cat4`,'No Value') = coalesce(prod.`cat4`,'No Value')
Improve this answer

5 Comments

This will return duplicate rows.
This doesn't work properly. I get exactly the number of results as my products table has products, but some lines in the result have no cat id. They are "null".
Does your categorys table define all the categorys in products table ?
Yep, all categories are in this table.
I tried with the data you gave us on my side, and it works. The only way you still get Null is he doesn't find the mix of cat1, cat2, cat3, cat4 in categorys
0

If you want to get only the ids when every cat fields are joining, try using INNER JOIN instead of LEFT JOIN.

SELECT
    cat.`id`,
    prod.`productnr`,
    prod.`productname`
FROM
    products as prod
INNER JOIN
    categorys as cat
ON
    cat.`cat1` = prod.`cat1`
AND
    cat.`cat2` = prod.`cat2`
AND
    cat.`cat3` = prod.`cat3`
AND
    cat.`cat4` = prod.`cat4`
Improve this answer

1 Comment

with this query my result has only about 500 lines, but the product table has about 1000 entrys.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.