Question

First issue:

a) I am trying to create a SQL query so i can list all the items that a user has bought

b)I am trying to show how much it will cost in total for user

My tables:

Table user(user_id, username, shipping_address)

Table item(item_id, item, pricing)

Table receipt(r_id, user_id, date_current)

Table rDetailing(r_id, item_id, item_in, amount_of_item)

Answer 1

a) considering that your user_id attribute is of varchar type. The following is the sql query for

list all the items that a user has bought

Query:

select item_id from receipt, rdetailing where rdetailing.r_id=receipt.r_id and user_id='&user_id';

while running this query one sholuld input the user_id and query will show the list of item_id that particular user has bought.

b) how much it will cost in total for user

select sum(amount_of_item) from rdetailing where item_id in(select item_id from receipt, rdetailing where rdetailing.r_id=receipt.r_id and user_id='&user_id');

in the above query we are considering amount_of_item attribute in the rdetailing table as the pricing amount for that r_id; (Though it is not very clear from the attribute name). Otherwise the query will be different let me know in that case through coments.

or it can be:

select sum(pricing) from item where item_id in(select item_id from receipt, rdetailing where rdetailing.r_id=receipt.r_id and user_id='&user_id');

However the query will depend on what the attributes item_in and amount_of_item attributes signify in rdetailing table. If it is different from what I have assumed let me know through comments. Then the query may need modification

thank you.