|
djhankypark wrote: I hope i am putting this across in an understandable way
Not to me, you now have users, authors, friends, buddies, I'm all confused. Whatever SQL clause I'd write it would be a wild guess as to your intentions. Maybe somebody else understands what you mean, I don't. I would like to see an example, an actual table (set of tables) with people named Jan, Ray, Bob, etc. and the output you want from those.
FWIW: I'm not sure you know exactly what you want either; it seems you know SQL sufficiently well, if you were sure what you wanted, IMO you would have written the WHERE clause yourself already.
|
|
|
|
|
Luc Pattyn wrote: 5.
If you're unfamiliar with it, this may be the time to learn about the IN operator. Google SQL IN, and remember, you can put a secondary SELECT statement inside those parentheses if necessary (don't expect it is, in this case).
Or they could use an INNER JOIN to the table of buddies.
|
|
|
|
|
that may be an elegant approach, I won't be sure until I really understand what he wants...
|
|
|
|
|
Ok, here goes...
This is the original query...
SELECT
(FU.id)AS ID1,
(TU.id)AS ID2,
(FU.username)AS Username1,
(TU.username)AS Username2,
(FU.level)AS level1,(TU.level)AS level2,
(NU.Profile_Image)AS Image1,
(MU.Profile_Image)AS Image2,
comments.comment
FROM comments
JOIN login as FU ON comments.Artist = FU.id
JOIN login as TU ON comments.Buddy = TU.id
JOIN tbl_profile as NU ON comments.Artist=NU.Usr_AD_ID
JOIN tbl_profile as MU ON comments.Buddy=MU.Usr_AD_ID
WHERE TU.active = 1
ORDER BY comments.commentid DESC LIMIT 10
The table structures are as follows including the extra table called tbl_buddylist, and what the output should look like...
| Table: Login | | id | username | active | Level | | 23 | Lacey | 1 | 4 | | 26 | Dave | 1 | 4 | | 232 | Eric | 1 | 4 | | 33 | Jake | 1 | 4 |
| Table: tbl_profile | | id | USER_AD_ID | Biography | Profile_Image | | 1 | 23 | Some Text About Me | prof_img.jpg | | 2 | 26 | Some Text About Me | prof_img.jpg | | 3 | 232 | Some Text About Me | prof_img.jpg | | 4 | 33 | Some Text About Me | prof_img.jpg |
| Table: tbl_buddylist | | id | Buddy_ID | Author_ID | Description | Approved | | 1 | 232 | 23 | Eric | 1 | | 2 | 26 | 23 | Dave | 1 | | 3 | 33 | 23 | Jake | 1 | | 4 | 26 | 232 | Dave | 1 | | 5 | 232 | 33 | Eric | 1 |
| Table: Comments | | commentid | Artist | Buddy | comment | | 1 | 23 | 33 | Hello Lacey from Jake | | 2 | 23 | 26 | Hello Lacey from Dave | | 3 | 232 | 33 | Hello Eric from Jake | | 4 | 26 | 23 | Hello Dave from Lacey | | 5 | 23 | 232 | Hello Lacey from Eric | | 6 | 232 | 26 | Hello Eric from Dave |
| Query Result Required if Lacey is logged in | | ID1 | ID2 | Username1 | Username2 | Level1 | Level2 | Image1 | Image2 | comment | | 23 | 33 | Lacey | Jake | 4 | 4 | prof_img.jpg | prof_img.jpg | Hello Lacey from Jake | | 232 | 33 | Eric | Jake | 4 | 4 | prof_img.jpg | prof_img.jpg | Hello Eric from Jake | | 23 | 26 | Lacey | Dave | 4 | 4 | prof_img.jpg | prof_img.jpg | Hello Lacey from Dave | | 23 | 232 | Lacey | Eric | 4 | 4 | prof_img.jpg | prof_img.jpg | Hello Lacey from Eric | | 232 | 26 | Eric | Dave | 4 | 4 | prof_img.jpg | prof_img.jpg | Hello Eric from Dave |
| Query Result Required if Eric is logged in | | ID1 | ID2 | Username1 | Username2 | Level1 | Level2 | Image1 | Image2 | comment | | 232 | 26 | Eric | Dave | 4 | 4 | prof_img.jpg | prof_img.jpg | Hello Eric from Dave |
I hope please god that is more clear lol
Many thanks
Regards
Ray
|
|
|
|
|
Waw. Nicely presented, and almost perfect.
I must admit I'm still confused about the meaning of the buddy list, it seems somehow either redundant or possibly conflicting with the comments table, that also links authors to buddies. If you only want comments beteen author-buddy pairs that exist in the buddylist table, you shouldn't store artist_ID and buddy_ID in comments, you should instead have a buddylist_ID in there.
I am assuming:
1. that "logged in" and "active" are synonyms
2. that the login table as shown does not fit the query results
2A. that "Query Result Required if Lacey is logged in" really means Lacey and Eric are the only ones "active"
2B. that "Query Result Required if Eric is logged in" really means Eric is the only one "active"
If all the assumptions are correct, I think you'd need to replace
WHERE TU.active = 1
by
WHERE FU.active = 1
Could it be that simple?
FYI: my original codes FU and TU stand for FromUser and ToUser (aliases are most useful when short but still understandable, I had better explained them explicitly from the start).
|
|
|
|
|
Hi there
I think i see where your going with the using of the buddlist_ID in the comments and will need to sit and think on that one to have it clear in my head.
Your questions, 1) The active field is if a user account is active or disabled.
2) There maybe a column missing in the login table structure I forgot, I was trying to only show fields that were used in the query.
2A) No all users are active.
2B) As Above
Your question of "could it be that simmple?" No, lol I wish it could be that simple lol.
The buddylist table is required by the system in other areas of the site so see no need to remove or not use it.
Regards
Ray
|
|
|
|
|
OK, so I'll assume there also is a "loggedin" column in the login table, and try again:
WHERE TU.active=1 AND FU.active=1 AND FU.loggedin=1
The query already has the joins on the relevant tables.
If you insist on having the buddies table (no problem), then you should normalize the comment table, i.e. replace author and buddy fields by a buddytable ID, as I hinted earlier already.
|
|
|
|
|
Hi Luc Pattyn
No there is no logged in field. When a user logs in their id (login.id) because a session variable called kt_login_id
Many Thanks
Regards
Ray
|
|
|
|
|
1.
if N users are known to the system (and active), and any number L of them are logged in, how could any one of them know who else is logged in through session variables?
2.
Your first query example in username1 lists stuff about Lacey and Eric but not Dave and Jake. How is this ever going to work without a loggedin field?
3.
3a. does the query result depend on who performs the query?
3b. Is the first result what gets shown to Lacey asking, and the second to Eric asking?
3c. if so, why is Lacey seeing stuff about Eric, but Eric nothing about Lacey??
I'm afraid your example isn't quite clear yet, and maybe not even correct.
|
|
|
|
|
Hi Luc Pattyn
People do not know when others are logged in, I suppose it would be easier put this way...
A person can only put comments if they are logged in, and they can only comment on profiles of people that are their friends.
If Eric is in Lacey's freinds list then lacey can comment on erics profile
If Lacey is not in Eric's friends list then Eric can not comment in laceys profile
Each user has to ask another to be added to their friends list (Buddies List)
These requests can be accepted(approved)in one direction or both directions i.e. allowing them to be added or allowing them to be added and also adding themselves to the requester.
This is why the buddy list is there to keep track of who is friends with who in both directions.
Regards
Ray
|
|
|
|
|
Sorry, I'm not getting it. I feel my questions 3a, 3b, 3c haven't been answered yet (I added numbering).
|
|
|
|
|
Sorry yes I will answer by number
1) users are not known to be logged by other users
2) see answer 3
3a) Yes, when the page displaying comments is opened the query is ran for the person logged in i.e. session variable (kt_login_id)
3b) Yes correct, the results are in the order you have put forward.
3c) Lacey will see Eric's comment because he is in her buddies list however Eric will not see any of Lacey's comments because she is not in his buddies list, this should also answer the question number 2
Regards
Ray
|
|
|
|
|
I think I got it now. I printed your specs, changed the notation, and came to the following spec for you to accept/reject:
person X wants to see all messages from F to T where X is buddy of author F (where buddyship is one-way and excludes oneself). Therefore Eric wants to see what Dave sent (Dave is his only buddy), and Lacey wants to see all except her own outgoing messages (she is buddy with all three others).
Assuming that is what you want, the code would resemble this:
...
JOIN buddylist AS BUD ON FU.id = BUD.author_ID
WHERE TU.active=1 AND BUD.buddy_ID = @ME
you need to get the ID of the querying person where it says @ME. There basically are two ways:
1. the string approach, just stuff the immediate value in the SQL statement
2. the parameterized approach, use SqlCommand with SqlParameter (could also apply with a stored procedure)
|
|
|
|
|
Hi again Luc Pattyn
From a quick glance at what you have just put there that might actually do it
Your patience has been amazing and I wish to say a big thank to you for that.
I am sorry if I have caused you lots of stress in trying to figure this, I know I could probably explain things better and of cause it would be eaiser to maybe have let you come and look at the site and pages which has only crossed my mind.
Am going to try what you put forward and see what happens
Many many thanks.
Kind regards
Ray
|
|
|
|
|
Hi Ray,
it was a pleasure helping you out.
My database experience is young and limited (I'm more a reader than a writer in this forum), however at the start this looked like a challenge I might just be able to cope with and certainly would learn from. And I tend to like potential win-wins.
See you.
|
|
|
|
|
I'm hoping someone can shed some light.
I need to create a database with Employees, Clients, and Practitioners. An employee could be a client and a practitioner could also be a client. I am hoping to design the database in a way that I only have to maintain name, demographic information, phone numbers, and emails once vs. having to change an address in multiple tables. I thought about having an entity table with the names, email table, phone table, address table, and then a table for each specific information such as a client table, emp table, and practioner table.
Any thoughts on what the tables should look like to accomdate or is it better to have the users maintain the information multiple times.
Any constructive feedback is appreciated.
digicd1
|
|
|
|
|
You could create a generic table that contains people, and then Employee, Client, and Practitioner tables that have a "personID" and then the unique information for each.
I wasn't, now I am, then I won't be anymore.
|
|
|
|
|
digicd1 wrote: I am hoping to design the database in a way that I only have to maintain name, demographic information, phone numbers, and emails once vs. having to change an address in multiple tables.
So, you're absolutely sure that every practitioner uses the same phone-number if they become clients? No practitioner who separates their workphone from their private phone?
digicd1 wrote: I thought about having an entity table with the names, email table, phone table, address table, and then a table for each specific information such as a client table, emp table, and practioner table.
That would lead to a whole lot of joins to get a single record. How about a table for People, one for Clients, one for Practitioners and one for Employees?
digicd1 wrote: Any thoughts on what the tables should look like to accomdate or is it better to have the users maintain the information multiple times.
Normalize the model. This time, there's no viable alternative that I can come up with
I are Troll 
|
|
|
|
|
I agree, that is alot of tables and it would only be the beginning. If a practitioner was a client then I would probably have two addresses. Once for work as a practitioner and one for his home as a client. If an employee was a client, I would have a home/work address for this individual as an employee and a home address as a client. Since the home address repeats itself - I could minimize duplicate records by having an address table vs. having the user be responsible to change the home address in two different locations. Do you think the extra table and joins would be a huge performance impact and would be better to store twice?
|
|
|
|
|
digicd1 wrote: Since the home address repeats itself - I could minimize duplicate records by having an address table
He'd still be a client once, and a practitioner once. You'd need a link to both addresses. The address might be registered and linked to more than once. That'll make complicated queries, and I doubt that you'll save a lot of room.
It's a form of premature optimization, don't do it.
digicd1 wrote: having the user be responsible to change the home address in two different locations
If it's two different entities, then they shouldn't be changed simultanously. Likewise, you could add a boolean to the Employee to let him/her indicate that their address as a client is the same address as their Employee-address. Depending on the boolean, you update either one, or both addresses.
digicd1 wrote: Do you think the extra table and joins would be a huge performance impact and would be better to store twice?
I think that storage-space is cheaper than cpu-time. You should really dive into normalization, three guidelines would help you get a relational model that you can build on. Once you have that, it's easier to spot where you can optimize things.
I are Troll 
|
|
|
|
|
Eddy,
Thank you for your reply. I think this answers my question. In summary it appears that using a database that is too normalized would really affect cpu time. And in an age where disk is cheaper, it would be better to have some information duplicated vs. slowing the entire system down.
Chad
|
|
|
|
|
digicd1 wrote: Thank you for your reply
My pleasure, I hope you don't mind me rambling on about the importance of normalizing too much. It may eat away a bit of your time, but it's well worth the investment.
digicd1 wrote: In summary it appears that using a database that is too normalized would really affect cpu time.
A database can't be "too" normalized. A "good" level of normalization would be BCNF[^]. That should give you a correct model, without (much) redundancy. Keep in mind that most RDBMS-es are optimized to work with relational data, in a relational format.
digicd1 wrote: it would be better to have some information duplicated vs. slowing the entire system down
That sounds like a statement on optimization again 
I are Troll 
|
|
|
|
|
Tables
People
Address
PeopleType
lnkPeopleType - many to many link between people and type (client can also pe a practitioner)
AddressType attribute on address (you may want multiple links if you need to have the same address for 2 types)
This is a heavily normalised structure and is a pain to use so create a view based on the PeopleType.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
In SQL Server, I was doing this in the stored procedure:
param_record_id int OUTPUT
then I :
set param_record_id = scope_identity()
how can id o the same in mySQL stored procedure?
|
|
|
|
|
jrahma wrote: how can id o the same in mySQL stored procedure?
select sequence_name_goes_here.currval
from dual;
There's an example both on using sequences and returning values in the manual over here[^]
I are Troll
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
