Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Jan 2010
    Posts
    101
    Thanks
    17
    Thanked 5 Times in 5 Posts

    Order by name of columns

    I've revised my question, please re-read!

    Ok, so basically I've made this a very straight forward question that I hope can be answered accordingly. Please disregard the following two posts because they pertained to the question I had asked previously. Here's my new question: How can I combine these queries into a single statement? Would it be best practice to do so?

    Code:
    SELECT * FROM `game_titles` WHERE `name` LIKE '%search%' ORDER BY `name` ASC LIMIT 2; SELECT * FROM `teams` WHERE `name` LIKE '%search%' ORDER BY `name` ASC LIMIT 2; SELECT * FROM `users` WHERE `username` LIKE '%search%' ORDER BY `username` ASC LIMIT 2;

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,155
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Umm...your basic query is hosed, right to begin with. You will end up getting MANY MANY MANY times more records than you expect. You are doing a "FULL JOIN* of the three tables.

    Just for instance, if you got
    Foo
    Bar
    from the first table,
    ABC
    DEF
    GHI
    from the second table
    TUV
    XYZ
    from the third table, you would end up with 2 times 3 times 2 records!

    That is. you would end up with
    Foo ABC TUV
    Foo DEF TUV
    Foo GHI TUV
    Foo ABC XYZ
    Foo DEF XYZ
    Foo GHI XYZ
    Bar ABC TUV
    Bar DEF TUV
    Bar GHI TUV
    Bar ABC XYZ
    Bar DEF XYZ
    Bar GHI XYZ

    And that's just an example showing one field per table!

    Let's start all over and do what I *THINK* you want:
    Code:
    SELECT 1 AS tablenum, fieldA, fieldB FROM tableA WHERE name LIKE '$phpvar%'
    UNION 
    SELECT 2, fieldAA, fieldBB FROM tableB WHERE name LIKE '$phpvar%'
    UNION
    SELECT 3, fieldAAA, fieldBBB FROM tableC WHERE username LIKE '$phpvar%'
    ORDER BY tablenum, fieldA, fieldB
    You need a UNION.

    But you must be careful when using a UNION.

    You must supply the SAME NUMBER of fields in each SELECT of a UNION. And, further, the fields *TYPES* must be the same (or convertible to the same).

    You can see that adding an extra dummy field that numbered the tables and then using it in the ORDER BY accomplished your first mission.

    Your second mission is trickier.

    If tableA has an ID field, then *ALL THREE SELECTs* must supply an ID field!
    If tableB has some field to be sorted alphabetically, then so must *ALL THREE SELECTs*.
    ANd the same goes for the time field of tableC.

    FURTHER... In the ORDER BY clause, you can *ONLY* use field names from the *FIRST* SELECT (and your PHP code will also only "see" names from the first SELECT).

    NO, you can *NOT* do
    Code:
    SELECT 1 AS tablenum, ID FROM tableA WHERE name LIKE '$phpvar%'
    UNION 
    SELECT 2, name FROM tableB WHERE name LIKE '$phpvar%'
    UNION
    SELECT 3, timefield FROM tableC WHERE username LIKE '$phpvar%'
    ORDER BY tablenum, fieldA, fieldB
    Because ID, name, and timefield do *NOT* have the same data types.

    What you want *CAN* be done, but it's trickier.

    If you can express your needs in terms of a real example, maybe we can come up with something.

  • Users who have thanked Old Pedant for this post:

    iLochie (07-06-2011)

  • #3
    Regular Coder
    Join Date
    Jan 2010
    Posts
    101
    Thanks
    17
    Thanked 5 Times in 5 Posts
    I've changed my question, please refer to the OP!

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    You would use a UNION like described in the first reply.

  • Users who have thanked guelphdad for this post:

    iLochie (07-06-2011)

  • #5
    Regular Coder
    Join Date
    Jan 2010
    Posts
    101
    Thanks
    17
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by guelphdad View Post
    You would use a UNION like described in the first reply.
    Ahh, ok thanks for clearing that up, and thank you Old Pedant for the solution!

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,155
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    But with one difference: In MYSQL, when you want the SELECT branches of a UNION to have their own ORDER BY and LIMIT clauses, you must put parenstheses around each.

    (This makes sense: Without the parens, the last ORDER BY would be assumed to apply to the entire UNION.)

    *AND* you can still force the results to be ordered by table using the trick I showed.

    Note that you *STILL* must have the same number AND TYPES of fields in each SELECT. So, in general, you can never use SELECT * as part of a UNION (there will of course be exceptions in very simple cases, but don't depend upon their existence).

    Code:
    ( SELECT 1 AS tablenum, name, fieldX, fieldY FROM game_titles 
      WHERE name LIKE '%search%' ORDER BY name ASC LIMIT 2 )
    UNION
    ( SELECT 2, name, fieldA, fieldB FROM teams
      WHERE name LIKE '%search%' ORDER BY name ASC LIMIT 2 )
    UNION
    ( SELECT 3, username, field1, field2 FROM `users` 
      WHERE `username` LIKE '%search%' ORDER BY `username` ASC LIMIT 2 )
    ORDER BY tablenum;
    That will *probably* do what you want.

    And next time, please don't go edit your original post, thus rendering useless any responses you got to it. Post a new version of your question, instead, if needed.

  • Users who have thanked Old Pedant for this post:

    iLochie (07-07-2011)

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,155
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Remember, that in PHP code that reads the results of that UNION, *only* the field names from the first SELECT will be "visible". So, for example, the 5th and 6th records in your result set will *still* have a field NAME of "name" even though, in the third SELECT there, the field name is "username".

  • Users who have thanked Old Pedant for this post:

    iLochie (07-07-2011)

  • #8
    Regular Coder
    Join Date
    Jan 2010
    Posts
    101
    Thanks
    17
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by Old Pedant View Post
    Remember, that in PHP code that reads the results of that UNION, *only* the field names from the first SELECT will be "visible". So, for example, the 5th and 6th records in your result set will *still* have a field NAME of "name" even though, in the third SELECT there, the field name is "username".
    Thanks, your posts have been very useful in helping me come to a resolution! I've worked out a query that is based on your second last post and everything is working perfectly! Thank you very much once again!


  •  

    Posting Permissions

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