...

View Full Version : Order by name of columns



iLochie
07-06-2011, 02:28 AM
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?


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;

Old Pedant
07-06-2011, 03:01 AM
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:


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

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.

iLochie
07-06-2011, 07:48 AM
I've changed my question, please refer to the OP!

guelphdad
07-06-2011, 02:32 PM
You would use a UNION like described in the first reply.

iLochie
07-06-2011, 09:10 PM
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!

Old Pedant
07-06-2011, 09:12 PM
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).



( 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.

Old Pedant
07-06-2011, 09:15 PM
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".

iLochie
07-07-2011, 08:03 PM
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!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum