0

hello friends m working on a project and making a notification system .

for which i need to join few tables . so that i can get the data based on timestamp of the notification table .

notification table

notification table

in this the type is for the type of notification . so i want to order it by timestamp and

type wall post table is

type wall post table is

type wall comment table is

type wall comment table is

and finally the user table to get the name of the id in all other table is

user table

user table

really not able to join these . can anyone help please.....

Sakshi Sharma
  • 1,414
  • 4
  • 20
  • 39
  • Could you provide an example of the data you are trying to.retrieve? I would think you would join on the foreign keys and order by notification time stamp. – scott.korin Jan 07 '12 at 19:25
  • i am making a notification system such as facebook has . i have made type_id such as type of notification . [ wall comments, wall post ,etc] plz reffer to my other post http://stackoverflow.com/questions/8768609/how-to-retrieve-notification-from-database-with-diffrent-types-of-notification thanks a lot for taking out time to help – Sakshi Sharma Jan 07 '12 at 19:39

1 Answers1

1

I see the user_id is common attribute in all the tables so you can join the tables on this attribute.

Then you can select table corresponding to a timestamp

Select * FROM notification_table INNER JOIN comment_table ON 
notification_table.user_d = comment_table.uid_fk INNER JOIN wallpost_table
ON comment_table.uid_fk == wallpost_table.uid_fk INNER JOIN user_table ON 
wallpost_table.uid == user_table.uid
Shraddha
  • 2,337
  • 1
  • 16
  • 14
  • i used ur query edited the table names and the tables are now joined but its repeating results . it repeats every result 4 times can u chk what is wrong SELECT * FROM notification INNER JOIN comments ON notification.user_id = comments.uid_fk INNER JOIN messages ON comments.uid_fk = messages.uid_fk INNER JOIN users_profile ON messages.uid_fk = users_profile.uid AND notification.user_id = '3' ORDER BY notification.time_stamp DESC LIMIT 0 , 30 – Sakshi Sharma Jan 07 '12 at 19:35
  • Why did you use "AND notification.user_id = '3'"? Use where instead, if you you want to get the record corresponding to user_id = 3 – Shraddha Jan 07 '12 at 19:47
  • thanks for helping shraddha i used where also still the result is same i m getting 4 records of each individual result – Sakshi Sharma Jan 07 '12 at 19:53
  • Try using distinct with distinct to avoid repetition.SELECT distinct * FROM notification – Shraddha Jan 07 '12 at 20:20
  • still the results are same dnt knw what is wrong , i think some problem in query – Sakshi Sharma Jan 08 '12 at 06:17