Q
Problem solve Get help with specific problems with your technologies, process and projects.

# Compare two many-to-many relationships, Part 2

See Compare two many-to-many relationships (Part 1) for the question.

In Part 1 we developed a query which would extract all of Joe's colors, the flavors that each color can make, and from each flavor, all the colors necessary to make that flavor. Here's that data again:

```11 Joe 1 red 303 grape 1 red 11 Joe 1 red 303 grape 5 blue 11 Joe 3 yellow 302 lime 3 yellow 11 Joe 3 yellow 302 lime 4 green 11 Joe 5 blue 301 raspberry 5 blue 11 Joe 5 blue 301 raspberry 6 purple 11 Joe 5 blue 303 grape 1 red 11 Joe 5 blue 303 grape 5 blue 11 Joe 6 purple 301 raspberry 5 blue 11 Joe 6 purple 301 raspberry 6 purple
```

Now add an additional column, which compares Joe's color to the required color, and comes up with a 1 if it's a match, otherwise a 0. We also sort the data into flavor sequence:

```select U.UserID , U.UName , C.ColorID , C.ColorName , F.FlavorID , F.FlavorName , Ctwo.ColorID , Ctwo.ColorName , case when C.ColorID = Ctwo.ColorID then 1 else 0 end  from USERS U inner join U2C on U.UserID = U2C.UserID inner join COLORS C on U2C.ColorID = C.ColorID inner join C2F on C.ColorID = C2F.ColorID inner join FLAVORS F on C2F.FlavorID = F.FlavorID inner join C2Ftwo on F.FlavorID = C2Ftwo.FlavorID inner join COLORS Ctwo on C2Ftwo.ColorID = Ctwo.ColorID order by 1,5,7

11 Joe 5 blue 301 raspberry 5 blue 1 11 Joe 6 purple 301 raspberry 5 blue 0 11 Joe 6 purple 301 raspberry 6 purple 1 11 Joe 5 blue 301 raspberry 6 purple 0 11 Joe 3 yellow 302 lime 3 yellow 1 11 Joe 3 yellow 302 lime 4 green 0 11 Joe 1 red 303 grape 1 red 1 11 Joe 5 blue 303 grape 1 red 0 11 Joe 1 red 303 grape 5 blue 0 11 Joe 5 blue 303 grape 5 blue 1
```

This is where the "magic" happens. In effect, we have a partial cross join from each of Joe's colors, to all the colors that are required to make any of the flavors that color can make. The 1 or 0 simply flags whether that specific color matches.

At this point we can do our aggregates. We want to count how many matches Joe's colors had for the required flavor colors, and compare that to the total number of required colors per flavor:

```select U.UserID , U.UName , F.FlavorID , F.FlavorName , sum( case when C.ColorID = Ctwo.ColorID then 1 else 0 end ) , count(distinct Ctwo.ColorID)  from USERS U inner join U2C on U.UserID = U2C.UserID inner join COLORS C on U2C.ColorID = C.ColorID inner join C2F on C.ColorID = C2F.ColorID inner join FLAVORS F on C2F.FlavorID = F.FlavorID inner join C2Ftwo on F.FlavorID = C2Ftwo.FlavorID inner join COLORS Ctwo on C2Ftwo.ColorID = Ctwo.ColorID group by U.UserID , U.UName , F.FlavorID , F.FlavorName

11 Joe 301 raspberry 2 2 11 Joe 302 lime 1 2 11 Joe 303 grape 2 2
```

Lastly, we rewrite the query so that it just produces the desired answer to the question "Which flavors can Joe make?"

```select U.UName , F.FlavorName from USERS U inner join U2C on U.UserID = U2C.UserID inner join COLORS C on U2C.ColorID = C.ColorID inner join C2F on C.ColorID = C2F.ColorID inner join FLAVORS F on C2F.FlavorID = F.FlavorID inner join C2Ftwo on F.FlavorID = C2Ftwo.FlavorID inner join COLORS Ctwo on C2Ftwo.ColorID = Ctwo.ColorID group by U.UName , F.FlavorName having sum(case when C.ColorID = Ctwo.ColorID then 1 else 0 end) = count(distinct Ctwo.ColorID)

Joe raspberry Joe grape
```

