SQL query - HELP!

[Freid001]

Hi, I need help with a SQL query. However i'm not sure if its possible to do what I want in one query. 

Basically have two tables one called items and one called user_items. In Items there is a list of items each with its own id. In user items there is the users id, account name and then a column number that represents the id of an item in items. For example.

 

Items Table:

id | Item        |

1  | Cheese  |

2  | Ham       |

3  | Tomato  |

 

User_items Table:

id | account | i1 | i2 | i3 |

1  | freid001| 0  |  1 | 5  |

 

I basically want to run one query which will return the number of items that a account has. So using the example I want this to show: 

 

items.id | User_items.account | {NUMBER OF ITEM}

1           | fried001                   | 0 

2           | fried001                   | 1

3           | fried001                   | 5 

 

Here is what I have done so far, i don't think it is right

SELECT `Items`.id, `user_items.account`, `Items`.Item, `user_items`. {GET THE COLUMN THAT HAS THE SAME NUMBER ID AS THE ITEM.ID}
FROM  `Items`, `user_items`
WHERE `user_items`.account = 'freid001';

Edited June 19, 2013 by Freid001

[trq]

Nope, it's not at all right. MSSQL doesn't like `backticks` at all.

[Boxerman]

Sorry, to add something else to the questions.

 

When you say "I basically want to run one query which will return the number of items that a account has." did you want to output * or did you want to COUNT ? 

[mikosiko]

You should start by normalizing your tables... the fact that you have "i1, i2, i3" in your user_items table means that it is not correctly designed and will bite you sooner or later... I better design could be:

Items Table:   (it is ok)

id | Item        |

1  | Cheese  |

2  | Ham       |

3  | Tomato  |

 

Users table  ( the items that the user has doesn't belong here... hence table is normalized)

id | user_name (?)        

1  | freid001  

 

Users_Items Table : (this must contain as many rows for a user as items he has)

user_id | item_id| item_number

1           | 2         |  1     (user 1 has 1 of item# 2)

1           | 3         |  5     (user 1 has 5 of item# 3)

 

now just make your select's JOINing the tables depending of your objectives

[dogdaynoon]

could you put all the items in an array and then put that in the user_items table in one column? assuming that the user is selecting items from a list...?

$items = $_POST['checked_items_checkboxes'];

[Freid001]

Sorry, to add something else to the questions.

 

When you say "I basically want to run one query which will return the number of items that a account has." did you want to output * or did you want to COUNT ? 

Yes I want to output. Not count.

[Barand]

I agree with Mikosiko. That is one of the worst table designs I've seen in a long time. SQL tables are NOT spreadsheets.

 

With that design you will need to use UNIONS

mysql> use idiot;
Database changed
mysql> SELECT ui.account, i.id, i.item, i1 as numItems
    ->     FROM items i JOIN user_items ui ON i.id = 1
    -> UNION
    -> SELECT ui.account, i.id, i.item, i2 as numItems
    ->     FROM items i JOIN user_items ui ON i.id = 2
    -> UNION
    -> SELECT ui.account, i.id, i.item, i3 as numItems
    ->     FROM items i JOIN user_items ui ON i.id = 3;
+----------+----+--------+----------+
| account  | id | item   | numItems |
+----------+----+--------+----------+
| freid001 |  1 | Cheese |        0 |
| freid001 |  2 | Ham    |        1 |
| freid001 |  3 | Tomato |        5 |
+----------+----+--------+----------+