Jump to content

Archived

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

Freid001

SQL query - HELP!

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';

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 ? 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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'];

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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