#### Start the conversation

Send me notifications when other members comment.

## SearchDataManagement

• ### Developing an enterprise data strategy: 10 steps to take

Consultants detail 10 to-do items for data management teams looking to create a data strategy to help their organization use data...

• ### How graph data science is helping the fight against COVID-19

Technology to rapidly correlate and connect disparate data sets in a knowledge graph is being used by German researchers and ...

• ### Okera sees increasing demand for enterprise data governance

Data governance and access vendor raises \$14 million as incoming CEO Nick Halsey sees a continuing need for enterprise data ...

• ### OmniSci analytics tools to come embedded in HP workstations

Under the terms of an agreement between OmniSci and HP revealed on Thursday, OmniSci's BI platform will come as a preloaded ...

• ### Economic downturn shows businesses true value of analytics

Business intelligence is proving to be a critical tool for enterprises as they attempt to survive during the sudden economic ...

• ### Key differences of a data scientist vs. data engineer

Data scientists and data engineers often work together, and sometimes the positions are treated as the same. Read on to find out ...

## SearchSAP

• ### SAP Cloud Platform app helps German citizens find their way home

Rückholprogramm, an SAP Cloud Platform app, was built in just a few days to fulfill a request from the German Federal Foreign ...

• ### SAP S/4HANA deployment options: Legacy ERP vs. cloud

Take this tour of the on-premises S/4HANA and its two cloud siblings and get expert advice on major differences in cost and ...

## SearchSQLServer

• ### SQL Server database design best practices and tips for DBAs

Good database design is a must to meet processing needs in SQL Server systems. In a webinar, consultant Koen Verbeeck offered ...

• ### SQL Server in Azure database choices and what they offer users

SQL Server databases can be moved to the Azure cloud in several different ways. Here's what you'll get from each of the options ...

• ### Using a LEFT OUTER JOIN vs. RIGHT OUTER JOIN in SQL

In this book excerpt, you'll learn LEFT OUTER JOIN vs. RIGHT OUTER JOIN techniques and find various examples for creating SQL ...

## TheServerSide.com

• ### Compare these top 6 Java IDEs for development

Find the Java IDE that best fits your development needs. Compare these options from Eclipse, NetBeans, IntelliJ, JDeveloper, ...

• ### Eclipse launches Theia 1.0 as alternative to VS Code

The Eclipse Foundation's Theia framework for building desktop and cloud IDEs has the potential to be as big as the initial ...

• ### Developing a COVID-19 application? Design it the right way

Application developers with skills and an idea could design an app to combat the virus. Follow these insights to effectively ...

## SearchDataCenter

• ### Advances in server hardware components speed up data centers

Server hardware has consistently evolved since the 1980s. Now, IT professionals can implement the latest in CPU and GPU ...

• ### Improvements in CPU features help shape selection

CPUs have evolved to meet ever-increasing technology demands. We look at the way performance and power characteristics have ...

• ### Dive into the quantum computing qubit with IBM's Bob Sutor

The quantum computing industry is entering a new era. IBM's Bob Sutor discusses the technology's importance and how his latest ...

## SearchContentManagement

• ### Enterprise search software comparison

Enterprise search has always been both a necessity and a challenge, and vendors have sought to bring improvements to the market ...

• ### New OpenText CE apps, services roll out

OpenText containerizes its applications for cloud deployment; adds raft of content services and features for customer experience ...

• ### Box Shield adds malware detection

Box Inc. quarantines malware-infected files with features that allow content to be viewed, with admin and security team alerts to...

## SearchHRSoftware

• ### Food industry turns to AI hiring platform to fill 1M jobs

The food industry is hungry for employees. It has a shopping list of occupations it needs filled to keep essential food producers...

• ### How companies can use tech to ramp up remote work productivity

With the sudden spike of employees working from home, companies need to get creative in how they keep employees connected and ...

• ### H-1B work visa petitions hit record high, despite pandemic

The U.S. received a record 275,000 H-1B work visas for the 2021 fiscal year. But these visa petitions were prepared in better ...

Close