|
Hi,
I have three tables with two relations.
First table is users, which just stores userids (autonumber), and a lastseen (text).
Second table is Names. Every user has names, right? So it's just rows of userid(long)/name(text).
Third table is IPs. Every user has a few ips, right? So again, it's just userid/ip.
The relations are just the ones I mentioned, a user has names and ips.
I have a name and an ip. I want to select a union from the IPs and Names.
The union needs to have only users that have the ip given in their ips, or - here's a tricky part - have an ip with first two octets the same as the given ip but also the name must appear in the user's names.
The idea is that given a name and ip, I want to select all previously entered users who are probably the same user.
But, after finding all these users, I also want to merge everything that I found to just one user. So all the ips I found should be updated to relate to the first user found, and the same for the names.
Any ideas?
I tried this: (given name is MasterShin and given ip is 204.15.0.0)
SELECT Players.userid, Names.name as [value], 1 as [type] FROM (Players RIGHT OUTER JOIN [Names] ON Players.userid = Names.userid) LEFT OUTER JOIN IPs ON Players.userid = IPs.userid
WHERE IPs.ip = '204.15.0.0' OR (IPs.ip LIKE '204.15.*' AND Names.name = 'MasterShin')
UNION
SELECT Players.userid, IPs.ip as [value], 2 as [type] FROM (Players RIGHT OUTER JOIN IPs ON Players.userid = IPs.userid) LEFT OUTER JOIN Names ON Players.userid = Names.userid
WHERE IPs.ip = '204.15.0.0' OR (IPs.ip LIKE '204.15.*' AND Names.name = 'MasterShin')
And after the select I can go over everything, save on the side what updates need to be done, and later do them.
But this big statement seems repetitive and not efficient...
|
|
|
|
|
It is a little late for me, so I did not read your hole problem. Maybey tomorrow. But, youe have one table with userids and a table with usernames. Is it possible that a userid has more names? If not, why not combine those two tables to one?
Wout Louwers
|
|
|
|
|
Yes. Users can have more than one name.
|
|
|
|
|
I stil think it is strange that one user can have more names, and I still do not understand the exact problem. But maybe you can try this:
select u.UserID, <br />
[value] = case when i.ip = '204.15.0.0' then n.Name else i.ip end,<br />
[type] = case when i.ip = '204.15.0.0' then 1 else 2 end<br />
from Players u<br />
left join IPs i on u.userID = i.UserID<br />
right join n Names on u.UserID = n.UserID <br />
Where i.ip = '204.15.0.0' or (i.ip like '204.15.%' and n.Name = 'masterSin')
Wout Louwers
|
|
|
|
|
Hello everyone,
I would like to create a table where I keep the record of time period which refrence to an event. The time variable is in the format of Hour, Minute, Second (Three Double Digits). How can I created a table to stroe these information and still be able to run a search quary?
I don't have a problem converting the time into only one number which would be second but I rather to keep the table in the same format of column for each of the hour, minute, and second.
Thank you very much for your help.
|
|
|
|
|
1-What is the database that you use ? MS SQL, Access, MySQL?
2-How will your searches look like?
3-Merging the 3 columns look a good idea to me and they will be easy to separate in UI or even in SQL.
|
|
|
|
|
1.Create column with datetime datetype and name it MyDateTimeColumn
2. use getdate() to fill it
it will be like 28-02-2007 12:34:567
3.Use if it is helpful CONVERT(varchar(11),Mytable.MyDateTimeColumn,105)
This 105 is pamaretar and can be in range 100-114?
or from some two gidits number I think from 10 to ...try u will see
Sorry if this doesnt fit
|
|
|
|
|
Hello group,
I am trying this command but i dont know its not working..
its not giving any error...but data is not getting inserted..i have tried to insert breakpoint..but program doesnt stop here..
about the databse structure..I am using SQL Server express edition
Site_ID is auto incremental in nature
Code is :
SqlDataSource siteDataSource = new SqlDataSource();
siteDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ProjectConnectionString"].ToString();
siteDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
siteDataSource.InsertCommand = "INSERT INTO site(Site_Id,Site,Address,City,State,Zip,Country,Date,Institution_Id,Project_id) VALUES (@Site, @Address, @City, @State, @Zip, @Country, @Date, @Institution_Id, @Project_id)";
siteDataSource.InsertParameters.Add("Site_Id", siteidTextBox.ToString());
siteDataSource.InsertParameters.Add("Site", sitenameTextBox.Text);
siteDataSource.InsertParameters.Add("Address", addressTextBox.Text);
siteDataSource.InsertParameters.Add("City", cityTextBox.Text);
siteDataSource.InsertParameters.Add("State", stateTextBox.Text);
siteDataSource.InsertParameters.Add("Zip", zipTextBox.Text);
siteDataSource.InsertParameters.Add("Country", countryTextBox.Text);
siteDataSource.InsertParameters.Add("Date", dateTextBox.ToString());
siteDataSource.InsertParameters.Add("Institution_Id", irbDropDownList.SelectedValue.ToString());
siteDataSource.InsertParameters.Add("Project_id", projectTextBox.ToString());
Kindly Please Tell me where I could Make changes....
Govind
|
|
|
|
|
|
Dear All,
i want to find no of empty rack(two dimensional )
i have a rack of two dimensional where every slot is recognized by rowno and columnno now in every slot i placed item (captured by itemcode). nOw i want to find slot do not assing any item
please give me some idea
there is rackmst( where i define max_no_row and max_no_cols).
Please help
thanks
regards
imran khan
|
|
|
|
|
Your question is not clear
Can you post the exact database structure and sample data ?
|
|
|
|
|
Hello
i've seen so many times N' in queries or SPs.
but i m still not able to understand this thing (N').
pls anybody can tell me
whats this?
why should we use it?
whats advantages of this thing (N')?
----------------------
Thanks,
Ankur Bakliwal
|
|
|
|
|
It's used to convert a string literal to wide character.
for example. If you have a column with nvarchar and you want to compare it with a string (where mycol= 'abc') you should make it like: (where mycol= N'abc')
you'll not notice that the first will not give the expected results unless you use a language other than English (Arabic strings for example).
|
|
|
|
|
hey thanks for this information.......
Thanks,
Ankur Bakliwal
|
|
|
|
|
hello all,
am using sqlserver 2000 as my database. my problem is am the DB administrator. i totally granting Permission to 10 user. i want to restrict 3 user can only access the DB at a time. All the others trying to enter, then it will shows that already 3 user's login u should try after some time...
Is It Possible? Please help me to solve this Problem.
|
|
|
|
|
Yes.
SQL server licensing can be set from control pannel.
by which max number of simultanious connections can be controlled.
Regards
KP
|
|
|
|
|
hello Mr.Krish
Can u tell me the details... how to do it? in control panel where i have to restrict? Please tell me more about such things.
|
|
|
|
|
Start -> Settings -> Control Panel -> SQL Server 2000 Licensing setup
need to set "per seat for" form the "Choose Licensing Mode" dialog box
Regards
KP
|
|
|
|
|
I couldn't find any sql server 2000 licensing setup in Control panel. what can i do now? else where it is actually resides?
|
|
|
|
|
I was tried your suggestion. in that case i am getting some error. i also want to know how to do it without any errors.error is come from my software
only but i dont know how to solve it.
Am going to controlpanel->administrative tools->computer management->services and application->microsoft sqlserver->localwindowsNT then its property am changing the connections to zero to five. whenever am doing this the computer management console not responding and am also not able to start my database services. it was shown some error message and the error number is -1073741724. what it is? how to rectify it? Please help me....
|
|
|
|
|
Pls set per seat
for any errors pls give full error message displayed. with would help in solving the same.
Regards
KP
|
|
|
|
|
Please tell me the full details how to set per page connections?
|
|
|
|
|
hi
i have sql server 2000 personal in server, and i want connect to it from client by vc#2005, but the following error shown me :
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
how to solve my problem ??
thanks
|
|
|
|
|
Your query is not clear.
is that you are trying to access from an application
if so, which one.
or from Query analyzer directly use the sql statements.
of from enterprise manager is a graphical interface.
Regards
KP
|
|
|
|
|
from Query analyzer.
task description is:
I have central sql2000(general office) db and several(5) sql2000 db as 5 point of sales.
GeneralOffice need fresh data about realizaton in stores as they need fresh data about new articles and price modification etc.
Connecton is by dial-up(modem connection).
I have one solution: create an xml file wich is transfer over network, but it is slowly and complicated for employees in stores.
My second idea is creating linked server but i have no experience in subject.
Any idea about this task is very helpful.
Deadline is monday!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
