Page 1 of 1

Quering works except need to sort results by other table...

Posted: Tue Jun 19, 2007 9:07 am
by kaisellgren
Hi,

I have the following query:

Code: Select all

SELECT plugin_id FROM tc_plugin_lists WHERE (page_id='1' AND position='0') ORDER BY tc_plugins.sort_number ASC;
This almost works, the problem is the ORDER BY clause. It should order the results by the sort_number field from another table called tc_plugins. Now it throws an error...

SELECT items FROM other table ORDER BY other tables field values where field is same as in 'items' ASC

?

Posted: Tue Jun 19, 2007 9:14 am
by Gente
It should order the results by the sort_number field from another table called tc_plugins.
So it must be a relation between this tables. Use JOIN (LEFT or INNER) and order the result by required field

Posted: Tue Jun 19, 2007 9:16 am
by kaisellgren
Gente wrote:
It should order the results by the sort_number field from another table called tc_plugins.
So it must be a relation between this tables. Use JOIN (LEFT or INNER) and order the result by required field
Can you be a little more specific?

I have tried:

Code: Select all

SELECT * FROM tc_plugin_lists LEFT JOIN (SELECT sort_number FROM tc_plugins WHERE id=(SELECT plugin_id FROM tc_plugin_lists WHERE (position='0' AND page='0'))) ORDER BY sort_number ASC;
But it gives me error: Every derived table must have its own alias

What's the difference between INNER and LEFT? I can't seem to find them from manual... =/

Posted: Tue Jun 19, 2007 9:31 am
by superdezign
The problem is that tc_plugin_lists and tc_plugins are not the same table. So, the sort_number in tc_plugins doesn't affect tc_plugin_lists at all because there is no relation between them. JOIN is what you use to create a relation.

Code: Select all

SELECT plugin_id FROM tc_plugin_lists LEFT JOIN tc_plugins ON tc_plugin_list.page_id = tc_plugins.id WHERE (page_id='1' AND position='0') ORDER BY tc_plugins.sort_number ASC;
Replace "tc_plugin_list.page_id = tc_plugins.id" with whatever the relation is between the two tables.

Posted: Tue Jun 19, 2007 9:33 am
by Gente
Ok. Can you tell me what is the relation between your tables?
You must have it because without it your task doesn't have a sense.
For example
`plugin_id` field in `tc_plugin_lists` corresponds to `x_field` in `tc_plugins`

Posted: Tue Jun 19, 2007 9:35 am
by kaisellgren
Hi,

Both tables have plugin_id,

tc_plugins has id and tc_plugin_list has plugin_id which should be the same.

I runned the code above but gave me "#1054 - Unknown column 'tc_plugin_list.plugin_id' in 'on clause' ". hmm...

EDIT: Mistype, it works now hallelujah!

Thanks guys :)

Posted: Tue Jun 19, 2007 10:20 am
by kaisellgren
Hi!

Sorry to trouble yet again :D, but I realised that my sorting is not so simple... all plugins can be sorted different way on different places so I had to create totally new table for sortings too.

Now my query is like:

Code: Select all

SELECT * FROM tc_plugin_lists LEFT JOIN tc_plugins ON tc_plugin_lists.plugin_id = tc_plugins.id LEFT JOIN tc_plugin_sort ON tc_plugin_lists.position = tc_plugin_sort.position AND tc_plugin_lists.plugin_id = tc_plugin_sort.plugin_id AND tc_plugin_lists.page_id = tc_plugin_sort.page_id WHERE (page_id='1' AND position='0') ORDER BY tc_plugins.sort_number ASC;
And it throws me: Column 'page_id' in where clause is ambiguous

What could it mean? :S

If you are interested in my new table structure...

Code: Select all

CREATE TABLE tc_plugin_sort (
plugin_id       INT     UNSIGNED NOT NULL DEFAULT '0',
position TINYINT UNSIGNED NOT NULL DEFAULT '0',
page_id         INT     UNSIGNED NOT NULL DEFAULT '0',
sort_number     TINYINT UNSIGNED NOT NULL DEFAULT '0');
^^ One plugin id can have different positions, pages and sort numbers, that's why the new table.

Posted: Tue Jun 19, 2007 10:23 am
by Gente
You have page_id in both tables. Which one should WHERE choose? :wink:

Code: Select all

SELECT * FROM tc_plugin_lists LEFT JOIN tc_plugins ON tc_plugin_lists.plugin_id = tc_plugins.id LEFT JOIN tc_plugin_sort ON tc_plugin_lists.position = tc_plugin_sort.position AND tc_plugin_lists.plugin_id = tc_plugin_sort.plugin_id AND tc_plugin_lists.page_id = tc_plugin_sort.page_id WHERE (tc_plugins.page_id='1' AND position='0') ORDER BY tc_plugins.sort_number ASC;

Posted: Tue Jun 19, 2007 10:30 am
by kaisellgren
Gente wrote:You have page_id in both tables. Which one should WHERE choose? :wink:

Code: Select all

SELECT * FROM tc_plugin_lists LEFT JOIN tc_plugins ON tc_plugin_lists.plugin_id = tc_plugins.id LEFT JOIN tc_plugin_sort ON tc_plugin_lists.position = tc_plugin_sort.position AND tc_plugin_lists.plugin_id = tc_plugin_sort.plugin_id AND tc_plugin_lists.page_id = tc_plugin_sort.page_id WHERE (tc_plugins.page_id='1' AND tc_plugins.position='0') ORDER BY tc_plugins.sort_number ASC;
Oh yes my topo :P

It now almost works. No errors, however, I have too many results...

Code: Select all

SELECT *
FROM tc_plugin_lists
LEFT JOIN tc_plugins ON tc_plugin_lists.plugin_id = tc_plugins.id
LEFT JOIN tc_plugin_sort ON tc_plugin_lists.position = tc_plugin_sort.position
AND tc_plugin_lists.plugin_id = tc_plugin_sort.plugin_id
AND tc_plugin_lists.page_id = tc_plugin_sort.page_id
WHERE (
tc_plugin_lists.page_id = '1'
AND tc_plugin_lists.position = '0'
)
ORDER BY sort_number ASC;
And it gives me 5 results. I should get 3. There are two too many, they are duplicated results...

Here is a screenshot: http://img224.imageshack.us/img224/5281 ... ed1tn8.jpg

It should fetch one "Cool!" with sort_number 1 and one "Cool1" with sort number 3, and also one "This is an example" with sort number 2. For some reason I am getting duplicated results of "Cool!"s? I should get total of two "Cools!" and both different sort number...

Posted: Tue Jun 19, 2007 11:40 am
by Gente
Use

Code: Select all

GROUP BY sort_number
Also I think you can specify required fields in SELECT.
It's easy to confuse looking to your result

Posted: Tue Jun 19, 2007 11:47 am
by kaisellgren
Gente wrote:Use

Code: Select all

GROUP BY sort_number
Also I think you can specify required fields in SELECT.
It's easy to confuse looking to your result
Thanks.

Yes of course ain't gonna select all fields :)

Posted: Tue Jun 19, 2007 12:19 pm
by ReverendDexter
FYI, I think you can clean up that query a bit if you're just using inner joins.

Something along the lines of...

Code: Select all

SELECT t1.f1, t1.f2, t2.f1
FROM t1, t2
WHERE t1.f1 = t2.f1
AND...
just make sure you have an where/and clause for each table beyond the first, else you'll get a cross-product, and just bump your existing WHERE clause down to an AND clause. So long as you stick to inner joins, you can just keep adding tables to the FROM list, and AND clauses to link those tables.

Hope that helps, and was at all relevant!
-Dex