|
My company has a project database that has numerous columns. We track everything between tables with the project number, but because of how screwed up in the past the database got there is another column which is actually the primary keys called OrderStatusID. So if I want to navigate forward through the records I use the following SQL statement:
SELECT TOP 1 * FROM [Order Status] WHERE OrderStatusID > " & CurrentRecordID & " ORDER BY OrderStatusID"
Now there is another table that holds certificates for the projects. A project may have zero to many certificates. So I was trying to use the SQL statement:
SELECT TOP 1 * FROM [Order Status] LEFT JOIN Lead_Free_Certs ON (CAST([Order Status].[Project #] AS nvarchar(255)) = Lead_Free_Certs.[Project Num]) WHERE (OrderStatusID > " & CurrentRecordID & ") ORDER BY OrderStatusID, Lead_Free_Certs.[Cert Num] DESC
However if a site has more then one certificate, only the first one is returned because of the SELECT TOP 1 statement. How can this statement be modified so that it would return one row form the [Order Status] table and multiple rows from the Lead_Free_Certs table? Is this even possible? Please keep in mind that because of the way the database was set up the SELECT TOP 1 (or something similar) must remain in place because I finding the next highest OrderStatusID. Thanks in advanced for any help.
|
|
|
|
|
How about
select *
from lead_free_certs
join (
SELECT TOP 1 *
FROM [Order Status]
WHERE OrderStatusID > " & CurrentRecordID & "
ORDER BY OrderStatusID
) t on (CAST(t.[Project #] AS nvarchar(255)) = Lead_Free_Certs.[Project Num])
|
|
|
|
|
When a record is being loaded from the order status table, if there isn't a matching project number in the lead_free_certs table, then no rows are returned. Would an Right Join work in this circumstance?
|
|
|
|
|
By using an right join it worked, the only problem was that it would have to change a lot of code because all the column indexes are different. I tried reversing the two section of the SQL Statement and came up with:
SELECT TOP 1 * FROM [Order Status] WHERE OrderStatusID > " & CurrentRecordID & " ORDER BY OrderStatusID JOIN (SELECT * FROM Lead_Free_Certs) t ON t.[Project Num] = CAST([Order Status].[Project #] AS nvarchar(255))
However I'm getting an error about an error near the JOIN but I don't know what it is.
|
|
|
|
|
I am having an interview on Friday 25 March. Most of the subject matter I am comfortable with, but my SQL is a bit rusty due to not using it for around 7 years. Now I pose to you denizens to ask me a question.
0. Something that would be likely for an interview,
1. Concerning SQL - restrict it to Oracle or SQL Server,
2. That I can try to answer.
I won't use any search, but will try and answer from the thick region of me head.
Thank you guys [and gals].
Panic, Chaos, Destruction.
My work here is done.
or "Drink. Get drunk. Fall over." - P O'H
OK, I will win to day or my name isn't Ethel Crudacre! - DD Ethel Crudacre
|
|
|
|
|
SQL Server Question (taken from sql server central question of the day!)
CREATE TABLE #Money
(ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Date DATETIME,
Amount INT,)
SET IDENTITY_INSERT #Money ON
INSERT INTO #Money(ID,Date,Amount)
SELECT '1', ' 2/10/2010 ','12' UNION ALL
SELECT '2', ' 2/11/2010 ','13' UNION ALL
SELECT '3', ' 2/12/2010 ','14' UNION ALL
SELECT '4', ' 2/13/2010 ','15' UNION ALL
SELECT SUM(AMOUNT) FROM #Money
Will the create statement fail?
will the insert statement fail?
will the select sum(amount) from #money fail?
Possible answers
1. The CREATE TABLE statement will fail, and hence so will all the following sql statements.
2. The INSERT INTO statements will fail and hence so will all the following sql statements
3. The SELECT SUM(Amount) will return a value of 54
4. The SELECT SUM(Amount) .. statement will fail
As barmey as a sack of badgers
Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
|
|
|
|
|
0. I'm not sure #Money is a valid table name; but I'll accept it for now.
1. The end of the CREATE statement is invalid, there is an extra comma - Amount INT,), so it and everything else fails.
2. If the CREATE was okay then I think the rest is fine and the SELECT will return 54.
Panic, Chaos, Destruction.
My work here is done.
or "Drink. Get drunk. Fall over." - P O'H
OK, I will win to day or my name isn't Ethel Crudacre! - DD Ethel Crudacre
|
|
|
|
|
0. Tables names with # in sql server are temporary tables.
1. I thought the same but sql sever ignores this and creates the table and tested the actual create table statement myself, but dont know why!
the correct answer is 54.
Explanation:
From CAST and CONVERT (Transact-SQL)[^]
Implicit Conversions:
Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. Explicit conversions are those conversions that require the CAST or CONVERT function to be specified.
Scroll down to the illustration which shows all explicit and implicit data type conversions that are allowed for SQL Server system-supplied data types. These include xml, bigint, and sql_variant.
There is no implicit conversion on assignment from the sql_variant data type, but there is implicit conversion to sql_variant.
As barmey as a sack of badgers
Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
|
|
|
|
|
Simon's question is a good one, and you can probably expect more than one of that sort of tests if the use of SQL is an integral part of the job.
If SQL is just "good to know" for this job, you can expect some simple questions measuring your basic understanding of SQL:
- What does USE OUR_COMPANY_LTD_DATABASE do?
- What is the difference between UNION and UNION ALL ?
- What does COALESCE do ?
- What is the difference between INNER JOIN and LEFT OUTER JOIN ?
- How will ISNULL() help you in getting reliable results in queries using the SUM() function.
My advice is free, and you may get what you paid for.
|
|
|
|
|
|
|
|
We have a vendor installed application that installed SQLServer 2008 Express with a named instance. I just completed an upgrade from Express to Standard edition, but the database is still showing as 'Microsoft SQL Server Express Edition'.
Can anyone give me directions on how to switch the instance to a standard edition? We need to be able to use SQLServer agent for backups and other routine maintenance.
Thanks,
Tim
|
|
|
|
|
Hi,
Few additional questions:
- Where do you see the Express Edition text? If it's the tools, they may still be Express versions
- What does the errorlog say? In the beginning you have the version info from the dbms itself, is it also Express?
mika
|
|
|
|
|
Is it simply the name of the instance?
|
|
|
|
|
It's possible your upgrade just installed the standard version alongside the express, with its own named instance. That's what the R2 Express edition did to my machine. Using the management tools, look for another instance on the host. If you find another, export the databases from the express version, then import them to the new one.
Will Rogers never met me.
|
|
|
|
|
1. MS-sql 2008 Server(windows2008, firewall off)
2. Windows7 64bit(ms-sql2008)
3. Windows xp 32bit
My Windows version is windows7 64bit.
I can connect other ms-sql server which windows2003.
But i can't connect ms-sql server which windows 2008.
But other pc(windows xp) can connect ms-sql server which windows2008, 2003.
So, I turn off firewall off windows 2008 server.
But still now, i can't connect.
Ping from my computer to windows 2008 is good.
Also I can connect 80 port(but can't 1433 port.)
How can i solve this problem?
hi
My english is a little.
anyway, nice to meet you~~
and give me your advice anytime~
|
|
|
|
|
can you connect with the sql sever client tools i.e. sql server management studio? if not what error message does it give?
As barmey as a sack of badgers
Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
|
|
|
|
|
|
Windows 2008 server has ms-sql 2008.
And my pc has windows7.
I run cmd and input
"telnet 192.111.111.11 1433"
my pc can't connect But other pc(windows xp) can connect.
How can i connect?
hi
My english is a little.
anyway, nice to meet you~~
and give me your advice anytime~
|
|
|
|
|
Hi,
Why would you connect to SQL Server using telnet? Although you could make the connection, SQL Server is dropping you off immediately when something is sent.
If you're trying to resolve connectivity issues, try using telnet against the telnet server port (typically 23) or simply ping to check that you can find the server.
|
|
|
|
|
To test server, i Input telnet command.
Database server ip is 198.1.1.123(example).
Other two pc(windows xp) can connect database server, but only my pc can't connect by using telnet.
hi
My english is a little.
anyway, nice to meet you~~
and give me your advice anytime~
|
|
|
|
|
Ok, so it's a connectivity issue. Try disabling the firewall in Windows 7. Also check that you are not blocked by any other firewalls, routers etc. You could also try pinging the server to see if it acts differently.
|
|
|
|
|
Could be caused by the firewall of Windows 7 - turn it completely off and try again. If it works, try to adjust your firewall such that it is still active but still allows the connection to SQL server.
There could be other issues like domain membership, group policies etc.
By the way, when trying telnet on my Win7 computer, I get a "command not found" message...
|
|
|
|
|
Bernhard Hiller wrote: By the way, when trying telnet on my Win7 computer, I get a
"command not found" message...
You need to turn it on.
Go to controlpanel, programs and features, turn windows features on or off.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
