Warm tip: This article is reproduced from serverfault.com, please click
mariadb mysql

MySQL: Taxonomy

发布于 2020-11-30 16:46:41

I was hoping someone could help me come up with a query for what I'm looking to do. I have a website that lists game servers and I'm trying to improve my search system a bit.

There's three tables of interest; servers, version_taxonomy and category_taxonomy. The taxonomy tables contain two columns, one for a server ID and one for a version/category ID, where associations between a server and it's supported versions and categories can be made.

Up till now, I've been joining both taxonomy tables to the server table and be looking up servers for one version and one category, it's been working fine. However I'm looking to allow the search of a server that has multiple categories at the same time.

I've made an image to try and illustrate what I'm looking to do: taxonomy tables

Say I'm looking for a server that has both categories 5 and 12 - Based on the table on the left that would be servers 1 and 3. But how would that be in a query? And how would I use that query to later get and work with the rest of the server data (JOIN like I'd normally do?)

Hopefully that makes sense! Looking forward to your responses.

Questioner
Stelios Mac
Viewed
0
分享
xQbert 2020-12-01 01:02:08

Assuming I understand the question:

Join the two tables then count the distinct values of category ID while limiting by them. Distinct is not be needed if you can guarantee the uniqueness of serverID, categoryID from table A and a 1:1 relationship to server taxonomy which would be true if you always limit by 1 and only 1 version...

SELECT A.ServerID, count(A.CategoryID) CatCnt
FROM A
INNER JOIN B
  on A.ServerID = B.ServerID
WHERE A.CATEGORYID in (5,12)
  and B.Version= 1.16
GROUP BY A.ServerID
HAVING count(distinct A.CategoryID) = 2

The category ID could be parameter passed in as well as the count distinct as you know both values.

This could be used as a CTE or as a inline derived table as a source then join in to get the addiontal data; or left join in the desired data assuming it's a 1:1 relationship.

If you want a working example: post DDL for table and SQL to create sample data and I'll put something in https://rextester.com/.

热门帖子

1
推荐一些好玩的/大众的手游
2
求指教后端项目迁移方案
3
迷你洗衣机是不是都是智商税？
4
求助一个排查了半年没解决的 MySQL order by 子句导致索引失效的问题， 500 多万条记录的小表要查快两分钟
5
个人开发了一款 WordPress 主题： iPao，集成了 AI 总结功能
6
偶然发现奇游加速器会在系统里植入根证书
7
国内有蒲公英替代品推荐吗？
8
语音助手这个东西真的会监听谈话并且上传，从而泄漏隐私吗？
9
出一些有意思的域名-明盘
10
jetbrains 全家桶升级 2024 后，在滚动代码时候感觉有点掉帧

热门github

1
A multi-platform library for OpenGL, OpenGL ES, Vulkan, window and input
2
Dev tool that writes scalable apps from scratch while the developer oversees the implementation
3
shadcn/ui, but for Svelte. ✨
4
The Python Risk Identification Tool for generative AI (PyRIT) is an open access automation framework to empower security professionals and machine learning engineers to proactively find risks in their generative AI systems.
5
Performance-portable, length-agnostic SIMD with runtime dispatch
6
ZK Credo
7
OpenCodeInterpreter: Integrating Code Generation with Execution and Refinement
8
Joplin - the secure note taking and to-do app with synchronisation capabilities for Windows, macOS, Linux, Android and iOS.
9
Mamba is a new state space model architecture showing promising performance on information-dense data such as language modeling, where previous subquadratic models fall short of Transformers. It is based on the line of progress on structured state space models, with an efficient hardware-aware design and implementation in the spirit of FlashAttention.
10
This repository contains System Design resources which are useful while preparing for interviews and learning Distributed Systems
11
Curso para aprender el lenguaje de programación Python desde cero y para principiantes. 75 clases, 37 horas en vídeo, código, proyectos y grupo de chat. Fundamentos, frontend, backend, testing, IA...
12
🎓 Path to a free self-taught education in Computer Science!
13
1️⃣🐝🏎️ The One Billion Row Challenge -- A fun exploration of how quickly 1B rows from a text file can be aggregated with Java
14
A collective list of free APIs
15
📚 Freely available programming books