[SOLVED] Can't think of how to do query

[pkedpker]

Hello. Ok I'll say here

 

I am trying to make a forum script myself..

I setup my database in a very odd way.. I doubt anyone uses my database format..

 

I have these tables

threads,categories,posts (ignore this one useless for this)

 

threads

--------

id (primary/auto-inc)

cat_id (int) what category each thread is binded too

subject (string) title of thread

startedby (int) user ID of thread maker

updated (timstamp) time last update (date/time)

updatedby (int) last user to make update to thread.

 

categories

----------

id (primary/autoinc)

order (int) orders which one is displayed first/second/third etc.. my style of coding.

name (string) category name.

 

posts

----------

id (primacy/autoinc)

date (timestamp)

thread (int) linked to id from threads table

member (int) linked to each users id.

post (string) the content the post has.

 

 

Now my problem is I will load all the categories.. but I want to have each category display l updatedby/subject (like most forums do)

so when you view category from the outside of the threads you can see who did the last post whats the last topic discussed about.

 

But due to my odd database structure I made I have to make a odd query now.

 

I came up with this (sucks i know)

 

SELECT order, name, subject, updated, updatedby, member FROM categories, threads, posts WHERE threads.cat_id = categories.id GROUP BY categories.id ORDER BY updated

 

also tried this

 

SELECT threads.id, subject, startedby, updated, updatedby FROM threads, categories WHERE threads.cat_id = categories.id GROUP BY categories.id ORDER BY updated

 

I want to combine this query

SELECT * FROM categories ORDER BY `order`

 

with threads by threads.cat_id to and ORDER by updated for earliest  for each category

 

 

but whatever what I want is a way for it to (search each post to find the earliest posts for some threads) don't think thats really needed I believe threads it self gets updated by updated timestamp for earliest  checking. Then from each thread thats linked to a category get the earliest thread to display with member which is only found in posts table.

 

so I'mma have to link 3 tables in 1 query.. kinda strange and i'm clueless

maybe u dont have to use 3 tables maybe im just confused

[pkedpker]

