Jump to content

SQL query - HELP!


Freid001

Recommended Posts

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';
Link to comment
Share on other sites

  • 1 month later...

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

Link to comment
Share on other sites

  • 2 weeks later...
  • 5 months later...

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.

Link to comment
Share on other sites

  • 3 weeks later...

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 |
+----------+----+--------+----------+

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.