SEO

I have 3 tables:

category_posts (no columns to select, but sorting recordset by
sortorder column)
post_titles (select multiple columns)
post_data (select multiple columns)

All three tables contain an entry_id column and there is one record
per table for any given entry id, so there is a row to fetch from each
table for any entry_id that exists.

I have a pre-existing list of entry_id's to pull from the tables, so
basically what I need is:

select some data from post_titles and some other data post_data with a
corresponding entry_id. This is easy to do with an inner join. what
I can't get working is that I need to sort these records by the
sortorder column in the category_posts table. Again, there is a
corresponding entry_id in that table for all the records we are trying
to get.

I tried this but its producing duplicate rows in the wrong order.

SELECT wt.entry_id, wt.title, wt.url_title, wd.field_id_42,
wd.field_id_43, wd.field_id_44, wd.field_id_45, field_id_46,
field_id_47, field_id_55, field_id_56, field_id_57, field_id_64,
field_id_65 FROM exp_category_posts ecp INNER JOIN exp_weblog_titles
wt ON ecp.entry_id = wt.entry_id INNER JOIN exp_weblog_data wd ON
ecp.entry_id = wd.entry_id WHERE wt.status = 'open' and wt.weblog_id =
'8' AND wt.entry_id = '1533' OR wt.entry_id = '1949' OR wt.entry_id =
'1531' OR wt.entry_id = '1392' OR wt.entry_id = '1389' OR wt.entry_id
= '1388' OR wt.entry_id = '1390' OR wt.entry_id = '1391' OR
wt.entry_id = '1185' OR wt.entry_id = '1186' OR wt.entry_id = '1361'
OR wt.entry_id = '1187' OR wt.entry_id = '1188' ORDER BY ecp.sortorder
asc

Thanks

On May 12, 5:43 am, Chuck Cheeze <chadcrow...@gmail.com> wrote:
> I have 3 tables:
> ...
> I tried this but its producing duplicate rows in the wrong order.
>
> ...snip...
>
> Thanks

Are you sure exp_weblog_titles.entry_id and exp_weblog_data.entry_id
are unique?

Can you show some more information such as DESCRIBE the relevant
tables, and paste some results from your query?

On Mon, 12 May 2008 06:43:32 +0200, Chuck Cheeze <chadcrowell@gmail.com>
wrote:

> I have 3 tables:
>
> category_posts (no columns to select, but sorting recordset by
> sortorder column)
> post_titles (select multiple columns)
> post_data (select multiple columns)
>
> All three tables contain an entry_id column and there is one record
> per table for any given entry id, so there is a row to fetch from each
> table for any entry_id that exists.
>
> I have a pre-existing list of entry_id's to pull from the tables, so
> basically what I need is:
>
> select some data from post_titles and some other data post_data with a
> corresponding entry_id. This is easy to do with an inner join. what
> I can't get working is that I need to sort these records by the
> sortorder column in the category_posts table. Again, there is a
> corresponding entry_id in that table for all the records we are trying
> to get.
>
> I tried this but its producing duplicate rows in the wrong order.
>
> SELECT wt.entry_id, wt.title, wt.url_title, wd.field_id_42,
> wd.field_id_43, wd.field_id_44, wd.field_id_45, field_id_46,
> field_id_47, field_id_55, field_id_56, field_id_57, field_id_64,
> field_id_65 FROM exp_category_posts ecp INNER JOIN exp_weblog_titles
> wt ON ecp.entry_id = wt.entry_id INNER JOIN exp_weblog_data wd ON
> ecp.entry_id = wd.entry_id WHERE wt.status = 'open' and wt.weblog_id =
> '8' AND wt.entry_id = '1533' OR wt.entry_id = '1949' OR wt.entry_id =
> '1531' OR wt.entry_id = '1392' OR wt.entry_id = '1389' OR wt.entry_id
> = '1388' OR wt.entry_id = '1390' OR wt.entry_id = '1391' OR
> wt.entry_id = '1185' OR wt.entry_id = '1186' OR wt.entry_id = '1361'
> OR wt.entry_id = '1187' OR wt.entry_id = '1188' ORDER BY ecp.sortorder
> asc

I'd be glad to help, but at least format your query so it's more readable,
and possibly give sample data (create table & insert statements), and the
desired result. Most of us will be glad to help, but please make that easy.
--
Rik Wasmus
[SPAM] Now temporarily looking for some smaller PHP/MySQL projects/work to
fund a self developed bigger project, mail me at rik at rwasmus.nl. [/SPAM]

On May 12, 7:43 am, Chuck Cheeze <chadcrow...@gmail.com> wrote:
> I have 3 tables:
>
> category_posts (no columns to select, but sorting recordset by
> sortorder column)
> post_titles (select multiple columns)
> post_data (select multiple columns)
>
[snip]

Looks like your WHERE statement could either use some brackets or the
statement with the entry_ids be changed into:
AND wt.entry_id in ('1533', '1949', '1531', '1392', '1389', '1388',
'1390', '1391', '1185', '1186', '1361', '1187', '1188')

Best wishes,
John