my best attempt (sucks u cannot edit topics on this forum.. so i'd have to reply to my own topic anyways

 

<?php //color

SELECT `members.name` , `categories.order` , `categories.name` , `threads.subject` , `threads.updated` , `threads.updatedby`
FROM `members` , `categories` , `threads`
WHERE `threads.cat_id` = `categories.id`
ORDER BY `threads.updated`, `categories.order`


?>

 

my best attempt and its a very huge packet and not only that... it doesn't work cannot find member.name.. doubt it would do what I wanted it to do anyways..

[pkedpker]

80% solved thanks to google my current query is

 

<?php //COLORS please
SELECT c.order,c.name, t.subject, t.updated, t.updatedby FROM threads AS t LEFT JOIN categories AS c ON t.cat_id = c.id GROUP BY c.order  ORDER BY t.updated, c.order
?>

 

Now i still have 1 problem left and thats it is not printing out the name of threads.updatedby in the rows.

 

 

I can do this but is it possible to do all in 1 query?

<?php 
$updatedby_result = mysql_query("SELECT name FROM members WHERE members.id = $updatedby") or die('Could not display thread count: ' . mysql_error());
?>

[Ken2k7]

This may be wrong because I don't get what you're trying to do 100%. I'm just assuming based off your query.

 

SELECT order, name,
     (SELECT t.subject, t.updated, t.updatedby FROM threads t WHERE c.id = t.cat_id ORDER BY t.updated DESC LIMIT 1)
FROM categories c;

 

I think that should do what you want right?

[pkedpker]

sorry to say that doesn't even work in my query browser..

 

what I want is to combine 3 tables..

process goes like this

 

threads.. a bunch of em in threads table each have cat_id and subject and updated (timestamp for last update) updatedby (id of last member to update it).

categories simple just a bunch of categories (order is which category comes first second etc..)

members table lots of stuff but important is the id which matches threads updatedby in order to access members.name

 

now I want to get all categories by order from 0,1,2 to X (last order) so

then when i get all categories i dont want to show all threads linking to the categories just the newest thread by updated (time stamp).

 

like i said im almost done with this

 

SELECT c.name, t.subject, t.updated, t.updatedby FROM threads AS t LEFT JOIN categories AS c ON t.cat_id = c.id GROUP BY c.order ORDER BY t.updated

 

I tried your way thanks for the trick of using t and c next to FROM..

 

I got this now

 

SELECT c.order,c.name, t.subject, t.updated, t.updatedby FROM threads t, categories c WHERE t.cat_id = c.id GROUP BY c.order ORDER BY t.updated, c.order

 

which still got the same problem of t.updated not working.

 

 

it's only missing the members.name to get from t.updatedby

 

also the ORDER BY seems to be broken I did a few.. updated changes and it picks first one.. doesn't order by updated (timestamp)

 

[Ken2k7]

Wow I am stupid. Had a brain fart there. Sorry about that.

 

SELECT c.name, t.subject, t.updated, t.updatedby, m.name
FROM threads t
LEFT OUTER JOIN categories c
     ON t.cat_id = c.id
LEFT OUTER JOIN members m
     ON m.name = t.updatedby
GROUP BY c.order
ORDER BY t.updated;

 

[pkedpker]

Wow I am stupid. Had a brain fart there. Sorry about that.

 

SELECT c.name, t.subject, t.updated, t.updatedby, m.name
FROM threads t
LEFT OUTER JOIN categories c
     ON t.cat_id = c.id
LEFT OUTER JOIN members m
     ON m.name = t.updatedby
GROUP BY c.order
ORDER BY t.updated;

 

stupid lol smartest man i know atm lol

 

almost lol but is that optmized enough why use left outer join anyways I almost did it without any joins...

 

 

btw u made a little mistake with m.name has to be m.id

 

fixed verison :

 

SELECT c.name, t.subject, t.updated, t.updatedby, m.name
FROM threads t
LEFT OUTER JOIN categories c
     ON t.cat_id = c.id
LEFT OUTER JOIN members m
     ON m.id = t.updatedby
GROUP BY c.order
ORDER BY t.updated;

 

still doesn't work by t.updated.. maybe i should try ASC/DESC to the ORDER BY.. see what happens..

 

also I had this going without any LEFT JOINS or anything.. it almost worked..  has a compile error i cannot find.. maybe after FROM only 2 limit max? or what

 

SELECT c.order,c.name, t.subject, t.updated, m.name FROM threads t, categories c, members m WHERE t.cat_id = c.id, m.id = t.updatedby GROUP BY c.order  ORDER BY t.updated, c.order

 

[Ken2k7]

SELECT c.name, t.subject, t.updated, t.updatedby, m.name
FROM threads t
LEFT OUTER JOIN categories c
     ON t.cat_id = c.id
LEFT OUTER JOIN members m
     ON m.id = t.updatedby
GROUP BY c.order
ORDER BY t.updated DESC;

 

What results do those return? I would like to see the kind of data it returns and what you expect.

 

Cartesian joins are slower. If you care for performance, you shouldn't use it. As for the syntax error, you should change the comma in your WHERE clause to AND.

[pkedpker]

i'm glad i found the best man for my solution.. I bet others would of just fixed up my cartesian join.. now I know to always use LEFT OUTER JOIN's for something like this..

 

as for results i get back

 

'Help Support', 'category2 firsttopic', 2009-06-18 00:00:00, 1, 'admin'

'General Section', 'category 1 test', 2009-06-18 00:00:00, 1, 'admin'

 

 

but look at the threads database

id, catid,subject,startedby,updated,updatedby

1, 1, 'category 1 test', 1, 2009-06-18 00:00:00, 1

2, 1, 'category 1 secondtopic', 2, 2009-06-18 00:01:00, 2

3, 2, 'category2 firsttopic', 1, 2009-06-18 00:00:00, 1

, , '', , ,

 

as you can see secondtopic is 1 hour better.. and should be used for cat_id instead of category test

 

 

 

You sure yours is faster I guess you mean in the log run using only 4 threads using my way with the Cartesian I get up to

 

2 rows fetched in 0.00084s (0.0007s)  highest being  0.00090s (0.0010s)

 

yours is

 

2 rows fetched in 0.0092s(0.0009s) highest being 0.0101s (0.0008)

 

i'm confused what second one means but in the long run i think u know what your talking about and i'll use it!

[pkedpker]

Also is it possible to combine this query all into 1 query? or is it bad to do it as 1 query?

 

SELECT COUNT(*) FROM threads WHERE cat_id = c.id  'IS IT POSSIBLE? or must use another query for this?

 

to count the threads made for a certain category

 

 

meh screw it.

[Ken2k7]

Well it's not so much as it being slower because cartesian joins are CROSS JOINs in case you didn't know that. It does something more than what you want.

 

It's hard to do SQL testing mainly because MySQL is very smart and if you run a SQL query once and run a similar one again, it will pretty much return the results instantaneously or at least much faster.

 

Not tested.

SELECT c.name, t.subject, t.updated, t.updatedby, m.name
FROM categories c
LEFT OUTER JOIN threads t
     ON t.cat_id = c.id
LEFT OUTER JOIN members m
     ON m.id = t.updatedby
WHERE t.id = (
     SELECT u.id
     FROM threads u
     WHERE u.cat_id = c.id
     ORDER BY updated DESC
     LIMIT 1
);

[pkedpker]

Well it's not so much as it being slower because cartesian joins are CROSS JOINs in case you didn't know that. It does something more than what you want.

 

It's hard to do SQL testing mainly because MySQL is very smart and if you run a SQL query once and run a similar one again, it will pretty much return the results instantaneously or at least much faster.

 

Not tested.

SELECT c.name, t.subject, t.updated, t.updatedby, m.name
FROM categories c
LEFT OUTER JOIN threads t
     ON t.cat_id = c.id
LEFT OUTER JOIN members m
     ON m.id = t.updatedby
WHERE t.id = (
     SELECT u.id
     FROM threads u
     WHERE u.cat_id = c.id
     ORDER BY updated DESC
     LIMIT 1
);

 

yes that worked i wonder why your previous one didn't and also is it possible to add to that query like

 

SELECT count(id) AS c FROM threads WHERE cat_id= c.id

 

I mean i wouldnt' mind running another query but the way its setup.. it generates the html tables while its looping the results.. and if i do it inside.. well i can still do it as long as i don't use the results create a new variable whatever its not needed..

 

still i dont understand why your previous one didnt work..

[Ken2k7]

It didn't work because GROUP BY sucks. But yeah, this won't work with GROUP BY or at least I don't know of a way. I apologize for that before. Don't know what I was thinking.

 

Try this -

SELECT c.name, t.subject, t.updated, t.updatedby, m.name, (
     SELECT COUNT(e.*) AS count
     FROM threads e
     WHERE e.cat_id = c.id
)
FROM categories c
LEFT OUTER JOIN threads t
     ON t.cat_id = c.id
LEFT OUTER JOIN members m
     ON m.id = t.updatedby
WHERE t.id = (
     SELECT u.id
     FROM threads u
     WHERE u.cat_id = c.id
     ORDER BY updated DESC
     LIMIT 1
);

[pkedpker]

It didn't work because GROUP BY sucks. But yeah, this won't work with GROUP BY or at least I don't know of a way. I apologize for that before. Don't know what I was thinking.

 

Try this -

SELECT c.name, t.subject, t.updated, t.updatedby, m.name, (
     SELECT COUNT(e.*) AS count
     FROM threads e
     WHERE e.cat_id = c.id
)
FROM categories c
LEFT OUTER JOIN threads t
     ON t.cat_id = c.id
LEFT OUTER JOIN members m
     ON m.id = t.updatedby
WHERE t.id = (
     SELECT u.id
     FROM threads u
     WHERE u.cat_id = c.id
     ORDER BY updated DESC
     LIMIT 1
);

 

hah dont have to apologize for anything man

 

and that latest one is bugged

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) AS count
     FROM threads e
     WHERE e.cat_id = c.id
)
FROM categories' at line 2

 

 

