Further Help with Select Required Please [modified]
-
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... :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
-
that may be an elegant approach, I won't be sure until I really understand what he wants... :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
:) 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 10The 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
-
:) 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 10The 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
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). :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
-
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). :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
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
-
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. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
-
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. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
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
-
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
:confused: :confused: :confused: 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. :confused: :confused: :confused:
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
modified on Tuesday, November 23, 2010 7:35 PM
-
:confused: :confused: :confused: 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. :confused: :confused: :confused:
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
modified on Tuesday, November 23, 2010 7:35 PM
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
-
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). :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
-
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
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 = @MEyou 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) :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
-
Sorry, I'm not getting it. I feel my questions 3a, 3b, 3c haven't been answered yet (I added numbering). :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
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 = @MEyou 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) :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
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 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. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.