Sql select where array in column

sql query with array values
sql where in array
sql select return array
postgres where in array
sql array column
sql array length
sql array functions
sql select value from array

In my query I use join table category_attributes. Let's assume we have such rows:

category_id|attribute_id
1|1
1|2
1|3

I want to have the query which suites the two following needs. I have a variable (php) of allowed attribute_id's. If the array is subset of attribute_id then category_id should be selected, if not - no results.

First case:

select * from category_attributes where (1,2,3,4) in category_attributes.attribute_id

should give no results.

Second case

select * from category_attributes where (1,2,3) in category_attributes.attribute_id

should give all three rows (see dummy rows at the beginning).

So I would like to have reverse side of what standard SQL in does.

Solution

Step 1: Group the data by the field you want to check.

Step 2: Left join the list of required values with the records obtained in the previous step.

Step 3: Now we have a list with required values and corresponding values from the table. The second column will be equal to required value if it exist in the table and NULL otherwise.

Count null values in the right column. If it is equal to 0, then it means table contains all the required values. In that case return all records from the table. Otherwise there must be at least one required value is missing in the table. So, return no records.

Sample

Table "Data":

Required values: 10, 20, 50

Query:

SELECT * 
FROM   Data 
WHERE  (SELECT Count(*) 
        FROM   (SELECT D.value 
                FROM   (SELECT 10 AS value 
                        UNION 
                        SELECT 20 AS value 
                        UNION 
                        SELECT 50 AS value) T 
                       LEFT JOIN (SELECT value 
                                  FROM   Data 
                                  GROUP  BY value) D 
                              ON ( T.value = D.value )) J 
        WHERE  value IS NULL) = 0;

SQL IN Predicate: With IN List or With Array? Which is Faster , 1. COLUMN.in( 1 , 2 , 3 , 4 ) Would an array bind variable be much better? Array. SELECT *. FROM film. JOIN film_actor USING (film_id). SELECT ARRAY (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) AS new_array; +-----+ | new_array | +-----+ | [1, 2, 3] | +-----+ To construct an ARRAY from a subquery that contains multiple columns,

You can use group by and having:

select ca.category_id
from category_attributes ca
where ca.attribute_id in (1, 2, 3, 4) 
group by ca.category_id
having count(*) = 4;  -- "4" is the size of the list

This assumes that the table has no duplicates (which is typical for attribute mapping tables). If that is a possibility, use:

having count(distinct ca.attribute_id) = 4

Working with Arrays in Standard SQL | BigQuery, Works for PostgreSQL, MySQL, BigQuery, SQL Server, Redshift, Snowflake, the contents of an array by specifying it in the select clause like any other column: . If you are in the unfortunate situation that you are working with SQL 2000 or even older versions, I have an old article Array and Lists in SQL Server 2000 and Earlier. Comma-separated Lists in a Table Column. Before I end this introductory article, there is one more thing I want to cover.

You can aggregate attribute_id into array and compare two array from php.

SELECT category_id FROM 
(select category_id, group_concat(attribute_id) as attributes from category_attributes
order by attribute_id) t WHERE t.attributes = (1, 2, 3);

But you need to find another way to compare arrays or make sure that array is always sorted.

How to Query Arrays in PostgreSQL, Arrays. PostgreSQL allows columns of a table to be defined as An alternative syntax, which conforms to the SQL standard by using the keyword ARRAY, can be used for SELECT * FROM sal_emp; name | pay_by_quarter | schedule� WHERE IN returns values that matches values in a list or subquery. WHERE IN is shorthand for multiple OR conditions. Previous. Next. The SQL WHERE IN syntax. The general syntax is. SELECT column-names FROM table-name WHERE column-name IN (values) SUPPLIER. Id.

Documentation: 9.1: Arrays, You can also use a slice step to select all array elements whose positions are 5 rows returned sql-> mode COLUMN Query output mode is COLUMN sql->. For more information, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server). The following example calls OPENJSON and transforms the array of objects that is stored in the @json variable to a rowset that can be queried with a standard SQL SELECT statement:

Working With Arrays, PostgreSQL allows you to define a column to be an array of any valid data type including You use the SELECT statement to query array data as follows:. SELECT value FROM STRING_SPLIT('B001,INTRODUCTION TO SQL,500,100G,MID,TECH', ','); But it can be applied for our example by using a CROSS APPLY function combined with our string split. On that way, we are going to be able to use this function in on our table. SELECT ID ,value FROM BOOKS BO CROSS APPLY STRING_SPLIT(ID, ',') AS BK

PostgreSQL Array, The list of values can be a list of literal values such as numbers, strings or a result of a SELECT statement like this: value IN (SELECT column_name FROM� Use of the array constructor in the select clause is optional. If no array constructor is used, an array will still be constructed, but only if the select-clause expression does indeed return more than one item. If exactly one item is returned, the result will contain just that one item.

Comments
  • Can more than one row have the same attribute_id? If so, how do you decide which category_id to return?
  • @Nick Actually yes. But I have very complex query. I will not use select directly. I just need where part. The whole query has join on category_id. So according to my estimate, after join the result will look like the provided dummy table. I need to filter that table. I tried right join, different where's, subquery. I have no clue how to perform my task.
  • It is very surprising to know that this question hasn't asked before.
  • @Anees For me it was more surprising that I was downvoted twice. Have not clue why. However agree. I got thinking that I do things in wrong way. However no one suggested any simple solution.
  • That may be because this question looks silly at first glance. But is valid or even interesting actually (You've got my +1). There should have been a simpler solution.
  • I haven't tried it, nor am I an expert in SQL. But this doesn't look like it will work since count(*) will count the records grouped together which is equal to 1 if attribute_ids are unique, not how many unique attribute_ids are there.
  • @Anees . . . The COUNT(*) counts the number of matching categories. That seems to be exactly what the OP wants. You are misunderstanding something about SQL and GROUP BY.
  • This will not fit the first case. Your select * from category_attributes where category_attributes.attribute_id in (1,2,3, 4); will give all results. I do not want this.
  • I know how to use in. I have written invented syntax which I need.
  • Changed answer. Hope this might help.
  • Did you try your query? I got an error Operand should contain 1 column
  • I managed to run your query (with a little change). However the first case is not implemented. Parameters for the query (1, 2, 3, 4) should give no results