0

I've constructed a nice query in drupal 7:

SELECT og.label AS title, og.gid AS gid 
FROM og
LEFT JOIN field_data_field_group_category 
  ON og.etid = field_data_field_group_category.entity_id
WHERE ( (field_data_field_group_category.field_group_category_value IN  ('1')) )
ORDER BY title ASC

This works but actually I want to remove the WHERE clause and instead put field_data_field_group_cat.field_group_category_value in my result. But when I try:

SELECT og.label AS title
  , og.gid AS gid 
  field_data_field_group_category.field_group_category_value AS cat 
FROM  og, field_data_field_group_category
LEFT JOIN field_data_field_group_category
  ON og.etid = field_data_field_group_category.entity_id
ORDER BY title ASC

This won't work.. How do I get a value of another table in the result object?

Greetings!

1
  • Two things worth noting with this question. One, nicely formatting queries will often reveal syntax and other errors. PHP support multiline strings, so you don't have to worry about reformatting things. Two, debugging queries inside a database tool (like MySQL Query browser) is much faster than trying to do this inside Drupal.
    – mpdonadio
    Sep 19, 2011 at 15:44

1 Answer 1

2

You second query is incorrect because you have the table field_data_field_group_category two times, so it would generates duplicates and there's a missing coma in your SELECT clause. If you want your column in the result without filtering it, just put it in the SELECT clause and that's it.

SELECT og.label AS title
  , og.gid AS gid
  , field_data_field_group_category.field_group_category_value AS cat 
FROM og
LEFT JOIN field_data_field_group_category 
  ON og.etid = field_data_field_group_category.entity_id
ORDER BY title ASC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.