Database

2-Feb-06 21:11

Hi,
I want to query two tables:
Select * FROM table1, table2
but I only want one column from table2.
Can I query * from table1 and just Column1 from table2?

thanks,
Ron

Rana Muhammad Javed Khan2-Feb-06 21:15

Rana Muhammad Javed Khan

2-Feb-06 21:15

Select A.*, B.Fld FROM A , B
where A.Fld = B.Fld

myNameIsRon3-Feb-06 5:15

3-Feb-06 5:15

thanks M

Colin Angus Mackay2-Feb-06 22:48

2-Feb-06 22:48

While M Javed Khan's answer is correct, you should be aware that it is generally bad practice to SELECT * in code. I only ever do that while trying things out in the query analyzer.

The reason for that is that SELECT * can break production code because someone changed the database schema and that change is being propogated to the application. The application isn't expecting the different set of columns it is now receiving so it will perform unexpectedly.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

myNameIsRon3-Feb-06 5:16

3-Feb-06 5:16

thanks Colin

Sri Vidhya3-Feb-06 21:58

Sri Vidhya

3-Feb-06 21:58

Give the alias names to your tables in the following way
select t2.columnname ,t1.columnname from table1 t1,table2 t2 where condition follows
Here Column name indicates the name of the column which you want to display from the second table table 2 .This way you can diasplay the columns you want to display .

myNameIsRon4-Feb-06 5:20

This beginner needs some HELP with SQL Registration

4-Feb-06 5:20

Right on, thanks Sri
Ron

alexvw2-Feb-06 12:25

alexvw

2-Feb-06 12:25

Hi,

We have a weird trouble with our SQL 2000 Server. When we turn the machines on (the server and our only work-station{client}, both in the same Domain) we can access SQL from the client using standard Windows authentication, and it works just fine, but after a while it does not work any more.

This is the Error Msg we get: Cannot generate SSPI context. Using the Command Prompt from the client machine, we issue a ping "Server" as well as a ping "IP address" and both work; This makes us think that the DNS is working properly. But then, why is it failing after a few minutes. If we try to connect using the SQL Server Authentication it works just fine.

This is the first time we encounter this problem. We Re-Installed SQL in the Server as well as the client tools on our other machine, but got the same behavior.

After searching Inet for some help we found an article by MS about something called Kerberos, that honestly did not understand at all.

Thanks for your time and help.

A very lost guy(who is a big time ignorant about all this)

-- modified at 18:27 Thursday 2nd February, 2006

Re: This beginner needs some HELP with SQL Registration

Mike Dimmick4-Feb-06 4:57

Mike Dimmick

4-Feb-06 4:57

Presumably that was this article[^]. In a Windows 2000 (or later) Active Directory domain, Kerberos is the protocol used to perform authentication. When you make a connection to SQL Server, the client library uses the Kerberos protocol to get a ticket from the Ticket-Granting-Service on a domain controller. It then presents that ticket to the SQL Server. If the ticket is valid, SQL Server allows the connection.

Kerberos tickets have timestamps on them so they're only valid for a short-ish period and cannot be replayed by an attacker. You should ensure that the clocks on the servers, domain controllers and clients are all synchronised. If you have multiple domain controllers, ensure that they're all in sync with each other - the default configuration for member servers and workstations is to synchronise with a DC. Ideally DCs should get their time from a true external time source, but synchronising with an online time server is fine. We use our ISP's NTP server; if you don't know yours or they don't have one, see the public NTP servers list[^]. On the DC, use NET TIME /SETSNTP to set the NTP servers to use.

Otherwise I suggest you follow the suggestions in that KB article and then call MS support.

Stability. What an interesting concept. -- Chris Maunder

Need help for sql ASAP Phuleez

Minoo S2-Feb-06 10:29

2-Feb-06 10:29

I need to match entries of two tables Survey and Phones . Donno what am I missing on

Tables and example :
Phones
[CaseID], [SurveyID],[DateTime], [TelephoneNumber]
90 13875 2006-01-05 10:31:00.000 2814211345 NULL
90 13875 2006-01-05 10:33:00.000 2814211345 NULL

Survey has following columns
[CaseID], [SurveyID], [CaseStartTime],[Phone]
90 13875 2006-01-05 10:31:00.000 2814211345 NULL
90 13875 2006-01-05 10:33:00.000 2814211345 NULL

There are multiple entries of CaseID in both the tables. When a call is made, there is an entry in Phones table and after that an entry is made in Survey table. Timelag could vary from millisec to 2 or 3 mins.So the way to identify is through timestamp, factoring in mins difference.

