All Questions

85
votes
13answers
12460 views

How to filter SQL results in a has-many-through relation

Assuming I have the tables student, club, and student_club: student { id name } club { id name } student_club { student_id club_id } I want to know how to find all students ...
29
votes
6answers
12465 views

Select values that meet different conditions on different rows?

This is a very basic query I can't figure out.... Let's say I have a two column table like this: userid | roleid --------|-------- 1 | 1 1 | 2 1 | 3 2 | 1 I w...
21
votes
8answers
11028 views

PostgreSQL where all in array

What is the easiest and fastest way to achieve a clause where all elements in an array must be matched - not only one when using IN? After all it should behave like mongodb's $all. Thinking about gro...
1
votes
2answers
148 views

SQL where joined set must contain all values but may contain more

I have three tables offers, sports and the join table offers_sports. class Offer < ActiveRecord::Base has_and_belongs_to_many :sports end class Sport < ActiveRecord::Base has_and_belongs_t...
7
votes
2answers
2788 views

jsonb query with nested objects in an array

I'm using PostgreSQL 9.4 with a table teams containing a jsonb column named json. I am looking for a query where I can get all teams which have the Players 3, 4 and 7 in their array of players. The ...
3
votes
1answers
2664 views

SQL: Get Products from a category but also must be in another set of categories

I am currently stuck in a situation. The scenario is this. I have products who may be associated with multiple categories. The data structure is shown below: Products Table: product_id name 1 ...
8
votes
3answers
46209 views

MySQL select join where AND where

I have two tables in my database: Products id (int, primary key) name (varchar) ProductTags product_id (int) tag_id (int) I would like to select products having all given tags. I tried: SELE...
9
votes
5answers
2896 views

MySQL Select ID's which occur on different rows with multiple specific values for a column

I'm trying to select items from an associative table that have satisfy two or more values of the same field, sounds confusing, let me explain. +-----------------------+ | item_id | category_id | +---...
4
votes
7answers
7161 views

SQL how to search a many to many relationship

I have a database with two main tables notes and labels. They have a many-to-many relationship (similar to how stackoverflow.com has questions with labels). What I am wondering is how can I search f...
1
votes
4answers
104 views

Matching algorithm in SQL

I have the following table in my database. # select * FROM matches; name | prop | rank ------+------+------- carl | 1 | 4 carl | 2 | 3 carl | 3 | 9 alex | 1 | 8 ale...

Previous Next