Get all data with the same id without knowing the id

Asked

Viewed 355 times

2

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

1 answer

5

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 N^2. 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:

X selecionado por id = 1

Thus, the result of this algebraic expression is:

(1, 12)
(1, 100)

So what would be the result of the following expression?

X selecionado por id = 1, renomeando id para id1; X renomeando id para id2; junção natural de id1 e id2

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)

  1. 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)
    
  2. 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:

X selecionado por value = 100, renomeando id para id1; X renomeando id para id2; junção natural de id1 e id2

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 renaming is done in the dataset, not in the column), but the general idea is this.

  • All the data is from the same table, so it would just repeat t1 instead of t2, right?

  • @NGTHM4R3, I don’t understand your comment. I can try to reformulate my answer in a more mathematical approach to make everything clearer, but I still don’t understand this repetition of t1 in place of t2

  • 1

    you used t1 and t2 as examples of two different tables, but the data is only in t1, in case the relation would be from t1 to t1

  • I understand your doubt. This may seem a little confusing at first glance, self-join, but it’s plausible. I took two copies of the same dataset (say so); the dataset x can be called t1, and I can get a copy of x and call it t2, thus making a Join of x in x. I will try to update my answer in more depth

  • 1

    @NGTHM4R3, updated my answer and put some relational algebra. Roughly speaking, SQL tries to implement this algebra, so the thoughts of algebra can be applied in SQL

  • I’m sorry, but I haven’t been able to implement it yet, it got a little confusing

Show 1 more comment

Browser other questions tagged

You are not signed in. Login or sign up in order to post.