Problems with query optimization

Question

I had two queries - one that selects group_id by knowing the post_id, and one that checks that user is in that group. I tried to optimize them - put them together, but it now throws an error...

SELECT COUNT(bio_community_group_members.id) AS count
FROM `bio_community_group_members`
JOIN `bio_community_posts`
    ON (`bio_community_posts`.`id` = `180`)
WHERE `bio_community_group_members`.`group_id` = 'bio_community_posts.group_id'
    AND `bio_community_posts`.`user_id` = '34'

It says:

Unknown column '180' in 'on clause'.

Problem: I have that entry!

Table structure:

bio_community_posts:

id,
user_id,
group_id,
other stuff;

bio_community_group_members:

id,
user_id,
group_id,
status,
other stuff;

I need to retrieve status from bio_community_group_members if it exists. That count thing was just because I didn't knew how to start to build my query. :(

Thanks in advice.

Edit:

Hmm... now it works..... but any idea how to optimize those two queries and get one that also selects status?

SELECT `group_id`
FROM `bio_community_posts`
WHERE `id` = 180

SELECT COUNT(id) AS count
FROM `bio_community_group_members`
WHERE `group_id` = 41
  AND `user_id` = '34'

Edit #2:

This is what I was looking for:

SELECT `bio_community_group_members`.`status`
FROM `bio_community_group_members`
JOIN `bio_community_posts` ON `bio_community_posts`.`group_id` = `bio_community_group_members`.`group_id`
WHERE `bio_community_group_members`.`group_id` = 41
  AND `bio_community_group_members`.`user_id` = '34'
  AND `bio_community_posts`.`id` = '180'
GROUP BY `bio_community_group_members`.`status`

Thanks! :)

Edit #3:

I guess that i need something like this...

SELECT `bio_community_group_members`.`status`
FROM `bio_community_group_members`
JOIN `bio_community_posts` ON `bio_community_posts`.`group_id` = `bio_community_group_members.group_id`
WHERE `bio_community_posts`.`id` = '180'
AND `bio_community_posts`.`user_id` = '34'

But:

[Err] 1054 - Unknown column 'bio_community_group_members.group_id' in 'on clause'.

Edit #4:

Just found a bug in the query. Here is the ultimate solution:

SELECT `bio_community_group_members`.`status`
FROM `bio_community_group_members`
JOIN `bio_community_posts` ON `bio_community_posts`.`group_id` = `bio_community_group_members`.`group_id`
WHERE `bio_community_posts`.`id` = '180'
AND `bio_community_posts`.`user_id` = '34'

Remove back ticks. Example: id = 180.

JK.
– JK.

2011-07-21 10:48:24 +00:00
Commented Jul 21, 2011 at 10:48 — JK.
– JK., Commented Jul 21, 2011 at 10:48

Andreas Ågren · Accepted Answer · 2011-07-21 11:53:54Z

4

Remove the back-quotes(?) on 180, I think you meant '180'? Also remove single-quotes around the other column name, like this:

SELECT COUNT(bio_community_group_members.id) AS count
FROM `bio_community_group_members`
JOIN `bio_community_posts`
    ON (`bio_community_posts`.`id` = '180') -- Replaced back- with single-quotes.
WHERE `bio_community_group_members`.`group_id` = `bio_community_posts.group_id` -- Replaced single- with back-quotes.
    AND `bio_community_posts`.`user_id` = '34'

Edit after OP edit:

I'm not exactly sure what kind of optimization you are looking for, but I'm guessing it's something like this?

SELECT COUNT(id) AS count, status
FROM `bio_community_group_members`
JOIN `bio_community_posts` ON `bio_community_posts`.`group_id` = `bio_community_group_members`.`group_id`
WHERE `bio_community_group_members`.`group_id` = 41
  AND `bio_community_group_members`.`user_id` = '34'
  AND `bio_community_posts`.`id` = '180'
GROUP BY `bio_community_group_members`.`status`

Edit after comments: Fixed your edit 3:

SELECT `bio_community_group_members`.`status`
FROM `bio_community_group_members`
JOIN `bio_community_posts` ON `bio_community_posts`.`group_id` = `bio_community_group_members`.`group_id`
WHERE `bio_community_posts`.`id` = '180'
AND `bio_community_posts`.`user_id` = '34'

edited Jul 21, 2011 at 11:53

answered Jul 21, 2011 at 10:48

Andreas Ågren

3,94926 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

daGrevis Over a year ago

Hmm... damn. Not exactly. I need to know user status by knowing only user_id and post_id. Is it possible with only one query?

daGrevis Over a year ago

I need to get access level (called status) for user in that group. Post can be under one group and relationships are group_id. group_members have user_id, group_id and status I need to get. You example returns a lot of statuses. :( I need to get only one for that user! And I know post_id and user_id.

Andreas Ågren Over a year ago

@daGrevis Ok, your last edit only had a syntax error, see my fix.

Maxim Krizhanovsky · Accepted Answer · 2011-07-21 10:48:48Z

1

Remove the ` arround 180

 ON (`bio_community_posts`.`id` = 180)

answered Jul 21, 2011 at 10:48

Maxim Krizhanovsky

26.8k5 gold badges62 silver badges91 bronze badges

Collectives™ on Stack Overflow

Problems with query optimization

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related