65129

SQL searching for rows that contain multiple criteria

Question:

I have 3 tables

Customer Groups CustomerGroupJoins

Fields to be used

Customer:Key Groups:Key CustomerGroupJoins:KeyCustomer, KeyGroup

I need to search for all users that are in all groups with keys, 1,2,3

I was thinking something like (but have no idea whether this is the right/best way to go):

SELECT * FROM Customer WHERE Key = ( SELECT KeyCustomer FROM CustomerGroupJoins WHERE KeyGroup = a ) = ( SELECT KeyCustomer FROM CustomerGroupJoins WHERE KeyGroup = b ) = ( SELECT KeyCustomer FROM CustomerGroupJoins WHERE KeyGroup = c )

Answer1:

I created this test data:

srh@srh@[local] =# select * from customer join customergroupjoins on customer.key = customergroupjoins.keycustomer join groups on groups.key = customergroupjoins.keygroup; key | name | keycustomer | keygroup | key | name -----+--------+-------------+----------+-----+--------- 1 | fred | 1 | 1 | 1 | alpha 1 | fred | 1 | 2 | 2 | beta 1 | fred | 1 | 3 | 3 | gamma 2 | jim | 2 | 1 | 1 | alpha 2 | jim | 2 | 2 | 2 | beta 2 | jim | 2 | 4 | 4 | delta 2 | jim | 2 | 5 | 5 | epsilon 3 | shelia | 3 | 1 | 1 | alpha 3 | shelia | 3 | 3 | 3 | gamma 3 | shelia | 3 | 5 | 5 | epsilon (10 rows)

So "fred" is the only customer in all of (alpha, beta, gamma). To determine that:

srh@srh@[local] =# select * from customer where exists (select 1 from customergroupjoins where keycustomer = customer.key and keygroup = 1) and exists (select 1 from customergroupjoins where keycustomer = customer.key and keygroup = 2) and exists (select 1 from customergroupjoins where keycustomer = customer.key and keygroup = 3); key | name -----+------ 1 | fred (1 row)

This is one approach. The (1,2,3) - your known group keys - are the parameters in the subqueries. Someone already mentioned you don't actually need to join to the groups table at all.

Another way:

select customer.* from customer join customergroupjoins g1 on g1.keycustomer = customer.key join customergroupjoins g2 on g2.keycustomer = customer.key join customergroupjoins g3 on g3.keycustomer = customer.key where g1.keygroup = 1 and g2.keygroup = 2 and g3.keygroup = 3

The general problem of finding users with all groups (g_1, g_2 .. g_N) is a bit tricker. These queries above have joined to the link table (customergroupjoins) N times, so it's a different query depending on the number of groups you're checking against.

One approach to <em>that</em> is to create a temporary table to use as a query parameter: the table contains the list of groups that the customers must have all of. So for instance create a temp table called "ParamGroups" (or "#ParamGroups" on SQL Server to mark it as temporary), populate it with the group keys you're interested in and then do this:

select * from customer where key in ( select keycustomer from customergroupjoins join paramgroup on paramgroup.keygroup = customergroupjoins.keygroup group by keycustomer having count(*) = (select count(*) from paramgroup))