looks soo complex i can't even follow it.. well yah i can its a bunch of nested select's but still its confusing u dont know if a c.id will work since its like outside the nest its real confusing for me i dont care if it is even.. as long as it works

[Ken2k7]

SELECT c.name, t.subject, t.updated, t.updatedby, m.name, (
     SELECT COUNT(*) AS cnt
     FROM threads e
     WHERE e.cat_id = c.id
)
FROM categories c
LEFT OUTER JOIN threads t
     ON t.cat_id = c.id
LEFT OUTER JOIN members m
     ON m.id = t.updatedby
WHERE t.id = (
     SELECT u.id
     FROM threads u
     WHERE u.cat_id = c.id
     ORDER BY updated DESC
     LIMIT 1
);

?

[pkedpker]

Attempting backtrace. You can use the following information to find out

where mysqld died. If you see no messages after this, something went

terribly wrong...

006AE78B    mysqld.exe!ha_innobase::open()[ha_innodb.cc:2601]
00443C36    mysqld.exe!handler::ha_open()[handler.cc:2047]
005BFEAB    mysqld.exe!open_table_from_share()[table.cc:1882]
005264E7    mysqld.exe!open_unireg_entry()[sql_base.cc:3910]
00529B5D    mysqld.exe!open_table()[sql_base.cc:2907]
0052AA32    mysqld.exe!open_tables()[sql_base.cc:4570]
0052B07B    mysqld.exe!open_normal_and_derived_tables()[sql_base.cc:5026]
005918C6    mysqld.exe!fill_schema_show_cols_or_idxs()[sql_show.cc:2929]
0059D751    mysqld.exe!get_all_tables()[sql_show.cc:3221]
0057DE8C    mysqld.exe!greedy_search()[sql_select.cc:4895]
7C910F04    ntdll.dll!wcsncpy()
005A0173    mysqld.exe!get_schema_tables_result()[sql_show.cc:6053]
0058F49A    mysqld.exe!JOIN::exec()[sql_select.cc:1730]
005907D3    mysqld.exe!mysql_select()[sql_select.cc:2380]
00590C0B    mysqld.exe!handle_select()[sql_select.cc:268]
00554034    mysqld.exe!execute_sqlcom_select()[sql_parse.cc:5009]
005551A0    mysqld.exe!mysql_execute_command()[sql_parse.cc:2211]
006C0070    mysqld.exe!innobase_start_or_create_for_mysql()[srv0start.c:1706]
7C91403C    ntdll.dll!RtlDetermineDosPathNameType_U()
006A51C9    mysqld.exe!get_share()[ha_federated.cc:1487]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 03B90200=SHOW COLUMNS FROM `categories`

 

 

