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:
- on the left, we have the selection of before
- 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
- 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
- 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:
on the left, we have the selection of before
[id, value]
(1, 12)
(1, 100)
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)
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)
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)
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.
All the data is from the same table, so it would just repeat t1 instead of t2, right?
– Thiago
@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 oft2
– Jefferson Quesado
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
– Thiago
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 calledt1
, and I can get a copy ofx
and call itt2
, thus making a Join ofx
inx
. I will try to update my answer in more depth– Jefferson Quesado
@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
– Jefferson Quesado
I’m sorry, but I haven’t been able to implement it yet, it got a little confusing
– Thiago