1

I am working with Expression Engine and the query module which allows you to use MySQL to get results. I have a set of data which I'm trying to associate with a user. My query is currently as follows:

SELECT COUNT(*) 
FROM exp_channel_grid_field_11 
INNER JOIN exp_member_data 
WHERE `col_id_12` = 'Race' && `member_id` = '1'

So, I'm not too clued up when it comes to joins, but I am just looking for the count. Thanks.

Dr Casper Black
  • 7,350
  • 1
  • 26
  • 33
devon93
  • 165
  • 2
  • 11
  • 1
    In your query is the on clause missing. Can you show the table structure, so we can see how the tables must be joined? – Jens Aug 04 '14 at 06:03
  • Are you sure you need a join at all? probably you just meant to do like `SELECT COUNT(*) FROM exp_channel_grid_field_11 WHERE col_id_12 = 'Race' and member_id = '1'` – Rahul Aug 04 '14 at 06:18
  • @Jens: in MySQL, the `on` clause is optional. A `join` without `on` is what other databases call a `cross join`, or full cartesian product. – Andomar Aug 04 '14 at 06:27

2 Answers2

1

Not sure what you're after - you don't necessarily need an 'ON' to do a JOIN but perhaps you do need to define the tables. I don't know which columns belong to which tables (and neither does mysql, perhaps that's the problem)
Assuming that 'member_id' is in exp_member_data and 'col_id_12' is in exp_channel_grid_field_11, you probably need to do something like this: 

SELECT COUNT(*)
 FROM exp_channel_grid_field_11
 INNER JOIN exp_member_data
 WHERE `exp_channel_grid_field_11.col_id_12` = 'Race'
 && `exp_member_data.member_id` = '1'

and you can "pretty it up" with "table aliases" such as like this: 

SELECT COUNT(*)
 FROM exp_channel_grid_field_11 e11
 INNER JOIN exp_member_data ed
 WHERE `e11.col_id_12` = 'Race'
 AND `ed.member_id` = '1'

Or, maybe there should be an 'ON' member_id? 

SELECT COUNT(*)
 FROM exp_channel_grid_field_11 e11
 INNER JOIN exp_member_data ed  
 ON e11.member_id = ed.member_id  
 WHERE `e11.col_id_12` = 'Race'
 AND `ed.member_id` = '1'
Community
  • 1
  • 1
Joe T
  • 2,300
  • 1
  • 19
  • 31
  • That's not true. You do need the `ON` clause to specify that on which column JOIN must happen unless you are doing a `natural join` – Rahul Aug 04 '14 at 06:15
  • This is better answer i think, shows a good way to write a proper syntax. – Mad Dog Tannen Aug 04 '14 at 07:01
  • The query you wrote does match up the correct column to the correct table but i get an error saying Unknown column 'exp_channel_grid_field_11.col_id_12' in 'where clause'. Thanks for your help with this. – devon93 Aug 04 '14 at 12:32
0

In stead of WHERE col_id_12 = 'Race', use: on col_id_12 = 'Race'

 SELECT COUNT(*) 
 FROM exp_channel_grid_field_11 
 INNER JOIN exp_member_data ON `col_id_12` = 'Race' 
 WHERE `member_id` = '1'
Dr Casper Black
  • 7,350
  • 1
  • 26
  • 33
  • Thanks for your answer. I know member id 1 has 5 races, but when i change the id to other members it still says 5 and not that members amount, so unfortunately i don't think its getting the members_id – devon93 Aug 04 '14 at 12:26