Get all data with the same id without knowing the id

Question

I have a table in the database with information that have the same ID, as if they were a set, I would like to get all the information with this ID, but I don’t have it, and I’m using a LIKE to search for terms of this set, something like this:

SELECT * FROM josyo_rsform_submission_values WHERE FieldName LIKE :term

However this only returns me the id of the information and only one information, I would like the result of the search to be returned all the information with the ID of the search, I do not understand much of SQL, but I believe that it is some kind of relation

Example:

ID / FieldName / FieldValue
20 / Carro / Ferrari
20 / Nome / Virgulino
20 / Sexo / Masculino

Answer 1

You can get this information by making a join of the table with itself:

SELECT
    t2.*
FROM
    josyo_rsform_submission_values t1 
    INNER JOIN josyo_rsform_submission_values t2
        ON (t1.id = t2.id)
WHERE
    t1.FieldName LIKE :term

Construction of the consultation

Okay, let’s break it down.

We want to take all the tuples that have one id. For that, we need to get the id somehow, but the basic information we have is FieldName.

We can get the id row of this table that contains this information as you did above:

SELECT
    id
FROM
    josyo_rsform_submission_values
WHERE
    FieldName LIKE :term

Okay, now we need to get all the rows of a table that match this information. If I treat as different tables, t1 and t2, I need all the information t2 who join the t1 through the id. This way of thinking indicates that we can use a join; just gather the information of a t1 and t2 any would be so:

SELECT
    *
FROM
    t1
    INNER JOIN t2
        ON (t1.id = t2.id)

If we just want the information from t2:

SELECT
    t2.*
FROM
    t1
    INNER JOIN t2
        ON (t1.id = t2.id)

Using the correct table name:

SELECT
    t2.*
FROM
    josyo_rsform_submission_values t1
    INNER JOIN josyo_rsform_submission_values t2
        ON (t1.id = t2.id)

Filtering by the desired information in t1:

SELECT
    t2.*
FROM
    josyo_rsform_submission_values t1
    INNER JOIN josyo_rsform_submission_values t2
        ON (t1.id = t2.id)
WHERE
    t1.FieldName LIKE :term

UPDATE: it had not become very clear in the first version of the answer how it works to join a table in itself

Explaining the auto junction

Let’s take a data set. Let’s say that it is the set X. For ease of understanding, my dataset belongs to . I will also say that the first field of this data is called id, and the second field is called value. My dataset X is:

(1, 12)
(1, 100)
(2, 15)
(2, 37)
(2, 0)

I can get all the elements in X who have id = 1. The notation in relational algebra is something like:

Thus, the result of this algebraic expression is:

(1, 12)
(1, 100)

So what would be the result of the following expression?

Split:

1. on the left, we have the selection of before
2. given this selection, I rename id for id1; this means that the result of this operation will now have the column id1 and the column value
3. natural joint operation of the left set (we just set it in the previous step) with the right set, yet to be set; the join is made using id1 = id2
4. of the data set X rename id for id2; note that in none of the operations so far the value of the data pool X was amended

Showing the results of each operation:

1. on the left, we have the selection of before

   [id, value]
   (1, 12)
   (1, 100)
   

2. given this selection, I rename id for id1; this means that the result of this operation will now have the column id1 and the column value

   [id1, value]
   (1, 12)
   (1, 100)
   

3. of the data set X rename id for id2; note that in none of the operations so far the value of the data pool X was amended

   [id2, value]
   (1, 12)
   (1, 100)
   (2, 15)
   (2, 37)
   (2, 0)
   

On step 3... A junction is composed of a Cartesian product followed by a selection, so I will divide in step 3 (3.a and 3.b)

3. a. with the right set

   [id1, value, id2, value]
   (1,   12,    1, 12)
   (1,   12,    1, 100)
   (1,   12,    2, 15)
   (1,   12,    2, 37)
   (1,   12,    2, 0)
   
   (1,   100,   1, 12)
   (1,   100,   1, 100)
   (1,   100,   2, 15)
   (1,   100,   2, 37)
   (1,   100,   2, 0)
   

4. b. the joint is made using id1 = id2

   [id1, value, id2, value]
   (1,   12,    1, 12)
   (1,   12,    1, 100)
   (1,   100,   1, 12)
   (1,   100,   1, 100)
   

Now imagine that you no longer want to filter through id = 1, but for value = 100. So we would have the following expression:

The result of this operation is:

[id1, value, id2, value]
(1,   100,   1, 12)
(1,   100,   1, 100)

And that’s more or less what you wanted in the beginning. There are some changes when you switch from relational algebra to SQL (for example, the operation rô renaming is done in the dataset, not in the column), but the general idea is this.