Stack Overflow
Stack Overflow

Questions tagged [sql-match-all]

51 questions
110
votes
13 answers

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 in both the soccer (30) and baseball (50)…
Xeoncross
  • 55,620
  • 80
  • 262
  • 364
34
votes
6 answers

Select values that meet different conditions on different rows

Let's say I have a two-column table like this: userid | roleid --------|-------- 1 | 1 1 | 2 1 | 3 2 | 1 I want to get all distinct userids that have roleids 1, 2 AND 3. Using the above example, the only…
John
  • 32,403
  • 80
  • 251
  • 422
8
votes
2 answers

SQL one-to-many match the one side by ALL in many side

In the following one to many CREATE TABLE source(id int, name varchar(10), PRIMARY KEY(id)); CREATE TABLE params(id int, source int, value int); where params.source is a foreign key to source.id INSERT INTO source values(1, 'yes'); INSERT INTO…
James Massey
7
votes
8 answers

How do I avoid dynamic SQL when using an undetermined number of parameters?

I have a StackOverflow-like tagging system for a database I'm working on. And I'm writing a stored procedure that looks for results based on an undetermined number of tags in a WHERE clause. There could be anywhere between 0 and 10 tags to filter…
Steve Wortham
  • 21,740
  • 5
  • 68
  • 90
6
votes
9 answers

What is a SQL statement to select an item that has several attributes in an item/attribute list?

Say I have a table that has items and attributes listed like, frog green cat furry frog nice cat 4 legs frog 4 legs From the items column I want to select unique objects that have both the green and 4 legs attribute. I would expect…
Jason Christa
  • 12,150
  • 14
  • 58
  • 85
5
votes
2 answers

Select in a many-to-many relationship in MySQL

I have two tables in a MySQL database, Locations and Tags, and a third table LocationsTagsAssoc which associates the two tables and treats them as a many-to-many relationship. Table structure is as follows: Locations --------- ID int (Primary…
Joff Williams
  • 53
  • 1
  • 3
4
votes
3 answers

SELECT multiple rows with WHERE

PID VALUE 3 1 4 3 1 9 1 3 How to select row(s) that has both values 3 and 9? I tried select PID from table where VALUE = 3 and VALUE = 9 So that i get something like below, instead i get an empty set. PID 1…
kornesh
  • 618
  • 2
  • 9
  • 24
4
votes
1 answer

Translating INTERSECT statement to MySQL

I have the following query using INTERSECT and I can't figure out how to translate it to MySQL using INNER JOIN. SELECT DISTINCT Title, Variable FROM Table WHERE Location='Location1' AND Date='Date1' INTERSECT SELECT DISTINCT Title, Variable…
Brian
  • 26,662
  • 52
  • 135
  • 170
4
votes
1 answer

Having Trouble Writing SQL query Many To Many Relationship

I have a Django application with the following postgres db tables: Publication and Tag Publication { title tags } Tag { title } Tag and Publication have a many to many relationship. What I want to do is do an and/or combo search: for…
jac300
  • 5,182
  • 14
  • 54
  • 89
3
votes
3 answers

Selecting primary from a mapping row SQL

I have a table that references a bunch of articles, the table contains tags for those articles. Like this: tag text article_id bigint I want to select all article_ids with a set of tags, say tag1, tag2, tag3 but the article could also have tag4,…
ehiller
  • 1,346
  • 17
  • 32
3
votes
3 answers

mysql: joining tables + finding records with an AND style query, rather than OR

Note: Using MySQL 4.0, which means no subqueries (at present). I have 2 tables: A "user_details" table A "skills" table, which has the user_id and a "skill_id", which maps to a predefined set of skills defined elsewhere. The current query allows…
starmonkey
  • 3,147
  • 2
  • 20
  • 15
3
votes
2 answers

How do I write a named scope to filter by all of an array passed in, and not just by matching one element (using IN)

I have two models, Project and Category, which have a many-to-many relationship between them. The Project model is very simple: class Project < ActiveRecord::Base has_and_belongs_to_many :categories scope :in_categories, lambda { |categories| …
Brandon Weiss
  • 591
  • 1
  • 5
  • 14
3
votes
4 answers

SQL query for selecting products with same ingredients of other products

I have a database that stores products "available on the market" and products "still in development" in two separate tables (market_product and dev_product). A third table (substance) contains all substances a product can made of. Other two tables…
Hobbes
  • 978
  • 2
  • 9
  • 15
3
votes
2 answers

SQL Query on four tables with MySQL - 'intersection'

I have 4 tables POST: id POST_TAG: post_id tag_id value TAG: id SEARCH: tag_id post_tag_value I need to query for posts who have all tags and values as rows in SEARCH table ( not just one equal value for a tag): EDIT: Sorry for not providing…
gabberr
  • 357
  • 2
  • 10
2
votes
3 answers

Can you solve this simple SQL query?

Suppose it's a website that sells photo cameras. Here are my entities (tables): Camera: A simple camera Feature: A feature like: 6mp, max resolution 1024x768, The thing is between cameras and feature i've got a Many to Many relationship, so i have…
santiagobasulto
  • 11,320
  • 11
  • 64
  • 88
1
2 3 4