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
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... =/
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.
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.
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`
Sorry to trouble yet again , 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.
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...
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.
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;
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
It now almost works. No errors, however, I have too many results...
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...
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...
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.