[MySQL] Help Optimize queries to select data from 2 tables

[mtha]

I want to do the following two thing:


1- Select all posts (with its information) belong to one thread
something like:

PHP Code:

$threadid == X;

$posts = $DB_site->query("
                    SELECT post.*, post.userid FROM post WHERE (post.threadid = $threadid)
");

$post = $DB_site->fetch_array($posts) 


giving me a list of posts, says 15 posts (15 rows)


2. For each posts (each row), there is one userid, I need to select all awards that belong to that userid
something like

PHP Code:

$alluserawards =  $DB_site->query("
                    SELECT award.* FROM award WHERE userid=$post[userid]
"); 


giving me a list of awards, says 5 awards (5 rows)

if I run query #2 for each of the rows, it will run 15 times, + 1 query for #1,

is there any way to optimize the queries? so that I can somehow get a list of awards for each userid.

[Dean C]

Untested:

[sql]
SELECT post.*
awards.*
FROM " . TABLE_PREFIX . "post as post
LEFT JOIN
post
ON
post.userid = award.userid
[/sql]

You didn't give any info on your db schema for the awards table so I'm afraid I can't be of much help other than the query above which may or may not work depending what you have in the awards table

[mtha]

Originally Posted by Dean C

Untested:

[sql]
SELECT post.*
awards.*
FROM " . TABLE_PREFIX . "post as post
LEFT JOIN
post
ON
post.userid = award.userid
[/sql]

You didn't give any info on your db schema for the awards table so I'm afraid I can't be of much help other than the query above which may or may not work depending what you have in the awards table

yeah, my mind was just out the query is kind of simple. Thanks Dean C
The db schema is available in my new hack
https://vborg.vbsupport.ru/showthread.php?t=78934

PHP Code:

            $userawards =  $DB_site->query("
                SELECT a.*, au.*, post.userid, post.postid 
             FROM " . TABLE_PREFIX . "post, " . TABLE_PREFIX . "award_user au, " . TABLE_PREFIX . "award a 
             WHERE $postids AND au.userid=post.userid AND a.award_id=au.award_id
                GROUP BY au.issue_id
            "); 


[Marco van Herwaarden]

You really should use the LEFT JOIN like Dean showed you.

Also you will need to add an AS to the FROM clauses:
[sql]" . TABLE_PREFIX . "post AS post[/sql]
Or things will go wrong if a table prefix is used.

[Xenon]

Dean's left join won't work, as it would just select ONE award per post.

you have two possibilities:
1) put both tables into the from tag
2) cache the results of query two with one big query, and then work with the cache

[mtha]

Originally Posted by MarcoH64

You really should use the LEFT JOIN like Dean showed you.

Also you will need to add an AS to the FROM clauses:
[sql]" . TABLE_PREFIX . "post AS post[/sql]
Or things will go wrong if a table prefix is used.

Got it work perfectly here
https://vborg.vbsupport.ru/showthread.php?t=78934

Thank you very much

[mtha]

Originally Posted by Dean C

Untested:

[sql]
SELECT post.*
awards.*
FROM " . TABLE_PREFIX . "post as post
LEFT JOIN
post
ON
post.userid = award.userid
[/sql]

Could you tell me the different between ON and USING on JOIN command?

says

LEFT JOIN post ON (userid)
and
LEFT JOIN post USING (userid)

[Xenon]

using just allows a column-name, ON instead allows more complex conditions like for example:

[sql]SELECT * FROM post LEFT JOIN user ON (user.userid = post.userid * 2)[/sql]
or whatever (not that the statement above would be usefull )