|
KORCARI wrote: without creating problems with ID colums
What are you using for a datatype? If UniqueIdentifier, you should be OK; if auto-increment integers, you're hosed.
|
|
|
|
|
It's a terrible task.
In your small Access dbs, turn off the autoincrement feature of the ID columns. Then look for the max ID values in the first db, update the IDs in the second by adding that value, then take the new max value, ....
In SQL Server, use "SET IDENTITY_INSERT " + table + " ON".
Now move your Access data to SQL Server, turn IDENTITY_INSERT off again, and throw away the small Access dbs.
|
|
|
|
|
KORCARI wrote: Is this possible to merge data from table1 table2 table3 in a SQL database without creating problems with ID colums of each table
Yes. Add a column called "sourceDatabaseName", make it part of the primary key, and when importing write the name of the database that you're importing into that column.
If you're moving a lot of data, then this would be the time to consider upgrading to Sql Server (express) 
I are Troll 
|
|
|
|
|
Hello
I want to connect my Vb.net application to SQL Azure database which is in cloud, what connection string i should use.
Thanks
Bhaskar
Senior Programmer
B R. Solutions, Delhi
www.brsolutions.info[^]
|
|
|
|
|
Connectionstring.com [^]is an excellent resource for this information
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
how can I convert a varchar like this "254565825145" to bigint in mysql stored procedure?
|
|
|
|
|
Try:
select cast('254565825145' as unsigned)
|
|
|
|
|
What if it's in hex? 
At a previous job of mine, the company stored 64-bit IDs in strings as hex (in RDB*) and there was no native way of converting them back and forth within the database, you had to fetch the data out and then work on it (in C). Eventually I wrote functions (like CLR extended stored procedures in SQL server) to do it.
* Which still doesn't support 64-bit integers?
|
|
|
|
|
The code below which was advised works very well, not sure if it was supposed to be laid out that way and was a little learning curve me but it works  am happy. My next problem is this...
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
I have a tbale which is basically a buddies list...
TABLE Structure...
ID
Buddy_ID
Author_ID
Description
Block
Approved
I am not sure how to include this so the comments only show comments from a person that is in a user buddies list, if that makes sense. at the moment it shows all comments from and to everyone. As members have to be logged in to see the page I could grab the users login_id from the session variable which would go against the Author_ID in the buddies list table, so only to show comments form people in his/her buddies list.
Any pointers would be very much appreciated.
King Regards
Ray
-- Modified Tuesday, November 23, 2010 2:00 PM
|
|
|
|
|
Hi again,
1.
no you don't have to format in any particular way, I did it there and then as it made things more clear.
2.
please use preview and modify your post till it looks all right, the PRE tags didn't work for you, probably because you had one of the checkboxes/radiobuttons wrong below the edit box. You can still edit and improve that message!
3.
in general, if you want fewer results, all it takes is a more limiting WHERE clause.
4.
I am not sure I understand your exact requirement, an example would have helped.
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).
6.
FWIW: I like http://www.w3schools.com/sql/ a lot as a primary source on SQL stuff. Sorry, the damn linkifier is on strike again.
|
|
|
|
|
Hi Luc Pattyn
Wow! quick reply
Seems you are to my rescue again. Many thanks for your support I am really enjoying the learning curve and achiveing so much just for having been pointed in the right directions.
Have a great evening, thank you again
Regards
Ray
|
|
|
|
|
You're welcome.
|
|
|
|
|
Hi again Luc Pattyn
When you say an example would help?
I basically want to include another table in the query so as to break the list to fewer comments being shown your are correct.
As a user is logged they have buddies "friends" and that is represented by their login_id in the buddies table, so the account holders login_id is held in the Author_id field of the buddies table. If the user befriends someone else that has an account that persons login_id is held in the buddy_id field of the buddies table.
Everytime someone adds a buddy "friend" a new record is added to the table again holding the Author_id and Buddy_id.
When a comment is made it puts the Authors id and the buddies id in the comments table, and of cause the comment that has been made.
So the query you helped me with previously now has to only show comments to an Author where th person is in their own Buddies list. a bit like myspace really where you only see comments that have been made by your friends and not every man and his dog.
I hope i am putting this across in an understandable way lol
Kind Regards
Ray
|
|
|
|
|
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
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
