Where a column equals this or this?

SELECT * FROM table WHERE column1 = 0 OR column1 = 20

-- or

SELECT * FROM table WHERE column1 IN (0, 20)

If your table holds records for both cases than they should be returned. Why do you think you only get the record where column1 is 20 ?

Thanks pritaeas. The IN clause almost works perfectly. I just can't get this query right. I'm on the verge of pulling my hair out.

My table structure must be wrong but I don't see how it could be.

My tables are as follows:

Table teams

*------------*
| id | team  |
|------------|
|  1 | team1 |
|  2 | team2 |
*------------*

Table product_types

*--------------------*
| id | product_type  |
|--------------------|
|  1 | bangle        |
|  2 | necklace      |
|  3 | ring          |
|  4 | men's rings   |
|  5 | women's rings |
*--------------------*

Table teams_types (products associated with each team)

*--------------------------*
| id | product_type | team |
|--------------------------|
|  1 | 1            | 0    |
|  2 | 2            | 0    |
|  3 | 3            | 0    |
|  4 | 4            | 0    |
|  5 | 5            | 0    |
|  6 | 2            | 1    |
|  7 | 3            | 1    |
|  8 | 4            | 1    |
|  9 | 2            | 2    |
| 10 | 4            | 2    |
*--------------------------*

What I want to do is when I view the page that allows me to edit a team (team1 for instance) I want to list all the product types and then check the checkboxes of the product types that are currently associated with the team (eg team1).

I'm grouping by product_type.id and using an IN clause as you suggested which works almost perfectly, but what's happening is when I go to edit team1 (I've associated every product_type available with team1) it ends up showing some product_type's where teams_types.team is equal to 0 when it should be 1.

What do I need to do to make this work...I don't know why this is turning into such a nightmare...I've been trying to get this right for the last two days! Must I restructure my tables? Am I going about this in a completely wrong way???

Perhaps your query is correct, but the id entered from your code is not. What I don't understand is why the teams_types has records with team id 0 in it. Your teams table only shows id's 1 and 2.

Perhaps your query is correct, but the id entered from your code is not. What I don't understand is why the teams_types has records with team id 0 in it. Your teams table only shows id's 1 and 2.

The 0's are there because I changed my code so that when I created a product_type in the table product_types, it would also insert it into the table teams_types with teams_types.team as 0.

I was hoping this change might solve my issue. Ideally I don't want any product_type's inside the table teams_types with a team of 0. It just seems messy.

All I want to do is display all the product_type's and mark the checkboxes of the ones that are associated with the team I'm editing. At the moment, if all the product_type's are associated with team1 then when I edit team2 it only shows the product_type's associated with team2 and not the other product_type's. So for instance I can't associate "rings" with team2 because the MySQL query only returns "necklace" and "men's rings"

P.S. I know my id is correct because I echo it out on the page and the product_type's that are associated with the team are the correct ones.

I think you need a query that selects everything from product_type, and adds an extra column to indicate whether that option has been selected (for your team). It would be something like this:

SELECT pt.*,IF(tt.team, 1, 0) AS selectedoption FROM product_type pt
LEFT JOIN teams_types tt ON pt.id = tt.product_type
WHERE tt.team = yourrequestedteamid

This will return all types and the column selectedoption will indicate if it's on (1) or off (0).

Well, if I understand you correctly, then you want to select all product types together with the product types associate with a relevant team within a single query. This can be done by set operations union and difference or by subselects.

I'll explain it step by step. First, let me allow to change the naming of your columns a little to have a more handy scenario:

Given three tables teams(teamid, team), products(productid, product_type) and a linking table team_product (teamid, productid), where the latter defines the many-to-many relationship between teams and products. Columns with id-suffix in their names denote the primary key.

1st step: Select all product types of the relevant team, say for teamid = 2, from linking table team_product

select teamid, productid from team_product where teamid = 2;
/* result
teamid productid
----------------
2      2
2      4*/

2nd step: Select all product types from table products

select productid from products;
/* result
productid
---------
1
2
3
4
5*/

3rd step: Combining both result sets by union set operation

To adjust the number of columns a formal -1 is added as teamid to the 2nd select to get two columns. This also allow to differentiate between product types already associate to a team and other product types the team not have chosen so far.

Modified select statement from 2nd step:

select -1 as teamid, productid from products;
/* result
teamid productid
----------------
-1     1
-1     2
-1     3
-1     4
-1     5*/

Now both queries can be formally combined:

select teamid, productid from team_product where teamid = 2
UNION
select -1 as teamid, productid from products;
/*
teamid productid
----------------
 2     2
 2     4     
-1     1
-1     2    <--- duplicate productid already associate with team 2
-1     3
-1     4    <--- duplicate productid already associate with team 2
-1     5*/

The result set of this union selects still contains duplicates (for productid 2 and 4). These duplicates are now removed in

4th step: Removing duplicate product types for the relevant team

select teamid, productid from team_product where teamid = 2
union
select -1 as teamid, productid from products
  where productid not in (select productid from team_product where teamid = 2) order by productid;

Finally the result for the relevant 2nd team is

*--------------------*
| teamid | productid |
|--------------------|
|  -1    |         1 |
|   2    |         2 |
|  -1    |         3 |
|   2    |         4 |
|  -1    |         5 |
*--------------------*

-1 in column teamid indicates that team 2 hasn't associate these product types so far. Therefore all Checkboxs where teamid > -1 should be checked.

I hope above result could allay your nightmare a little.

Note: The correct primary key of team_product is (teamid, productid), auto-incremented ID is useless. As for your question "...restructure my tables?" the primary keys and foreign keys should be correctly defined. ID of table "id | product_type | team" should be dropped. You don't need to add the rows with team=0 to that table.

tesu