Catch all the data with the same id without knowing the id

Question

Catch all the data with the same id without knowing the id

Navigation

#1 by (4 votes)

1

I have a table in the database with information that has the same ID, as if it were a set, I would like to get all the information with this ID, but I do not have it, and I'm using a LIKE to fetch terms from this set, something like this:

SELECT * FROM josyo_rsform_submission_values WHERE FieldName LIKE :term

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

Example:

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

mysql sql

asked by anonymous 22.06.2017 / 02:02

1 answer

How to enable click on every cell in a table by opening a file on a new page Change placeholder checking radio with jquery

score 4 · 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 query

Okay, let's explain little by little.

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

We can get the id of the 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 in a table that match this information. If I treat as different tables, t1 and t2 , I need all the information of t2 that join t1 through id . This way of thinking indicates that we can use join ; just adding the information of a t1 and t2 any would be like this:

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

If we only want information from t2 :

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

Using the correct name for the tables:

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 was not very clear in the first version of the answer how a join of a table works on itself

Explaining self-join

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

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

I can get all elements in X that have id = 1 . The notation at relational algebra looks something like:

Sotheresultofthisalgebraicexpressionis:

(1,12)(1,100)

So,whatwouldbetheresultofthefollowingexpression?

Inparts:

ontheleftside,wehavetheselectionfrombefore

Giventhisselection,Imakearenameofidtoid1;thismeansthattheresultofthisoperationwillnowhavecolumnid1andcolumnvalue

naturaljoinoperationoftheleftset(wejustdefineditinthepreviousstep)withtherightset,yettobedefined;thejoinisdoneusingid1=id2

fromXdatasetIrenamefromidtoid2;notethatinnoneoftheoperationsuntilnowhasthevalueofthedatasetXchanged

Displayingtheresultsofeachoperation:

Ontheleftside,wehavetheselectionfrombefore

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

Giventhisselection,Imakearenameofidtoid1;thismeansthattheresultofthisoperationwillnowhavecolumnid1andcolumnvalue

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

ofdatasetXImaketherenameofidtoid2;notethatinnoneoftheoperationssofarhasthevalueofthedatasetXchanged

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

Aboutstep3...AjoinismadeupofaCartesianproductfollowedbyaselection,soIwilldivideitintotwosteps3(3.aand3.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 join is done 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 by id = 1 , but by value = 100 . Thus, 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% rename operation is done in the data set, not in the column), but the general idea is this.