possibly your mysql crafted query crashes mysql.. which probably should be reported to MySQL Bugs.. dunno.

 

Nevermind fixed the bug.. the column categories didn't save well or something when I shutdown my computer or whatever happened... Re-built the categories column no more mysql crashes..

 

 

your query works excellent!

 

only thing left to do is to get a list of posts thats linked to the threads that are linked to the category lol i dont think it could be done in that mega query I got atm.. but i'll try something out

[pkedpker]

to count posts i got this..  (which works) but I got a cartesian join in it

 

 

SELECT COUNT(*) AS cnt FROM threads e, posts p WHERE e.cat_id = c.id AND p.thread = e.id

 

 

now to join it to the big boy

 

SELECT c.name, t.subject, t.updated, t.updatedby, m.name, (
     SELECT COUNT(*) AS a
     FROM threads e
     WHERE e.cat_id = c.id
), (
     SELECT COUNT(*) AS b
     FROM posts p
     WHERE e.cat_id = c.id AND p.thread = e.id
)
FROM categories c
LEFT OUTER JOIN threads t
     ON t.cat_id = c.id
LEFT OUTER JOIN members m
     ON m.id = t.updatedby
WHERE t.id = (
     SELECT u.id
     FROM threads u
     WHERE u.cat_id = c.id
     ORDER BY updated DESC
     LIMIT 1
);

 

