Mysql select query fetch rows containing multiple ids
your query is logically wrong :
you can use OR
or In
like below:
SELECT * FROM `users` WHERE `id`=10 OR `id`=21
or
SELECT * FROM `users` WHERE `id` IN ( 10 , 21 )
How to get values from table using multiple ID's in single column in SQL?
Two tricks are needed. One is joining based on a comma-separated list of IDs. That can easily be done poorly resulting in unwanted matches such as 1 and 2 matching 12. The article Stephen Jennings referenced has some good reliable solutions.
The second is concatenating a collection of results into a single string. For recent versions of SQL Server, the STRING_AGG is the preferred solution. For older versions (such as 2014) the most common method is the "FOR XML" trick.
I've combined the two techniques below.
DECLARE @Tbl_Process TABLE(ID INT, process_Name VARCHAR(100))
INSERT @Tbl_Process
VALUES (1, 'Item 1'), (2, 'Item 2'), (3, 'Item 3'), (4, 'Item 4'), (5, 'Item 5'), (12, 'Item 12')
DECLARE @Tbl_av TABLE(ID INT, ProcessId VARCHAR(100))
INSERT @Tbl_av
VALUES (1, '1,3,5'), (2, '2,4'), (3, '1,2,3'), (4, '1,5'), (5, ''), (6, '3,4,12')
SELECT AV.*, PL.*
FROM @Tbl_av AV
CROSS APPLY (
SELECT ISNULL(STUFF(
(
SELECT ',' + P.process_Name
FROM @Tbl_Process P
--(bad) WHERE CHARINDEX(CONVERT(VARCHAR, P.ID), AV.ProcessId) > 0
WHERE ',' + AV.ProcessId + ',' LIKE '%,' + CONVERT(VARCHAR, P.ID) + ',%'
ORDER BY P.ID -- alternately ORDER BY P.process_Name
FOR XML PATH(''), TYPE
).value('text()[1]','nvarchar(max)')
, 1, 1, '')
, '(none)')
AS Process_Names
) PL
Results
ID | ProcessId | Process_Names |
---|---|---|
1 | 1,3,5 | Item 1,Item 3,Item 5 |
2 | 2,4 | Item 2,Item 4 |
3 | 1,2,3 | Item 1,Item 2,Item 3 |
4 | 1,5 | Item 1,Item 5 |
5 | (none) | |
6 | 3,4,12 | Item 3,Item 4,Item 12 |
Sql Query to get an item that contains multiple ids from another table
Instead of distinct, you use group by and use count to verify that all required skus were present:
SELECT c.ContainerId
FROM Container c
JOIN Inventory i ON i.Container = c.ContainerId
JOIN SKU s ON s.SkuId = i.Sku AND s.SkuId in (S1, S2)
GROUP BY c.ContainerId
HAVING COUNT(DISTINCT s.SkuId)=2
But, as commented by others, there's no need to use any table but Inventory:
SELECT i.Container
FROM Inventory i
WHERE i.Sku in (S1, S2)
GROUP BY i.Container
HAVING COUNT(DISTINCT i.Sku)=2
how can I fetch the specific ids data( multiple ids are stored in list) using Django
You can use filter(pk__in=pk)
.
Get All Records With Single ID From Multiple IDs In PHP
Use FIND_IN_SET()
for comma separated value in table
SELECT *
FROM `tablename`
WHERE FIND_IN_SET(2,`category_id`)
UPDATED
As per documentation - FIND_IN_SET's second argument is a comma separated list. So the value 2, 7 being split by a comma and space
Solution: stop using this approach and normalize your schema to use one-to-many (or many-to-many).
OR
remove space from your comma separated value
Fetch data from same Mysql column with multiple Ids
Most of case id column is integer,
then remove single quotes from following line
change this
$sql = "SELECT credit FROM dailycredits Where id IN ('1','2','3')";
to
$sql = "SELECT credit FROM dailycredits Where id IN (1, 2, 3)";
Note: Single quotes removed from IN
Try this...
$sql = "SELECT credit FROM dailycredits Where id IN (1,2,3)";
$result = $connect->query($sql);
$rows = []; //empty array.
while ($row = mysql_fetch_array($result)){
$rows[] = $row; //assigning credit to array.
}
print_r($rows);
Or you can now access like $rows[0], $rows[2].
Since mysql_ is deprecated in php 5.5.0, I'm strongly not recommend to use mysql_, use mysqli_* instead.
MySQL Get exactly one entry per id by multiple ids
If you want the max date record for each of the two IDs, then you may use a subquery:
SELECT t1.*
FROM yourTable t1
INNER JOIN
(
SELECT userId, MAX(date) AS max_date
FROM yourTable
WHERE userId IN (7243, 1)
GROUP BY userId
) t2
ON t1.userId = t2.userId AND t1.date = t2.max_date
WHERE
t1.userId IN (7243, 1);
This is the just greatest-value-per-group question with a slight twist, namely that you only want to see two of the possible groups in the output.
As @Raymond commented below, an index on (userId, date)
should greatly speed up the t2
subquery. I am not sure if this index would help beyond that, but it should make a difference.
Related Topics
Regex for No More Than 5 Digits or Contain String
How to Remove a Directory That Is Not Empty
Increase a Value Like = +1 in MySQL and PHP
Laravel 6: Call to Undefined Method App\\User::Createtoken()
Sum Values from Column in HTML
Keep Values Selected After Form Submission
Change the Maximum Upload File Size
How to Sort a Multidimensional Array in PHP
Comparing Date from Variable to MySQL Date
How to Prevent Mails Sent Through PHP Mail() from Going to Spam
Easiest Way to Replace All Characters in Even Positions in a String.
What Is the Regular Expression to Validate a Comma Delimited List But Ending With '&' and a Word