4

Is there any way to select row/s from an other table using a comma-separated value from the first table?

Table 1. faculty

subject
101, 102
104
103, 105

Table 2. subject

code subject
101 subject 1
102 subject 2
103 subject 3
104 subject 4
105 subject 5

Expected Output:

subject subject
101, 102 subject 1, subject 2
104 subject 4
103, 105 subject 3, subject 5

I already tried this:

SELECT faculty.subject, subject_offered.code, subject_offered.subject 
       FROM faculty 
       LEFT JOIN subject_offered 
       ON subject_offered.code 
       IN (faculty.subject)

but the rows from faculty with multiple values (comma-separated) shows NULL in the code and subject column in subject table.

my output

Kristyan
  • 43
  • 7
  • The most ideal way to express your schema / sample table data is to create a sql fiddle. Then volunteers can immediately play with **real** data and confidently post answers. [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/q/3653462/2943403) How about `FIND_IN_SET()`? I can't imagine that you are the first person on Stack Overflow to ask how to overcome this "poor table design" challenge. – mickmackusa Oct 26 '21 at 04:06
  • Are you using SQL Server? I don't see that mentioned anywhere in the question. – mickmackusa Oct 26 '21 at 04:34
  • 1
    The **real solution** is of course to change your data model. Don't store comma separated values, when you are interested in the separate values. This is against database normalization and is just not how to use a relational database. Writing queries will become easier when you fix this. They will also be faster in general. And the DBMS will be able to guarantee data consistency (i.e. that the IDs you find in the set really exist). – Thorsten Kettner Oct 26 '21 at 05:13
  • my bad, I'm using SQL server. I appreciate your suggestions – Kristyan Oct 26 '21 at 07:12

3 Answers3

4
SELECT t1.subject, GROUP_CONCAT(t2.subject)
FROM faculty t1
JOIN subjects t2 ON FIND_IN_SET(t2.code, t1.subject)
GROUP BY t1.subject;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3e40ab353ff1e0a1cde678225fa63ed2

Akina
  • 39,301
  • 5
  • 14
  • 25
  • I didn't know `FIND_IN_SET` function, this answer is much simpler and clearer than mine. Great work! – ThangLeQuoc Oct 26 '21 at 04:51
  • @ThangLeQuoc The only possible problem - one who uses CSV in string column may add excess spaces after commas (for some beauty, maybe?) which will need in removing this space symbols before applying FIND_IN_SET. – Akina Oct 26 '21 at 05:02
  • I suggest not to use alias names like this. Alias names are supposed to make a query easier to read. This is the case with mnemonic names, such as f for faculty and s for subject. It is not the case for arbitrary names like t1 and t2. Just the opposite, such alias names can make a query much harder to read than it was without them. – Thorsten Kettner Oct 26 '21 at 05:17
2

Updated

Please see the answer by @Akina for a complete correct and cleaner solution.

Although this is a very poor table design, there's still a way to get around this.

Solution

SELECT faculty_subject AS faculty_subject_codes, GROUP_CONCAT(subject ORDER BY code ASC) AS subjects
FROM (
  SELECT faculty.subject as faculty_subject, subject.*
FROM subject
JOIN faculty 
ON faculty.subject LIKE CONCAT('%', subject.code, '%')
) fa GROUP BY faculty_subject;

Give

faculty_subject_codes subjects
101,102 subject 1,subject 2
103,105 subject 3,subject 5
104 subject 4

Checkout my fiddle for your question

Explanation

Since the faculty.subject is a comma separated values, so you cannot do a regular join by subject.code, the work around is to use a JOIN with LIKE clause instead.

SELECT faculty.subject as faculty_subject, subject.*
FROM subject
JOIN faculty 
ON faculty.subject LIKE CONCAT('%', subject.code, '%');
faculty_subject code subject
101,102 101 subject 1
101,102 102 subject 2
103,105 103 subject 3
104 104 subject 4
103,105 105 subject 5

Now we have multiple rows with the same faculty_subject , e.g for code 103 and 105. The next thing is to combine those duplicated row into a single entry, we will do this with a GROUP CONCAT statement, and GROUP BY the faculty_subject

SELECT faculty_subject AS faculty_subject_codes, GROUP_CONCAT(subject ORDER BY code ASC) AS subjects
FROM (
  SELECT faculty.subject as faculty_subject, subject.*
FROM subject
JOIN faculty 
ON faculty.subject LIKE CONCAT('%', subject.code, '%')
) fa GROUP BY faculty_subject;
ThangLeQuoc
  • 2,272
  • 2
  • 19
  • 30
  • 1
    Errorneous. Add `(10, 'subject 6')`... https://www.db-fiddle.com/f/dwBwNENqBtywdgqB9LYaZu/2 – Akina Oct 26 '21 at 04:51
  • Yeah true @Akina, I'm still thinking how to solve this, probably your solution is the best then... – ThangLeQuoc Oct 26 '21 at 04:56
  • 1
    Formally `ON CONCAT(',', faculty.subject, ',') LIKE CONCAT('%,', subject.code, ',%')` may fix - but it is excessively complex. – Akina Oct 26 '21 at 05:00
  • Great! I just dont get why the subject column shows only one subject and repeats itself based on the number of values in the faculty_subject column in my device, but it works fine in the sqlfiddle – Kristyan Oct 26 '21 at 06:35
1
    SELECT  f.subject,
            GROUP_CONCAT(s.subject  ) as subject
    FROM    faculty as f
    INNER JOIN subjects s
        ON FIND_IN_SET(s.code, f.subject) 
    GROUP BY f.subject;

Run in Fiddle

TBA
  • 1,921
  • 4
  • 13
  • 26
  • How do you know that the OP is using SQL Server? Are you sitting in the same room as them? – mickmackusa Oct 26 '21 at 04:35
  • @mickmackusa Well, my apology. Obviously, you are right about this. – TBA Oct 26 '21 at 04:39
  • This answer looks A LOT like the bottom of this answer: https://stackoverflow.com/a/16508385/2943403 – mickmackusa Oct 26 '21 at 04:39
  • 1
    The question is MySQL-tagged, not SQL Server. – Akina Oct 26 '21 at 04:52
  • mickmackusa & Akina Thank you, for pointing it out. My apology. I have updated my answer to MySql – TBA Oct 26 '21 at 05:08
  • 1
    @TBA, did you edit your entire answer ( previously written in SQL server), and completely replace it with the answer from @Akina? This is literally stealing. – ThangLeQuoc Oct 26 '21 at 05:44
  • @ThangLeQuoc no way...I didn't even notice that Akina replied! I will never ever think about doing this that too in the same question :| – TBA Oct 26 '21 at 06:00