|
Now how is the SQL code shown as the solution different from the one shown in your last post above?
I'm referring to:
select id, max(dateColumn) from tabelname group by id
|
|
|
|
|
Because I was trying to do it with a single SELECT statement, which is apparently impossible. You can't have non-aggregated columns in a group by select statement (and if I'm wrong about that, I hope someone can rebut that conclusion).
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
I have two tables with a relationship set on a given column (the related column names are the same in each table, and both tables have that column set as the primary key).
Do I still have to do a join to get data from both tables, or does the configured relationship magically enable it?
Here's what I have so far (I actually list the individual columns I want to retrieve, but for the sake of brevity, I'm using "SELECT *". Also, The ID column is the primary key and the column on which the tables are related.
SELECT *
FROM TableA a, TableB b
WHERE a.ID = b.ID
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
i want to tell you that is the relationship ypu set is not relationship but
it is a constraint but the where statment "WHERE a.ID = b.ID" make the relationship or join statment
|
|
|
|
|
Yes you still have to do a join if you want data from both tables. A Foreign Key constraint is there to only allow non-null values that are in the parent column. The FK plays no part in the query; If you were to remove it, the query would still work just as well.
By the way, your chosen style of joins is not considered to be good practice and may well be deprecated in future versions of Sql Server.
SELECT *
FROM TableA a
JOIN TableB b
ON b.ID = a.ID
The primary reason for this is that if you forget/remove the WHERE clause you have a CROSS JOIN. This can't happen with the suggested approach.
Ian
|
|
|
|
|
Is there anything out there to encrypt my SQL 2008 server tables etc. so that only via passwords I can view the contents of it.
|
|
|
|
|
I'm not understanding something. Isn't the connection to the database userid/password authenticated?
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Sometimes people don't want the admin to be able to see the data.
Being an admin I find that disturbing.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Jörgen Andersson wrote: Sometimes people don't want the admin to be able to see the data.
That's going to make the admin's job a little tough.
Jörgen Andersson wrote: Being an admin I find that disturbing.
I concur.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Yes[^]
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Here, I need your help. Please your comments, suggestions, expectation or potential interview Question (along with answer) here. Your input will be very valuable. As time goes by we all learn and get better. There were few things missing at that time when those interview questions and answers were prepared, now is the time to complete the gap and make this interview questions more useful.
http://blog.sqlauthority.com/2010/03/19/sql-server-interview-questions-answers-needs-your-help/
|
|
|
|
|
Why are people uni-voting this? Did they bother following the link to his site? He has spent some considerable time on what should be a valuable community resource and all he wants is some help to make it better.
|
|
|
|
|
Hi Russell,
You have raised excellent point. I have worked very hard but just like everybody I can not make it better without the help of community and smart people.
I invite all the community members to help me build this interview Q and A, this way it is more useful to all of us in future. This will be on of the reference material for people who are new to industry and will know what to expect when they start their career.
Many thanks for your valuable point.
Kind Regards,
Pinal
|
|
|
|
|
Because it looks like an ad.
And if you follow the link it's about how to learn answers to common questions at interviews as opposed to learning a trade.
Having a site that gives you quick and simple answers to common database questions you might have, is brilliant, and I find it a good and wellwritten page as such.
But I find the purpose wrong.
And no, I haven't univoted him.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
And if you read the text you will find that these misgivings have been discussed with collegues and various solutions suggested.
I don't think it is as one dimensional as you have implied.
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
So im designing a pm system for a game written in Ruby/On Rails and not quite sure how i would design the database side of the game to facilitate the desired features. The general message system looks like
Table: users
int userid
~ extra
Table: messages
int messageid
int authorid
longtext message
datetime timestamp
Table: message_to
int key
int messageid
int recieverid
What I'm looking to add is essentially custom folders. Meaning a user could create a "Diplomatic" folder and have the message appear under that folder. How would one go about facilitating the association between a message and a folder?
|
|
|
|
|
You need to create a table for folders i.e.
Table: folders
int folderid
text name
and then in meesages table add
int folderid.
Then when you access a message you know which folder it belongs to.
|
|
|
|
|
Yes but that doesn't hand ownership of folders would one do
Table: Folders
int folderid
varchar foldername
int userid
the do something like
SELECT foldername FROM table WHERE userid="x";?
then look into messages with
SELECT message, folderid FROM table WHERE receiverid="x";
then output based on folder?
|
|
|
|
|
Sorry, didn't get the part about folder ownership. This how I would set it up
Tables:
folders:
id int
name varchar
userid int
messages:
id int
fromid int
toid int
message varchar(250)
messagedate datetime
folderid int
users:
id int
name varchar
Then when you want to look up all messages from one person (say his id is 1) to another person (id = 2) in a particular folder (say id = 1) you would have a query like this
SELECT
m.message
FROM
messages m
INNER JOIN
folders f
ON
m.folderid = f.id
WHERE
m.fromid = 1
AND m.toid = 2
AND f.id = 1;
|
|
|
|
|
thanks alot, actually the first table gets inserted, but the rest do not.
thanks.
|
|
|
|
|
Hi,
i have this stored prcedure to update multiple tables in sqlserver DB,
when i run it , i get an saying tbpassport,tbaccounts, tbpermanentaddress and tbcontactaddress cannot allow nulls.
this is the code, where am i getting it wrong.
CREATE PROCEDURE InsertEmployeeRecord
@emp_id char(5), @surname char(25), @fisrtname char(25), @middlename char(25),
@sex char(10), @date_of_birth char(12), @ministry_code char(3), @dept_code char(3), @nationality char(15),
@state_code char(3), @lga_code char(3), @designation char(15), @date_of_employment char(12),
@unit_code char(3), @employement_type char(15), @marital_status char(10), @religion char(10), @glevel char(10), @step char(8),
@picture image,@filename char(52), @bank_name char(52), @bank_code char(3), @sortcode char(9), @branch char(100), @account_no char(40),
@pcity char(45), @ptown char(45), @pstreet char(100), @pphone char(200),
@ccity char(45), @ctown char(45), @cstreet char(100), @cphone char(200)
AS
BEGIN
SET NOCOUNT ON
INSERT tbemployee (emp_id,surname,firstname,middlename,
sex,date_of_birth,ministry_code,dept_code,nationality,
state_code,lga_code,designation,date_of_employment,
unit_code,employment_type,marital_status,religion,glevel,step)
VALUES(@emp_id, @surname, @fisrtname, @middlename, @sex, @date_of_birth, @ministry_code, @dept_code, @nationality,
@state_code, @lga_code, @designation, @date_of_employment, @unit_code, @employement_type, @marital_status,
@religion, @glevel, @step)
INSERT INTO tbpassport (emp_id,filename,picture)
VALUES(@emp_id, @filename, @picture)
INSERT INTO tbaccounts (emp_id,bank_code,bank_name,sortcode,account_no,branch)
VALUES (@emp_id, @bank_code, @bank_name, @sortcode, @account_no, @branch)
INSERT INTO tbpermanentaddress (emp_id, city, town, street, phone)
VALUES (@emp_id, @pcity, @ptown, @pstreet, @pphone)
INSERT INTO tbcontactaddress (emp_id,city,town,street, phone)
VALUES (@emp_id,@ccity, @ctown, @cstreet, @cphone)
END
GO
thanks.
|
|
|
|
|
It seems as though your tables have been set up not to accept null values. You either need to edit the table to accept nulls (probably not the best approach), or check whether your input values are not null before running the stored procedure(my preferrred approach) .
|
|
|
|
|
you kind of lost the thread there with your second post, however..
Either change the tables to allow nulls,
Or ensure no null values are passed to the procedure,
Or best of all, use isnull checks for each insert.
e.g.
INSERT INTO tbcontactaddress (emp_id,city,town,street, phone)
VALUES
(
isnull(@emp_id, '')
,isnull(@ccity, '')
,isnull(@ctown, '')
,isnull(@cstreet, '')
,isnull(@cphone, '')
)
Another consideration is that rather than passing so many parameters you may be better off passing it all wrapped up in a single XML parameter.
|
|
|
|
|
select spid, status, loginame, hostname, blocked, db_name(dbid), cmd from master..sysprocesses
Above query lists all hosts currently logged in using which login name.
But i want which hostname have logged in past using which login name . where do i found this information ?
Is there any log is maintained for that.
|
|
|
|
|
Member 6392036 wrote: i want which hostname have logged in past
AFAIK, the information isn't stored historically. If you want to know who was online when, then you'll need to add some kind of logging.I are Troll 
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
