When you join a table with itself, you have to give aliasses to the two tables (maybe only to one, but I always give one to both of them, with the numbers 1 and 2, makes it easier for me to follow myself ). You have to use aliasses, because otherwise MySQL can’t know from which table you want to use the columns in the SELECT, WHERE and other clauses. Because for the sake of the query, it considers the two ‘instances’ of the same table used in the query as two different tables.
We are telling, join table with it self, and what will be the condition?
That the gateway of t1 corresponds to the gateway of t2.
Yes?
Yes.
So…
3)
Since we are referring to the same table, why are we retrieving less then what we would if we do:
SELECT value
FROM `table`
I mean, on this table, if I list the values there, I do get empty value column values as well.
Shouldn’t we get exactly the same records?
No.
I’ve put WHERE conditions on both tables. So from table t1 I take all rows where setting = ‘name’:
gateway setting value order
paypal name PayPal 2
banktransfer name Transfer 1
From table t2 I take all rows where setting = ‘visible’ and value = ‘on’:
gateway setting value order
banktransfer visible on 0
paypal visible on 0
Then I join those lines and get only those that have the same gateway.
Btw, probably MySQL first joins all rows on the gateway column, and then selects those rows that have all WHERE conditions I specified, but the end result is the same, and this way it was easier to explain