Also, as a beginner, I strongly recommend you look into advice about naming conventions for database tables and columns. Everyone has different ideas (and they can spark off holy wars), but pick some standards (if they aren't dictated to you) and stick to them. For instance you named one table "customer" (singular) and one table "groups" (plural) which looks bad. It's more usual to use "id" rather than "key", and to use it as a suffix ("customer_id" or "CustomerID") than a prefix. The whole CamelCase vs old_skool argument is more a matter of style, as is the primary-key-is-just-"id"-not-"table_id".

Answer2:

The above solutions will work if the customer is in <em>any</em> of the three groups, but won't check for membership in <em>all</em> of them.

Try this instead:

SELECT a.* FROM (SELECT c.*, substring((SELECT (', ' + cg.KeyGroup) FROM CustomerGroupJoins cg WHERE cg.KeyCustomer = c.[Key] AND cg.KeyGroup IN (1,2,3) ORDER BY cg.KeyGroup ASC FOR XML PATH('')), 3, 2000) AS GroupList FROM Customer AS c) AS a WHERE a.GroupList = ('1, 2, 3')

This will also work:

SELECT c.* FROM Customer c WHERE c.[Key] IN (SELECT cg.[KeyGroup] JOIN CustomerGroupJoins cg WHERE cg.KeyGroup IN (1,2,3) GROUP BY cg.KeyGroup HAVING count(*) = 3)

Answer3:

Maybe something like this?

SELECT c.Key, g.Key, cgj.KeyCustomer, cgj.KeyGroup FROM Customer c LEFT JOIN CustomerGroupJoins cgj ON cgj.KeyCustomer = c.Key LEFT JOIN Groups g ON g.Key = cgj.KeyGroup WHERE g.key IN (1, 2, 3)

Answer4:

From what you described, try this:

SELECT * FROM Customer c INNER JOIN CustomerGroupJoins cgj ON c.key = cgj.keyCustomer INNER JOIN groups g ON cgj.keyGroup = g.key WHERE g.key IN (1,2,3)

Answer5:

SELECT * FROM customer c INNER JOIN customerGroupJoins j ON(j.customerKey = c.key) WHERE j.keyGroup IN (1, 2, 3)

You don't need to join against groups-table, as long as you are only interested in the group key, which is found in your join table.

Answer6:

Here's a possible answer, not tested:

select custid from CustomerGroupJoins where groupid in (1,2,3) group by custid having count(*) = 3

Searches for customer's that have 3 rows with groupid 1, 2, or 3. Which means that they are in all 3 groups, because I assume you have a primary key on (custid,groupid).

Recommend

  • gate level parsing using yosys
  • error: field ‘children’ has incomplete type ‘Node [2]
  • Fewer Colors Than Vertices
  • Finding shortest distance between two polygons(SqlGeography c# )
  • How to build from multiple source files at once in Eclipse
  • filtering two columns into one picking one or the other according to some condition
  • Search for two consecutive rows with same data in Excel
  • Using VBA Running A Loop Thru Multiple Worksheets
  • C's pow() doesn't work with a variable exponent
  • mod-rewrite for URLs with extra characters after second slash
  • Force close in android app
  • Static initialization order issue in C++
  • Creating Subgraph using igraph in R
  • How to write binary data in bash
  • how to define xsd element with multiple option?
  • How to solve simbolically a pair of nonlinear equations using Python (sympy)?
  • listView filter mistake
  • How to insert a character in a list, based on the consecutive appearance of two elements from anothe
  • C strings from text file
  • Audit each inserted row in a Trigger
  • Matlab: Dividing chunks of data randomly into equal sized sets
  • Program OK outside debugger, SIGILL under debugger when stepping?
  • compiling openmp, macports gcc, and eclipse cdt
  • VB.NET Radio Button Handler Code running twice
  • UWP/C# - Issue with AQS and USB Devices
  • How to make JSON.NET deserialize to Microsoft Date Time?
  • one Local Olampyad Questions on Informatic in 2011
  • Allowing both email and username for authentication
  • Asynchronous UI Testing in Xcode With Swift
  • Get one-time binding to work for ng-if
  • Finding past revisions of files in StarTeam w/ .NET SDK / C#
  • Join two tables and save into third-sql
  • How can I use Kendo UI with Razor?
  • How to model a transition system with SPIN
  • ActionScript 2 vs ActionScript 3 performance
  • ORA-29908: missing primary invocation for ancillary operator
  • How do you troubleshoot character encoding problems?
  • What are the advantages and disadvantages of reading an entire file into a single String as opposed
  • Django query for large number of relationships
  • Converting MP3 duration time