CODEWITHSUNDEEP
mysqlsql
shabeer90
shabeer90

Reputation: 5151

Using 'AND' in SQL WHERE clause for the same filed

Here is the scenario. I have 1 table that has all the contact details. Another table that has all the list of Categories. And a 3rd table which is an associate table, that has the ID of the first table and the ID of the second table.

This is how my associate table looks like

contactdid -2 | categoryid -1

contactdid -2 | categoryid -2

contactdid -2 | categoryid -3

contactdid -3 | categoryid -1

contactdid -3 | categoryid -3

This is my SQL code below(Generated using SQLyog and i included the where clause). 

SELECT
    press_contacts.email
FROM
    contacts_category
    INNER JOIN press_category 
        ON (contacts_category.categoryid = press_category.id)
    INNER JOIN press_contacts 
        ON (contacts_category.contactdid = press_contacts.id)
WHERE contacts_category.categoryid = 1 AND contacts_category.categoryid = 2 ;

I get the output when I do not have AND contacts_category.categoryid = 2inserted in the code.

Any idea how to solve this.I clearly have data. Thanks in advance for the help.

Upvotes: 1

Views: 1435

Answers (3)

user359040
user359040

Reputation:

If you only want to see those email addresses with contacts in both categories, try:

SELECT press_contacts.email
FROM contacts_category
    INNER JOIN press_contacts 
        ON (contacts_category.contactdid = press_contacts.id)
WHERE contacts_category.categoryid in (1, 2)
GROUP BY press_contacts.email
HAVING COUNT(DISTINCT contacts_category.categoryid)=2

Upvotes: 2

user1027167
user1027167

Reputation: 4448

contacts_category.categoryid can not be 1 and 2 at the same time, perhabs you mean OR instead of AND?

Upvotes: 5

Iridio
Iridio

Reputation: 9271

Use OR or IN() instead of AND. A field can't have two values at the same time

Upvotes: 3

Related Questions