lundi 4 juillet 2016

Join product hierarchy to customer table

This is for an MSQL2012 instance

I currently have an issue joining a client data table to a hierarcy, my aim is to produce an output table that shows what they do not own on the first level.

I'm having issues with the client ID, as when you compare the tables the outputs overlap and give exceptions for all client ID's, and not for each client ID

Could anyone help me in generating the syntax for this type of issue?

product_data
     Level 1, Level 1 Name, Level 2, Level 2 Name
       1         Fruit         2        Apple
       1         Fruit         3        Banana
       1         Fruit         4        Orange
       5         Vegetable     6        Potato
       5         Vegetable     7        Celery

client data
   Client ID, Level 1, Level 1 Name, Level 2, Level 2 Name
       1       1          Fruit         2        Apple
       1       1          Fruit         3        Banana
       2       5          Vegetable     5        Potato
       2       1          Fruit         4        Orange

Desired Output
   Client ID, Level 1, Level 1 Name, Level 2, Level 2 Name
       1          1       Fruit         4        Orange
       2          1       Fruit         2        Apple
       2          1       Fruit         3        Banana
       2          5       Vegetable     7        Celery

Thanks!

Aucun commentaire:

Enregistrer un commentaire