from what I learned from you.. I have to do it like that.. but it doesn't work SQL syntax error.. but I tried hehe it seems cool

[Ken2k7]

SELECT c.name, t.subject, t.updated, t.updatedby, m.name, (
     SELECT COUNT(*)
     FROM threads e
     WHERE e.cat_id = c.id
) thread_count, (
     SELECT COUNT(*)
     FROM posts p
     WHERE p.thread = t.id
) post_count
FROM categories c
LEFT OUTER JOIN threads t
     ON t.cat_id = c.id
LEFT OUTER JOIN members m
     ON m.id = t.updatedby
WHERE t.id = (
     SELECT u.id
     FROM threads u
     WHERE u.cat_id = c.id
     ORDER BY updated DESC
     LIMIT 1
);

[pkedpker]

Haha yup thanks.. sorry for pushing you.. really I feel bad..

 

...even though your last reply is kinda wrong it set me in the correct direction.

 

 

I had to combine this to get the post_count

 

SELECT COUNT(*) AS cnt FROM threads e, posts p WHERE e.cat_id = 1 AND p.thread = e.id

 

returns

cnt=36

 

catagory 1

Topic 1 has 31 posts

Topic 2 has 4 posts

Topic 3 has 1 post

category 2

1 topic and 3 posts..

 

cnt=3  when e.cat_id = 2

yup 36 is correctly

 

just doing WHERE e.cat_id =1 or 2  gave me more posts then I have in all of my forum combined.. (strange..) but adding that AND p.thread = e.id gives me the correct amount of posts per category.

 

IDK this is how I fixed it.. but its probably bad for preformance.

 

SELECT c.name, t.subject, t.updated, t.updatedby, m.name, (
     SELECT COUNT(*)
     FROM threads e
     WHERE e.cat_id = c.id
) thread_count, (
     SELECT COUNT(*)
     FROM posts p, threads h
     WHERE p.thread = h.id AND h.cat_id = c.id
) post_count
FROM categories c
LEFT OUTER JOIN threads t
     ON t.cat_id = c.id
LEFT OUTER JOIN members m
     ON m.id = t.updatedby
WHERE t.id = (
     SELECT u.id
     FROM threads u
     WHERE u.cat_id = c.id
     ORDER BY updated DESC
     LIMIT 1
);

 

thanks for everything..

[Ken2k7]

Why is it wrong? It makes sense to me.

[pkedpker]

Don't ask me I am no SQL Expert I debug using the returned rows and I keep trying different patterns of logical AND's until I get the result I want.. and your query has everything correct except the post_count which returns 1 for each row..

 

I think the fact that your using a join from the outside of the box which doesn't have the correct threads hell I may be just talking bullshit here but I don't know SQL just saying mines works completely except for the point that I use a cartesian join that you told me not to use so I am kinda down about that but the same time all good that it works.

 

Mines is always going at 0.0090s (0.0014s)

Your broken one for post_count is at 0.0072s (0.0013s)

 

but no meh whatever its good enough.

[Ken2k7]

SELECT c.name, t.subject, t.updated, t.updatedby, m.name, (
     SELECT COUNT(*)
     FROM threads e
     WHERE e.cat_id = c.id
) thread_count, (
     SELECT COUNT(*)
     FROM posts p
     LEFT OUTER JOIN threads h
          ON h.id = p.thread
     WHERE h.cat_id = c.id
) post_count
FROM categories c
LEFT OUTER JOIN threads t
     ON t.cat_id = c.id
LEFT OUTER JOIN members m
     ON m.id = t.updatedby
WHERE t.id = (
     SELECT u.id
     FROM threads u
     WHERE u.cat_id = c.id
     ORDER BY updated DESC
     LIMIT 1
);

[pkedpker]

Alright that works completely perfect i'll stick to that.. so finally I guess nothing more to say i'll set topic to solved