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';
Edited by 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...
  • 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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.