-1

I am making a food donating application. I have three tables in the database namely:

user_info
donated_info
accepted_info

The attributes of 'user_info' table are:

user_id, name, username, password, address, phone, pincode

The attributes of 'donated_info' table are:

user_id, Food_id, Food_item, No_of_serving, Expiry_date, Expiry_time, Image, status

The attributes of 'accepted_info' table are:

Food_id, user_id

I want to create a module in which the donater gets to view the status of his donations. [By default, the status attribute is set to 'to_accept'. Whenever someone accepts it, the value is changed to 'accepted'. I want to retrieve four columns: name, phone, Food_item, Image. If the Food_item is accepted, the name and phone number of the person who has accepted it will be displayed. Else those two columns will be empty. How can I write a query for this? Any kind of helps will be greatly appreciated.

Shadow
  • 33,525
  • 10
  • 51
  • 64
Sharon Shelton
  • 1
  • Where did you get stuck with writing your query? – Shadow May 19 '21 at 15:08
  • I wrote a query which just returns the details of the person who is donating it. But I want the details of the user who has accepted if there is any. Can you help me @Shadow – Sharon Shelton May 19 '21 at 15:26

1 Answers1

1

You use a simple Join

SELECT name, phone, Food_item, Image
FROM accepted_info a JOIN donated_info d ON u.Food_id= d.Food_id
    JOIN user_info u ON a.user_id = u.user_id
WHERE d.user_id = 1

Also try

SELECT name, phone, Food_item, Image
FROM donated_info d  LEFT JOIN accepted_info a   ON u.Food_id= d.Food_id 
    JOIN user_info u ON a.user_id = u.user_id
WHERE d.user_id = 1
nbk
  • 45,398
  • 8
  • 30
  • 47
  • Actually I want the name and phone number of the person who is accepting it(which is stored in accepted_info) – Sharon Shelton May 19 '21 at 15:24
  • i changed my answer – nbk May 19 '21 at 15:30
  • I'm sorry, it still displays the donater's information @nbk – Sharon Shelton May 19 '21 at 15:56
  • i don't see wherr the id from the user is that you want in your table the hole design makes not that much sense, but you only have one user_id field per table, all seem to link to the same user, so change my query a.user_id = u.user_id a.user_id to the correct column – nbk May 19 '21 at 16:16
  • Thank you, it was kinda helpful. I just changed the where clause condition to d.user_id because I want the data for a particular donator's food items. Problem solved :) But I can't display the unaccepted columns tho. Maybe I can use a separate query for that. – Sharon Shelton May 19 '21 at 16:38
  • I added a version where all donations are display independent of the of the acceptance. but you see how it is done. – nbk May 19 '21 at 16:46
  • But it returns an empty set when none of the user's donation are accepted. Which means unaccepted food details are not returned – Sharon Shelton May 19 '21 at 17:28
  • no it shows all donation whether or not they are acepted please make a sample on https://dbfiddle.uk and show me whart you have as data, this go nowhere. withou it – nbk May 19 '21 at 17:35
  • From user_id = 11, I have made two donations that yet to be accepted. I ran the query and it doesn't return anything – Sharon Shelton May 19 '21 at 17:41
  • make a fiddle **please** – nbk May 19 '21 at 17:45
  • I used an union instead:) ```SELECT name, phone, Food_item, Image FROM accepted_info a JOIN donated_info d ON a.Food_id= d.Food_id JOIN user_info u ON a.user_id = u.user_id WHERE d.user_id = 11 UNION ALL SELECT null, null, Food_item, Image FROM donated_info WHERE user_id = 11 AND status='to_accept'``` Thanks @nbk – Sharon Shelton May 20 '21 at 09:20
  • thta doesn't seem right, are you sure you don't want to a dbfiddle? – nbk May 20 '21 at 09:25
  • Pardon me. What is meant by 'dbfiddle'? Btw, the query returned what I actually wanted – Sharon Shelton May 20 '21 at 09:54
  • as i wrote before post your tables and data here https://dbfiddle.uk/ – nbk May 20 '21 at 10:08