Help with MySQL conditional queries...

[RJP1]

Hi guys,

 

I'm a bit stuck here. I've got it mostly working but can't complete my next task. Basically, I have a table where each user has 3 fields and each field has a value.

 

eg

user_id | field_id | value
1           |      1     |   UK
1           |      2     |   Yes
1           |      3     |   1
2           |      1     |   USA
2           |      2     |   Yes
2           |      3     |   4

 

I'm trying to count users who match a few conditions. In order to count as 1, the user must be from the country specified in my loop and have a field_id 2 = Yes and field_id = specified by the loop again.

 

This is what I have so far - it counts just by country which is promising but I don't know how to make a conditional mysql statement where if all 3 fields (country, yes, ID) = the specified reference OR country and yes are true.

 

It must prefer the 3 statements being true, then if the 2 are true... (in other words, if either statement is true, count = 1). Here is what I have so far:

 

$sql = "SELECT COUNT(*) FROM `fields_values` AS tab1, `fields_values` AS tab2, `values` AS tab3, `fields_values` AS tab4
WHERE tab1.user_id = tab2.user_id
AND tab2.user_id = tab3.user_id
AND tab3.user_id = tab4.user_id
AND (tab1.field_id = 11 AND tab1.value = '$item->website')
AND (tab2.field_id = 16 AND tab2.value= 'Yes')
AND (tab3.field_id = 17 AND tab3.value= '$item->id')
AND (tab4.field_id = 18 AND tab4.value>='$date_first' AND tab4.value<='$date_last')";

 

Thanks for the help!

 

RJP1

[hamza]

start learning joins INNER , OUTER < LEFT < RIGHT EQUI and few others

[RJP1]

Thanks for the point in the right direction hamza, really appreciated. I'll have a look!

 

Cheers, RJP1

[RJP1]

I had a look at joins and it seems it's for multiple tables... the issue I have here is a single table but with a condition.

 

Can anyone suggest a method of solving my problem?

 

Thanks, RJP1

[fenway]

I don't follow -- you want to ensure that all 3 conditions are met?

[RJP1]

Hi Fenway,

 

At the moment all 3 conditions must be met in order to be counted as +1. I'd like it to count +1 if all 3 are met, or if 2 conditions are met, count still = 1.

 

Is that doable?

 

Cheers, RJP1

[fenway]

Everything is "doable".

 

But what's the difference between 1 and +1?

[RJP1]

Sorry, no difference. If either is true, the count should be "1"... hard to explain, sorry!

 

RJP1

[fenway]

So either 2 or 3 results in "true"?  A specific 2, or any 2?