I am using this query - does not match correctly though

SELECT t1.CaseID, t1.CaseStartTime,t1.ITS, t2.CaseID,t2.DateTime,
DateDiff("n", t2.DateTime,t1.CaseStartTime)as "Min_Diff"
FROM Survey AS t1,Phones AS t2
WHERE t1.CaseID = t2.CaseID and t1.CaseID=90
and DateDiff("n", t2.DateTime,t1.CaseStartTime) <= 2 and DateDiff("n", t2.DateTime,t1.CaseStartTime) >0
Order by t1.CaseID;

Result
90 2006-01-05 10:33:00.000 47 90 2006-01-05 10:31:00.000 2

Minoo S

Colin Angus Mackay2-Feb-06 10:59

2-Feb-06 10:59

Minoo S wrote:
Donno what am I missing on

Proper relationships? Primary Keys?

I really don't know where to start on this. There is apparently no real relationship between these two tables. In which case, matching them up is going to be sporatic and error prone.

To get the situation right:
A phone call is made. At the start of the call a row is inserted in the Phones table.
At the end of the call a row is inserted in the Survey table.

Minoo S wrote:
Timelag could vary from millisec to 2 or 3 mins.So the way to identify is through timestamp, factoring in mins difference.

No it isn't. Really! That isn't the way to design a database.

Why does the survey table not have a foreign key that matches the primary key of the Phones table? (Or if there is the possibility of a many-to-many join - why is there no intermediate table?)

I'm hoping that there is missing information here and you just need to post it. What is the structure of the two tables? (I want DDL or a description of column names, types and primary/foreign keys)

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Minoo S2-Feb-06 17:31

2-Feb-06 17:31

Thanx for your response Colin.

Scenarios : When a call is placed, it writes to Phone Log. After that logic goest to the Survey software and writes the information in Survey log. After writing in the survey log, the interviewer gets to talk to the respondent.

The information I am pulling out is from Phone log and Survey log. There is more complexity to the case as there could be a row in Phone log that does not exist in Survey log and vice versa. So while matching I need to have left and right join information as well. If I cannot resolve in sql, then may be I should work with some scripting langauge and write a prg to match. Can you think of any prg lang I could do this real quick ??

Minoo S

Colin Angus Mackay2-Feb-06 20:58

2-Feb-06 20:58

Minoo S wrote:
There is more complexity to the case as there could be a row in Phone log that does not exist in Survey log and vice versa.

Okay - but that doesn't seem to excuse not having proper primary / foreign keys. Two times *close* to each other really does not cut it. For a database to join things up properly you need to be precise.

Minoo S wrote:
may be I should work with some scripting langauge and write a prg to match.

That would be slow. Scripting languages are useless for this sort of thing.

Minoo S wrote:
Can you think of any prg lang I could do this real quick ??

SQL - But only if your database is designed right in the first place.

Some questions:

What is the relationship between PhoneLog and SurveyLog? one-to-many, many-to-many, many-to-one, one-to-one

(This is a repeat of something I asked before - I really would like to know because it will help solve your problem) What are the primary keys of each of these tables as it stands now?

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Minoo S3-Feb-06 7:16

3-Feb-06 7:16

Thanx Colin . I will try to answer your questions.

Some questions:
What is the relationship between PhoneLog and SurveyLog? one-to-many, many-to-many, many-to-one, one-to-one

Sorry, I missed response on this one, its one to one relationship. So one entry in Phone can have only one entry in Survey LOG . Just that Phone log might have entries that were not recorded in Survey log and vice versa.

(This is a repeat of something I asked before - I really would like to know because it will help solve your problem) What are the primary keys of each of these tables as it stands now?

Primary Key : I will have to put ID to make the rows unique,I think that might join the rows mentioned in original examples correctly, if I index by unique rows ?? Presently , I parsed the logs and pulled out information and did not set Primary key. I understand it is gross mistake and may be main cause of the issue in first place ! I thot I could make CaseID and timestamps together but then we cannot use it as foreign key as the timestamp will not match in Survey table. Any ideas ?

Minoo S

Minoo S5-Feb-06 8:43

5-Feb-06 8:43

HI ! Any clue from this point ?? Need more info ??

Minoo S

Colin Angus Mackay5-Feb-06 8:47

5-Feb-06 8:47

Minoo S wrote:
Any clue from this point ??

I thought you'd figured it out already:

Previously Minoo S wrote:
Primary Key : I will have to put ID to make the rows unique,I think that might join the rows mentioned in original examples correctly, if I index by unique rows ??

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Minoo S19-Apr-06 6:14