How to Select Multiple Values in One Field MySQL

MYSQL: Select Query with multiple values from one column

You need to GROUP BY BookID and a HAVING clause with the condition that both keywords are linked to that BookID:

SELECT b.BookID, b.Title 
FROM Books b
INNER JOIN KeywordAssignment ka ON ka.BookID = b.BookID
INNER JOIN Keyword k ON k.KeywordID = ka.KeywordID
WHERE k.Keyword IN ('Magic', 'Fantasy')
GROUP BY b.BookID, b.Title
HAVING COUNT(DISTINCT k.Keyword) = 2

This code will return books that are linked to both 'Magic' and 'Fantasy'.

If you want either of the 2 keywords then remove the HAVING clause.

How to select multiple values from one MySQL table column with PHP

You could perform it with PHP like this:

foreach ($values as $key => $value) {
    $cars = explode(',', $value['car']);
    foreach ($cars as $car) {
        switch ($car) {
            case 'toyota':
                $values[$key]['car'] = $car;
                break;

            default:
                # code...
                break;
        }
    }
}

I recommend you remodeling your database, transforming this data in a
many-to-many relationship.

See also:

• explode()
• Many-to-many relationships examples

How to select multiple values for one table "cell" MySQL

You could use the group_concat aggregate function:

SELECT   playerno, GROUP_CONCAT(DISTINCT teamno)
FROM     matches
GROUP BY playerno
ORDER BY playerno;

Select Multiple rows in single column separated by New Line

This should do

SELECT name, GROUP_CONCAT(fruit SEPARATOR '\n') FROM your_table GROUP BY name

Demo in db<>fiddle

Update to add numbering:

SELECT name ,
       GROUP_CONCAT(CONCAT (rn,')',fruit) SEPARATOR '\n')
FROM (
    SELECT * 
        ,ROW_NUMBER() OVER (PARTITION BY name) AS rn
    FROM your_table
    ) SQ
GROUP BY name

Demo with numbering in db<>fiddle

Searching with multiple values in one column of Mysql table

SELECT countryID FROM country WHERE countryName IN ("value1", "value2", "value3")

As many values as you like.

How to select data from one column has multiple values using join from other table?

Your database schema is not normalized. The first normal form tells that you need to have atomic values in the field. Also considering other normal forms, you need to have separate User table and UserCity table.

If you normalize your table, then your queries will become simpler.

User Table

--------+----------
| UserId UserName  |
--------+----------
|   1   | Harry   |
|   2   | Peter   |
-------------------

UserCity Table (It is Bridge table)

--------+----------
| UserId CityId |
--------+----------
|   1   | 1     |
|   1   | 2     |
-------------------

Now, you can easily query

SELECT u.UserName as traveller_name, group_concat(c.cityname,',') as  travelled_city
FROM User as u
JOIN UserCity as uc
ON u.UserId = uc.UserId
JOIN City as c
ON uc.CityId = c.CityId
GROUP BY u.UserName

How to select multiple values in one column many to many relationship

Example - 1 (query) -

select p.* from product p
where p.id in (
select distinct pa.product_id 
from attribute a join product_attribute pa
on 
pa.attribute_id = a.id
and (a.name,a.value) in (('branch','Acer'),('screen','13 Inch'))
)

Example - 1 (Query) - similar to above, slightly different approach

select distinct p.*
from product p join product_attribute pa
on p.id = pa.product_id 
join attribute a
on a.id = pa.attribute_id
and (a.name,a.value) in (('branch','Acer'),('screen','13 Inch'))

Example -2 (query) -

select distinct p.*
from product p join product_attribute pa
on p.id = pa.product_id 
join attribute a
on a.id = pa.attribute_id
and (a.name,a.value) in (('branch','Acer'),('branch','Dell'), ('screen','13 Inch'),('screen','15.6 Inch'))

Modified query to select products with both attributes -

select distinct p.*
from product p join product_attribute pa1
on p.id = pa1.product_id 
join product_attribute pa2
on pa1.product_id = pa2.product_id
and 
pa1.attribute_id in 
(select a.id from attribute a where (a.name,a.value) in (('branch','acer'),('branch','Dell')))
and pa2.attribute_id in 
(select a.id from attribute a where (a.name,a.value) in (('screen','13 Inch'),('screen','15.6 Inch')))

You can refer to the db fiddle here

Required: Matching attrbutes should be atleast given in query or more.

select distinct p.*
from product p 
where p.id in 
(
select pa1.product_id from 
product_attribute pa1,
product_attribute pa2,
product_attribute pa3
where 
pa1.attribute_id in 
(select a.id from attribute a where (a.name,a.value) in (('branch','acer'),('branch','Dell')))
and pa2.attribute_id in
(select a.id from attribute a where (a.name,a.value) in (('screen','13 Inch'),('screen','15.6 Inch')))
and pa3.attribute_id in
(select a.id from attribute a where (a.name,a.value) in (('need','gaming')))
and pa1.product_id = pa2.product_id
and pa2.product_id = pa3.product_id
and pa1.product_id = pa3.product_id
)

Fiddle for above query

Two - Matching attributes:

Four - Matching attributes:

---

Related Topics