jamie shepherd jamie shepherd - 3 years ago 158
SQL Question

How to find the common value between records in SQL?

Just a little question, if I had a database that was using SQL:

Names: Max¦ John¦ May¦ Olivia

Numbers: 3, 7, 9¦ 1, 3, 4¦ 8, 5, 3¦ 5, 7, 1


Is there a way using SQL to check the "Numbers" column to see how many people like the number 5 and then show their name, who like the number, to the user.

Answer Source

Well, at first you have a bad database design, but if you still want to move on it here is how to do it:

SELECT *
FROM YourTableName
WHERE Numbers LIKE '%5%';

Result:

+--------+---------+
|  Name  | Numbers |
+--------+---------+
| May    | 8, 5, 3 |
| Olivia | 5, 7, 1 |
+--------+---------+

But I recommand to change that design to somthing like:

DECLARE @Names TABLE ( ID INT IDENTITY(1,1), Name VARCHAR(30) );
DECLARE @Numbers TABLE ( Name INT, Number INT);

INSERT INTO @Names VALUES
('Max'),
('John'),
('May'),
('Olivia');
INSERT INTO @Numbers VALUES
(1, 3),
(1, 7),
(1, 9),
(2, 1),
(2, 3),
(2, 4),
(3, 8),
(3, 5),
(3, 3),
(4, 5),
(4, 7),
(4, 1);
/**/
SELECT N.Name, NM.Number
FROM @Names N INNER JOIN @Numbers NM ON N.ID = NM.Name
WHERE NM.Number = 5;

Result:

+--------+--------+
|  Name  | Number |
+--------+--------+
| May    |      5 |
| Olivia |      5 |
+--------+--------+

Demo1

Demo2

Here is also a good article to start with.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download