Click here to Skip to main content
14,921,361 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello everyone, i have been thinking long time and can't understand how to make relationships and tables in SQL to do something like this:

i have one table users:
it includes username, password, user_id;

second table quests
it includes quest_id, quest_nr, description of quest


and i need that registered user see all list of quests and behind checkbox where he can check if he is done that, and if other user log in he see the same list of quests but checked in column done quest only hes checks...

How to display one table with all columns, that each user who log in in system see only hes checks of last column ? Please help me, i am new in this situation

I know how to make it for one person, there need one table quests and last column of that where user can check, but how to make same table that displays only login user checks and all quests table to not make table for each user, but use one table for all, i dont understand how to make this sql table and relation select and table structure, mybe someone understand me and my long text of idea what i mean?

What I have tried:

I am using PostgreSQL, created users table and quests table
Posted
Updated 20-Feb-20 10:55am
Comments
Richard Deeming 21-Feb-20 7:53am
   
NB: I hope your password column is not storing the user's password either in plain-text or using a reversible encryption!

Secure Password Authentication Explained Simply[^]
Salted Password Hashing - Doing it Right[^]

1 solution

You need to JOIN the two tables.
Here is a very good explanation: PostgreSQL - JOINS - Tutorialspoint[^]

However, your tables need a column that is used for the join -- a common column (primary key in master and foreign key in the other).

For example you would have a column in your user table named quest_id.

Then you can :
SQL
select * user join quest on user.quest_id = quest.quest_id


Also normally you name your ids just ID in the target table.
For example your User table would have an ID column (not user_id) and your quest table would have a ID column (not quest_id).
Then you join might look like:

SQL
select * from user join quest on user.quest_id = quest.id

This will give all columns

If you only want one column you do the same query but list the only column you want.
SQL
select username, [description of quest] from user join quest on user.quest_id = quest.id
   
v3
Comments
Edgars Kupčs 20-Feb-20 17:32pm
   
Thank You very much for help and good explain, but Your solution is for one row and one quest to be recorded. I need to display all quests 1-100.. and select quest_number, description, boolean check, all together 3 columns and all many 1-100 row of quests... after player log in into website he see all quests from table quests number, description, and checkbox and if he done one of those he can check put as true... but problem is when other player log in into website how to display all same table with many rows but in column with checkbox see only that players quests what he done... i want use same table of quests but each player see his checked list in that table... easy way is to make duplicates of each quests table but its not good idea because players can bee many and why duplicate table, when can use these description table for all but checkbox column each have own... i hope i good explain idea mate.
raddevus 20-Feb-20 17:36pm
   
I think you are saying that you have a 1 to Many relationship from User to Quest.
1 user can have 1 to many quests.
In this case you can create a simple table that contains only two columns:
User_id, Quest_id

Then you simply put your data in there and use it for the join later.
User_id, Quest_id
1 , 1
1 , 3
1 , 4
1 , 5
2, 3
3, 1
3, 2

You see, some users have many quests(userid 1) and others (userid 2) only have 1.
Edgars Kupčs 20-Feb-20 17:43pm
   
I have tryed these, but dont understand how to display all columns of quests not olny which are done, but all done and not done, here i have a problem that when i used join i know how to display those quests which player done, but i need all list with quests and column like in photo where each player can check if he done one of those, and then other player see only in checks hes checked and uncheked list of all quests
Edgars Kupčs 20-Feb-20 17:43pm
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900