Build a select query based on a given table structure - a hard case - (so I believe)

Hello all, I have the following table structure:


[B]gateway[/B] 	      [B]setting[/B] 	       [B]value[/B]  	[B]order[/B]
  paypal 	        name 	       PayPal 	  2
  banktransfer 	        name           Transfer   1
  banktransfer 	        type 	       Invoices   0
  banktransfer 	        visible        on 	  0
  paypal 	        visible        on 	  0

I would like to retrieve “PayPal” and “Transfer” only if the setting has visible and value has ‘on’ on that specific “gateway”.


SELECT value FROM 'table' 
WHERE ... (
SELECT DISTINCT gateway 
AND name !="" 
AND (setting='visible AND value='on') 

Ahh!!! :sick:

Can I have a push please. :smiley:

Thanks in advance,
Márcio

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 :wink: ). 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 :wink:

I haven’t read all but…

Sorry my bad there. I was trying to compare, without even going to the WHERE clause, so before the WHERE clause, if we just compare:

SELECT t1.value
FROM `table` AS t1
INNER JOIN `table` AS t2 ON t1.gateway = t2.gateway

with this:

SELECT value
FROM `table`

In the first case, we DON’T get empty values.
In the second case, we get.

It is somehow implicit that, when we join, we DO NOT join empty values perhaps?

I will review your answers later on, in order to properly understand.
Thanks a lot for your patience guido, really. :slight_smile:

Regards,
Márcio

Well… I need to use this table, but I have no way to change it’s structure.

Ok. :stuck_out_tongue:

your table design is sub-optimal

you have over-generalized your data, and shoved everything into what is called an “entity-attribute-value” scheme

this makes it real easy to store stuff about anything, and what’s more, you can write the app so that new requirements can be added without any change to the database

lovely… as far as that goes

but storing stuff with maximum flexibility also has its down side – it becomes increasingly difficult to pull meaningful information out of it

i know how to solve this type of problem, but i will let guido carry on, as he is sort of on the right track (although i would prefer he use joins instead of subqueries, which can sometimes perform poorly)

Of course you get them all, you’ll have to add some WHERE conditions to the main query :slight_smile:
Right now, the subquery gets all the gateways that have setting ‘visible’ and value ‘on’, and the main query gets all rows with those gateways.
I only gave you a push, just like you asked. Now all you have to do is add a bit to get only the rows you want :slight_smile:

Hi,

Vali I struggle myself with that question. I’m newbie, I’m not a department. I don’t have the resources that this company has. And I still notice how strange this “let’s put all in one place with zero normalization” table is, even given credit to what Sir r937 pointed.

So, no, I cannot change it. It’s associated to an encrypted application. So I have to deal with it as it is.

About the second point, well, the query provided by Guido works like a charm. And it uses a very standard way of dealing with this, the so called “Self Join” that, I would like to better understand.

So, my question was only, that of understand the code provided by Guido2004. :slight_smile:

Márcio

#1 fix up your table, it’s really bad design.

#2 use this code, if your REALLY cannot change that table.


SELECT value FROM foo WHERE gateway IN (SELECT gateway FROM foo WHERE setting = 'visible' and value = 'on') AND setting = 'name'

That’s more then a push!
Ok… still I need to understand it… give me some time to try to read it…

Thanks a lot,
Márcio

See my final solution above… Rudy made me do it… :lol:

Empty values? Care to give the test data in the table, and the result set of those two queries?


SELECT t1.value
FROM `table` AS t1
INNER JOIN `table` AS t2 ON t1.gateway = t2.gateway

Not getting. :confused:

We need the alias. First of all. So, it’s mandatory. Why? According to mysql manual it’s with that alias that the temporary data will be stored … ?

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?

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?

I do have more questions… but I just stopped here. :slight_smile:

Thanks in advance,
Márcio

:injured: Sort of?

(although i would prefer he use joins instead of subqueries, which can sometimes perform poorly)

I would have, but then I decided not to change the OP’s original query. If a join makes for better performance:


SELECT DISTINCT t1.value
FROM table AS t1
INNER JOIN table AS t2
ON  t1.gateway = t2.gateway
WHERE t2.setting = 'visible'
AND   t2.value = 'on'
AND   t1.setting = 'name'

lol… ok… true. :slight_smile:

I will give a second try then. :slight_smile:

I will need some time however, but don’t worry, I’m doing it. :slight_smile:

Hi.

That will not work, even if I put the ’ :slight_smile:

With that, I get strange results actually and I cannot understand what the logic is but, I get all fields from values column, not only “Paypal” and “Transfer” , I also get the undesirable Invoices and the on’s.

I realise that this is hard to explain, but I can clear more things, no problem of course, just let me know.

:shifty:


SELECT value 
FROM table
WHERE gateway IN
   (SELECT DISTINCT gateway
    FROM table
    WHERE setting='visible
    AND value='on'
   )