SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hello all, I have the following table structure:
    Code:
    gateway 	      setting 	       value  	order
      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".

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

    Ahh!!!!!

    Can I have a push please.

    Thanks in advance,
    Márcio
    Last edited by oikram; Sep 24, 2010 at 06:55. Reason: changed the formulation sentence..

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code MySQL:
    SELECT value 
    FROM table
    WHERE gateway IN
       (SELECT DISTINCT gateway
        FROM table
        WHERE setting='visible
        AND value='on'
       )

  3. #3
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi.

    That will not work, even if I put the '

    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.


  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Of course you get them all, you'll have to add some WHERE conditions to the main query
    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

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,249
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    but storing stuff with maximum flexibility also has its down side -- it becomes increasingly difficult to pull meaningful information out of it
    Well... I need to use this table, but I have no way to change it's structure.


    Quote Originally Posted by r937 View Post
    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)
    Ok.

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    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:
    Code MySQL:
    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'

  8. #8
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Of course you get them all, you'll have to add some WHERE conditions to the main query
    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
    lol... ok... true.

    I will give a second try then.

    I will need some time however, but don't worry, I'm doing it.

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by oikram View Post
    lol... ok... true.

    I will give a second try then.

    I will need some time however, but don't worry, I'm doing it.
    See my final solution above... Rudy made me do it...

  10. #10
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Code MySQL:
    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'
    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

  11. #11
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code MySQL:
    SELECT t1.value
    FROM `table` AS t1
    INNER JOIN `table` AS t2 ON t1.gateway = t2.gateway

    Not getting.

    1)
    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 ... ?

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


    Thanks in advance,
    Márcio

  12. #12
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    #1 fix up your table, it's really bad design.

    #2 use this code, if your REALLY cannot change that table.
    Code:
    SELECT value FROM foo WHERE gateway IN (SELECT gateway FROM foo WHERE setting = 'visible' and value = 'on') AND setting = 'name'

  13. #13
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


    Márcio

  14. #14
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by oikram View Post
    [HIGHLIGHT="MySQL"]
    1)
    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 ... ?
    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.
    2)
    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:
    Code MySQL:
    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':
    Code:
    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':
    Code:
    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

  15. #15
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I haven't read all but...

    Quote Originally Posted by guido2004 View Post
    No.
    I've put WHERE conditions on both tables. So from table t1 I take all rows where setting = 'name':
    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:

    Code MySQL:
    SELECT t1.value
    FROM `table` AS t1
    INNER JOIN `table` AS t2 ON t1.gateway = t2.gateway
    with this:

    Code MySQL:
    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.


    Regards,
    Márcio

  16. #16
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by oikram View Post
    In the first case, we DON'T get empty values.
    In the second case, we get.
    Empty values? Care to give the test data in the table, and the result set of those two queries?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •