Need to sum columns: select

Showing posts with label select. Show all posts

Wednesday, March 28, 2012

Net-Lib Question

Hi,
In the Server Network Utility (sql server 2000), if I do not select or enabl
e any net-libs such as TCP/IP, Names Pipes, Multiprotocol in the server, use
rs from client machine will still be able to connect to the server? How is t
his possible?
When the client is connecting to the server, which is the default network pr
otocol it uses and can it be changed?
Also If I want to clients to use Multiprotocol net-lib to connect to the ser
ver, what are the configurations I need to make both at the server and clien
t?
One more question is; If I have 2 instances of sql server 2000 on the same
machine, on the named instance, in server network utility, I Cannot see Mult
iprotol net-lib, any idea why?
Thanks
GYKThere is not default server side net library.
Multiprotocol cannot be enabled on a named instance. See the CLient and
Network Net-Libraries topic in Books on Line.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Net-Lib Question

Hi,
In the Server Network Utility (sql server 2000), if I do not select or enable any net-libs such as TCP/IP, Names Pipes, Multiprotocol in the server, users from client machine will still be able to connect to the server? How is this possible?
When the client is connecting to the server, which is the default network protocol it uses and can it be changed?
Also If I want to clients to use Multiprotocol net-lib to connect to the server, what are the configurations I need to make both at the server and client?
One more question is; If I have 2 instances of sql server 2000 on the same machine, on the named instance, in server network utility, I Cannot see Multiprotol net-lib, any idea why?
Thanks
GYKThere is not default server side net library.
Multiprotocol cannot be enabled on a named instance. See the CLient and
Network Net-Libraries topic in Books on Line.
Rand
This posting is provided "as is" with no warranties and confers no rights.sql

Friday, March 23, 2012

Nested XML with XML Explicit

I am using SQL Server 2005.

I want to assign the result of a SELECT FOR XML EXPLICIT statement having an order by clause to a XML Variable such as

DECLARE @.outputXML as XML

SET @.outputXML = (
SELECT 1 as TAG, NULL as PARENT, BatchID as [Batch!1!id], NULL as [Sequence!2!id],NULL as [Step!3!id], NULL as [Device!4!DeviceName]FROM BatchDeviceMapping WHERE BatchID = 32
UNION
SELECT 2 as Tag, 1 as Parent, BatchID, SequenceID, NULL,NULL FROM BatchDeviceMapping WHERE batchID = 32
UNION
SELECT 3 as Tag, 2 as Parent, BatchID, SequenceID, StepID, NULL FROM BatchDeviceMapping WHERE batchID = 32
UNION
SELECT 4 as Tag, 3 as Parent, BatchID, SequenceID, StepID, Device.DeviceName FROM BatchDeviceMapping
JOIN Device Device on BatchDeviceMapping.Deviceid = device.deviceid WHERE batchID = 32 Order by 3,4,5,6 FOR XML EXPLICIT)
Its always erroring with the following information
"Msg 1086, Level 15, State 1, Line 16
The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it."

Please note: The select query is working fine with FOR XML EXPLICIT and Order By Clause - The problem is with assigning the result of SELECT to the variable.

Any help would be appreciated.

Thanks,
Loonysan

See if this works

DECLARE @.outputXML as XML

set @.outputXML =
(
select TAG,PARENT,[Batch!1!id],[Sequence!2!id],[Step!3!id],[Device!4!DeviceName] from
(SELECT 1 as TAG, NULL as PARENT, BatchID as [Batch!1!id], NULL as [Sequence!2!id],NULL as [Step!3!id], NULL as [Device!4!DeviceName]FROM BatchDeviceMapping WHERE BatchID = 32
UNION
SELECT 2 as Tag, 1 as Parent, BatchID, SequenceID, NULL,NULL FROM BatchDeviceMapping WHERE batchID = 32
UNION
SELECT 3 as Tag, 2 as Parent, BatchID, SequenceID, StepID, NULL FROM BatchDeviceMapping WHERE batchID = 32
UNION
SELECT 4 as Tag, 3 as Parent, BatchID, SequenceID, StepID, Device.DeviceName FROM BatchDeviceMapping) as A
JOIN Device Device on BatchDeviceMapping.Deviceid = device.deviceid WHERE batchID = 32 Order by 3,4,5,6 FOR XML EXPLICIT)

|||

Thanks... This modified query from yr logic works..

DECLARE @.outputXML as XML

set @.outputXML =

(select TAG,PARENT,[Batch!1!id],[Sequence!2!id],[Step!3!id],[Device!4!DeviceName] from

(SELECT 1 as TAG, NULL as PARENT, BatchID as [Batch!1!id], NULL as [Sequence!2!id],NULL as [Step!3!id], NULL as [Device!4!DeviceName]FROM BatchDeviceMapping WHERE BatchID = 32
UNION
SELECT 2 as Tag, 1 as Parent, BatchID, SequenceID, NULL,NULL FROM BatchDeviceMapping WHERE batchID = 32
UNION
SELECT 3 as Tag, 2 as Parent, BatchID, SequenceID, StepID, NULL FROM BatchDeviceMapping WHERE batchID = 32
UNION
SELECT 4 as Tag, 3 as Parent, BatchID, SequenceID, StepID, Device.DeviceName FROM BatchDeviceMapping
JOIN Device Device on BatchDeviceMapping.Deviceid = device.deviceid where batchid = 32) as A
Order by 3,4,5,6 FOR XML EXPLICIT)
SELECT @.outputXML

Thanks,
Loonysan

|||Hi,

I'm looking for a similar solution but for SQL Server 2000. Any idea ?

(The error in SQL2000 is "Incorrect syntax near 'XML'.")

Thanks
Sylvain|||Can you give more details? The problem was not a specific one to SQL 2K5.|||

I am using SQL 2005.

I am trying to assign the result of FOX XML AUTO to a xml variable which looks something like this:

SELECT @.auditXML = (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

FOR XML AUTO, TYPE)

It gives me this error

Msg 1086, Level 15, State 1, Procedure sf_department_openCloseFolder, Line 312

The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.

I have exactly the same problem as discussed in this thread. The query works fine on its own. It is only the assignment which throws this error.

Can somebody please help? I am stuck mid-way.

Thankyou,

Umaima

|||

Try this:

SELECT @.auditXML =(select objectid,ObjectType,ParentId from (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

) as A FOR XML AUTO, TYPE)

|||

This post was extremely useful, I was trying to insert xml into a table variable where the xml was result of for XML explicit statement

Thanks

|||I had the exact same error. I solved it in the same way as you did , but my problem is that the query doesnt always run as i expect it to. Every so often Sql server throughs me back this error.
The original query worked without the error. All I did was wrap it in another select, exactly is done in the solution on this thread

6833 Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.

Does anybody know why that would be the case.|||Is it possible because of bad data? Since the error seems to be random, I would check the data or any other query which gets executed before this query that might change the assumptions your current query is making.|||You are getting the error because of ordering of the data.After applying the order by clause, the result set should have the parent tag ID appear before the child tag id.You can verify that by executing the query without the for xml clause.

Nested XML with XML Explicit

I am using SQL Server 2005.

I want to assign the result of a SELECT FOR XML EXPLICIT statement having an order by clause to a XML Variable such as

DECLARE @.outputXML as XML

Please note: The select query is working fine with FOR XML EXPLICIT and Order By Clause - The problem is with assigning the result of SELECT to the variable.

Any help would be appreciated.

Thanks,
Loonysan

See if this works

DECLARE @.outputXML as XML

|||

Thanks... This modified query from yr logic works..

DECLARE @.outputXML as XML

set @.outputXML =

(select TAG,PARENT,[Batch!1!id],[Sequence!2!id],[Step!3!id],[Device!4!DeviceName] from

Thanks,
Loonysan

|||Hi,

I'm looking for a similar solution but for SQL Server 2000. Any idea ?

(The error in SQL2000 is "Incorrect syntax near 'XML'.")

Thanks
Sylvain|||Can you give more details? The problem was not a specific one to SQL 2K5.|||

I am using SQL 2005.

I am trying to assign the result of FOX XML AUTO to a xml variable which looks something like this:

SELECT @.auditXML = (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

FOR XML AUTO, TYPE)

It gives me this error

Msg 1086, Level 15, State 1, Procedure sf_department_openCloseFolder, Line 312

I have exactly the same problem as discussed in this thread. The query works fine on its own. It is only the assignment which throws this error.

Can somebody please help? I am stuck mid-way.

Thankyou,

Umaima

|||

Try this:

SELECT @.auditXML =(select objectid,ObjectType,ParentId from (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

) as A FOR XML AUTO, TYPE)

|||

This post was extremely useful, I was trying to insert xml into a table variable where the xml was result of for XML explicit statement

Thanks

6833 Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.

Nested XML with XML Explicit

I am using SQL Server 2005.

I want to assign the result of a SELECT FOR XML EXPLICIT statement having an order by clause to a XML Variable such as

DECLARE @.outputXML as XML

Please note: The select query is working fine with FOR XML EXPLICIT and Order By Clause - The problem is with assigning the result of SELECT to the variable.

Any help would be appreciated.

Thanks,
Loonysan

See if this works

DECLARE @.outputXML as XML

|||

Thanks... This modified query from yr logic works..

DECLARE @.outputXML asXML

set @.outputXML =

(select TAG,PARENT,[Batch!1!id],[Sequence!2!id],[Step!3!id],[Device!4!DeviceName] from

(SELECT 1 as TAG,NULLas PARENT, BatchID as [Batch!1!id],NULLas [Sequence!2!id],NULLas [Step!3!id],NULLas [Device!4!DeviceName]FROM BatchDeviceMapping WHERE BatchID = 32
UNION
SELECT 2 as Tag, 1 as Parent, BatchID, SequenceID,NULL,NULLFROM BatchDeviceMapping WHERE batchID = 32
UNION
SELECT 3 as Tag, 2 as Parent, BatchID, SequenceID, StepID,NULLFROM BatchDeviceMapping WHERE batchID = 32
UNION
SELECT 4 as Tag, 3 as Parent, BatchID, SequenceID, StepID, Device.DeviceName FROM BatchDeviceMapping
JOIN Device Device on BatchDeviceMapping.Deviceid = device.deviceid where batchid = 32)as A
Orderby 3,4,5,6 FORXMLEXPLICIT)
SELECT @.outputXML

Thanks,
Loonysan

|||Hi,

I'm looking for a similar solution but for SQL Server 2000. Any idea ?

(The error in SQL2000 is "Incorrect syntax near 'XML'.")

Thanks
Sylvain
|||Can you give more details? The problem was not a specific one to SQL 2K5.|||

I am using SQL 2005.

I am trying to assign the result of FOX XML AUTO to a xml variable which looks something like this:

SELECT @.auditXML = (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

FOR XML AUTO, TYPE)

It gives me this error

Msg 1086, Level 15, State 1, Procedure sf_department_openCloseFolder, Line 312

I have exactly the same problem as discussed in this thread. The query works fine on its own. It is only the assignment which throws this error.

Can somebody please help? I am stuck mid-way.

Thankyou,

Umaima

|||

Try this:

SELECT @.auditXML =(select objectid,ObjectType,ParentId from(SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] =MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUPBY ObjectType, ParentId

HAVING ObjectType = 3

)as A FORXMLAUTO,TYPE)

|||

This post was extremely useful, I was trying to insert xml into a table variable where the xml was result of for XML explicit statement

Thanks

6833 Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.

Does anybody know why that would be the case.

|||Is it possible because of bad data? Since the error seems to be random, I would check the data or any other query which gets executed before this query that might change the assumptions your current query is making.|||You are getting the error because of ordering of the data.After applying the order by clause, the result set should have the parent tag ID appear before the child tag id.You can verify that by executing the query without the for xml clause.

Nested XML with XML Explicit

I am using SQL Server 2005.

I want to assign the result of a SELECT FOR XML EXPLICIT statement having an order by clause to a XML Variable such as

DECLARE @.outputXML as XML

Please note: The select query is working fine with FOR XML EXPLICIT and Order By Clause - The problem is with assigning the result of SELECT to the variable.

Any help would be appreciated.

Thanks,
Loonysan

See if this works

DECLARE @.outputXML as XML

|||

Thanks... This modified query from yr logic works..

DECLARE @.outputXML as XML

set @.outputXML =

(select TAG,PARENT,[Batch!1!id],[Sequence!2!id],[Step!3!id],[Device!4!DeviceName] from

Thanks,
Loonysan

|||Hi,

I'm looking for a similar solution but for SQL Server 2000. Any idea ?

(The error in SQL2000 is "Incorrect syntax near 'XML'.")

Thanks
Sylvain
|||Can you give more details? The problem was not a specific one to SQL 2K5.|||

I am using SQL 2005.

I am trying to assign the result of FOX XML AUTO to a xml variable which looks something like this:

SELECT @.auditXML = (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

FOR XML AUTO, TYPE)

It gives me this error

Msg 1086, Level 15, State 1, Procedure sf_department_openCloseFolder, Line 312

I have exactly the same problem as discussed in this thread. The query works fine on its own. It is only the assignment which throws this error.

Can somebody please help? I am stuck mid-way.

Thankyou,

Umaima

|||

Try this:

SELECT @.auditXML =(select objectid,ObjectType,ParentId from (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

) as A FOR XML AUTO, TYPE)

|||

This post was extremely useful, I was trying to insert xml into a table variable where the xml was result of for XML explicit statement

Thanks

6833 Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.

Does anybody know why that would be the case.

Nested XML with XML Explicit

I am using SQL Server 2005.

I want to assign the result of a SELECT FOR XML EXPLICIT statement having an order by clause to a XML Variable such as

DECLARE @.outputXML as XML

Please note: The select query is working fine with FOR XML EXPLICIT and Order By Clause - The problem is with assigning the result of SELECT to the variable.

Any help would be appreciated.

Thanks,
Loonysan

See if this works

DECLARE @.outputXML as XML

|||

Thanks... This modified query from yr logic works..

DECLARE @.outputXML as XML

set @.outputXML =

(select TAG,PARENT,[Batch!1!id],[Sequence!2!id],[Step!3!id],[Device!4!DeviceName] from

Thanks,
Loonysan

|||Hi,

I'm looking for a similar solution but for SQL Server 2000. Any idea ?

(The error in SQL2000 is "Incorrect syntax near 'XML'.")

Thanks
Sylvain
|||Can you give more details? The problem was not a specific one to SQL 2K5.|||

I am using SQL 2005.

I am trying to assign the result of FOX XML AUTO to a xml variable which looks something like this:

SELECT @.auditXML = (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

FOR XML AUTO, TYPE)

It gives me this error

Msg 1086, Level 15, State 1, Procedure sf_department_openCloseFolder, Line 312

I have exactly the same problem as discussed in this thread. The query works fine on its own. It is only the assignment which throws this error.

Can somebody please help? I am stuck mid-way.

Thankyou,

Umaima

|||

Try this:

SELECT @.auditXML =(select objectid,ObjectType,ParentId from (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

) as A FOR XML AUTO, TYPE)

|||

This post was extremely useful, I was trying to insert xml into a table variable where the xml was result of for XML explicit statement

Thanks

6833 Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.

Does anybody know why that would be the case.

Wednesday, March 21, 2012

Nested table concept in SQL Server

Hi all,

What is the equivalent for Oracle's nested table concept in SQL Server ?
Is there anything like TABLE( ) function to select from nested table as in Oracle ?

Eg in Oracle :

SELECT t.* FROM TABLE(nested_table_datatype) t;

( like the above query used in Oracle PL/SQL and 'nested_table_datatype' is a table datatype created in Oracle using 'create type ...' syntax )

Please give the equivalent for above...

Thanks,
Samthank $deity, there is no equivalent in sql server, for nested tables are evil|||MS-SQL is a relational database. Oracle is a database with an SQL-like command language. There is a significant difference, neither one is inherantly better or worse than the other. They aren't comparable.

No relational database can have anything like Oracle's nested tables. Nested tables implicitly violate the first normal form.

In a relational database like MS-SQL, you can do exactly the same thing as a nested table by using a foreign key relationship. Create a second table using the same schema you would use for an Oracle nested table, adding a "link" column. Include the value from the "link" column in the parent row, so that you can join the main table to the logically "nested" table.

-PatPsql

Nested SQL(Nested SQL(Nested SQL(Nested SQL)))

Can you give a whole SQL statement an alias so you can use it later?

Eg.

SELECT * FROM Employees WHERE age < 19
-- Could I call the above statement something like 'statement1' to use below as shown

SELECT * FROM Employees WHERE age < 25 AND NOT IN (statement1)

Soin effect I get a nested statement.
The reason I am asking about aliases is because this would need to be repeated for, E.g. age < 30 Then age < 35 and so on and so forth.

So basically, I just want to alias a qhole SQL statement

Any help would be greatly appreciated - Georgethe concept you are thinking of is called a view

so, the answer is yes, you can|||Could you elaborate slightly please?
Could you perhaps include an example of hwo to reference/alias/view an sql statement.

Thanks mate|||Something like this?CREATE VIEW stat_1 AS
SELECT * FROM employees WHERE age < 19;

CREATE VIEW stat_2 AS
SELECT * FROM employees
WHERE age < 25
AND employee_id NOT IN (SELECT employee_id FROM stat_1);|||I'm using a program called QUERY ANALYZER in which I am the following (and just to confuse I am now trying qualifications..

CREATE VIEW stat_1 AS
SELECT e.employee_number
FROM pwa_master.employee e, pwa_master.trgqual t
WHERE t.qualification_level = 'ALEV'

CREATE VIEW stat_2 AS
SELECT e.employee_number, t.qualification_level
FROM pwa_master.employee e, pwa_master.trgqual t
WHERE t.qualification_level = 'GCSE'
AND e.employee_number NOT IN (SELECT e.employee_number FROM stat_1)

From which I get the following message:

Server: Msg 2714, Level 16, State 5, Procedure stat_1, Line 2
There is already an object named 'stat_1' in the database

I don't know what I have done, but it doesn't look good.

Normally in this program if I do a select it only displays the results, which is all I want to do, not create anything in the database! :S :o :confused: :shocked:|||if you run CREATE VIEW stat_1 more than once, guess what happens on the second attempt?

a view is simple a query definition -- it's exactly what you want

yes, the query definition gets stored in your database

if you don't want to store the view definition in your database, you cannot use a view|||I dont want to store anything in the database, just use SQL to extract the data I need.

So are there any other methods I can use instead of a view?

P.S. When a view is created, where in the database is it stored?|||I dont want to store anything in the database, just use SQL to extract the data I need.you can always do that

So are there any other methods I can use instead of a view?depends on what you are really trying to do, which isn't really clear

P.S. When a view is created, where in the database is it stored?in the system tables|||Ok, I'll try and clarify.

Employees table is linked to a table called Training and Qualifications.
I have been asked to produce an SQL statement that selects an employees' highest qualification. Unfortunately I cannot do this with things such as child functions or even entry date as this will be innacurate.

Unfortunately there is no ranking available in the database to signify which qualification is highest E.g. GCSE, ALevels, OLevels, Masters etc etc.

So I thought that if I could say, have one SQL statement for each and gave them each an alias I could do the following:

SELECT employee_number
FROM training_and_qualifications
WHERE Qualification_Level = 'GCSE'
AND Qualification_Level NOT IN [SQL Statement 2]

the previous one would be of a similar format again, but GCSE could be replaced with 'ALEV' and used NOT IN [SQL Statement 3].

Is that any better?
If not - I'm sorry :o|||you should have posted your original requirements first, instead of asking how to accomplish something really complicated

"I have been asked to produce an SQL statement that selects an employees' highest qualification"

please give information about the ranking of these qualifications|||That's the problem, there is no ranking.

I would have to do it logically which is why I was building this sql statement.
I would take the highest qualification (Masters I think) and that would be my very first SQL statement.

Then I would perform another one for, say, ALevels and use "NOT IN MastersSQLStatement"

Followed by GCSE (?) and use "NOT IN MastersSQLStatement OR NOT IN ALevelsSQLStatement"

Sorry for complicating the issue.|||if there is no ranking, then you could never pick the highest one, could you

so of course there's a ranking

you said yourself that Masters is the highest, but i bet Doctors is even higher, and Bachelors is next highest after Masters, and so on

if an employee has several qualifications, how are they stored?

you need to give information about your table structure|||There is no ranking stored in the database, I am producing the order myself.
I wouldn't have a problem if in the database each qualification has a rank number, but I am unable to amend the tables to do such a thing.

training_and_qualifications table is linked to the employees table by the employees unique_identifier.

In the training_and_qualifications table each row has its own unique_identifier.

therefore one employee can have many qualifications, linked by the employee unique ID|||There is no ranking stored in the database, I am producing the order myself.i do realize there is no ranking stored in the database, however, if you don't share this information with me, i cannot help you

what are the rankings?|||This discussion reminds me of something that happened ages ago when I was a teenager and worked in a service station. One morning I showed up to open up, and found a car parked on the driveway, no keys, with a note on the windshield that said "something is wrong".

Looking at the car, my first thought was "Obviously" which was quickly followed by "What is this thing doing squarely in the middle of my driveway?" although the phrasing was a bit different. ;)

I'm sure that we can help you. I'm comfortable that your database design needs some small changes, specifically you need a table that includes all of the qualifications you use with a ranking for each of them.

As we know that this uses Microsoft SQL Server, and is almost certainly using SQL 2000 instead of SQL 2005, that helps us too. In order to get more relevant comments, I'm going to move the whole thread to the Microsoft SQL Server forum where more people will find it and contribute to helping solve your problem(s).

At the top of every page in the Microsoft SQL forum is a FAQ. In the FAQ is a post describing how to get fast and correct answers to your questions. Please read that post, the ideas there will help you get a faster and less frustrating answer for your question!

-PatP|||for the sake of a quick response (there are 77 different qualifications in the code table). Here is a condensed list or 5 in order.

Highest

MAST (Masters)
ALEV (A Level)
ASLEV (AS Level)
GCSE (GCSE)
CG (City and Guilds)

Lowest

Hope it helps|||Ok, so create a list of the qualifications (if you show your table layouts, I can provide code that will help you with this), and then assign each one a number. I'd start numbering at 100000 and bump the number by 1000 for each higher qualification. That gives you lots of room in case you need to rearrange things later.

Once you create this table, then things are relatively easy, but we'll take things one step at a time for now.

-PatP|||Thanks Pat.

The database contains a table called "Qualifications_Code_List" which has 77 rows, each one has a unique "Qualification_Code" and "Description".

I know the simplest way would be to add a third column in which I could assign a numeric value for rank. BUT I am supposed to be doing this without changing the database, hence this workaround.

tomake a change to the tables would require exclusive access, i.e. booting everyone off the system. At the end of the month we are taking the system offline for 6hours, so if all else fails I can impliment this then along with lots the other changes.

I'm just being nagged to give these people an answer sooner than that :p

Oh, and I realise my original post was.. well.. rubbish, so I apologise.
I will read up on the FAQ and hopefull next time I will be better.

thanks for all the replies everyone :beer: cheers!|||Ok, so create a list of the qualifications (if you show your table layouts, I can provide code that will help you with this), and then assign each one a number. I'd start numbering at 100000 and bump the number by 1000 for each higher qualification. That gives you lots of room in case you need to rearrange things later.

Once you create this table, then things are relatively easy, but we'll take things one step at a time for now.

-PatP

Very good idea using 1000's instead, I wouldn't have thought of that, thanks!|||I'm just being nagged to give these people an answer sooner than that :ptell them you cannot do it until they provide you with a list in sorted order

how the heck are you supposed to run a query if you're not sure about the ranking values?

there's no way i would guess at how to find the "highest" value from amongst 77 different values

and i would have no problem telling the users that they aren't going to get what they want until they are more forthcoming|||Check out ALTER TABLE, it requires the use of SQL syntax instead of the GUI, but it allows you to add a column in real time without your users being aware that anything happened.

The next time they do a SELECT * FROM yourTable there will suddenly be a new column. Note that you should try this in a dev/test environment before you rip into production, but ALTER TABLE will have no adverse affect on online users (other than adding the column to the table).

-PatP|||IF I eventually get this with rankings in the database table, I just sort by rank or whatever...

IF I cannot amend the table but I still have an ordered list, i.e. I know the order, can I use sql to create it?

I know it will be a huge pain in the a** but still :p

Sorry eveyone :o|||SELECT employees.unique_identifier
, case when exists
( select 1
from training_and_qualifications
where employee_unique_identifier
= employees.unique_identifier
and Qualification_Level = 'MAST' )
then 'Masters'
when exists
( select 1
from training_and_qualifications
where employee_unique_identifier
= employees.unique_identifier
and Qualification_Level = 'ALEV' )
then 'A Level'
when exists
( select 1
from training_and_qualifications
where employee_unique_identifier
= employees.unique_identifier
and Qualification_Level = 'ASLEV' )
then 'AS Level'
when exists
( select 1
from training_and_qualifications
where employee_unique_identifier
= employees.unique_identifier
and Qualification_Level = 'GCSE' )
then 'GCSE'
when exists
( select 1
from training_and_qualifications
where employee_unique_identifier
= employees.unique_identifier
and Qualification_Level = 'CG' )
then 'City and Guilds'
else '*none*'
end as highest_qualification
FROM employeesthe reason this works is because a CASE expression is evaluated linearly, so the first WHEN clause that evaluates true will determine the result, which in this instance is a column called "highest_qualification"

simply create as many WHEN clauses as you have qualifications

simple, yes?|||Check out ALTER TABLE, it requires the use of SQL syntax instead of the GUI, but it allows you to add a column in real time without your users being aware that anything happened.

The next time they do a SELECT * FROM yourTable there will suddenly be a new column. Note that you should try this in a dev/test environment before you rip into production, but ALTER TABLE will have no adverse affect on online users (other than adding the column to the table).

-PatP

Sadly I have tried this in the past in our test environment, and through the query analyzer you can populate the row, but it remains unrecognised in the system (complete pain in the a**). This means when I create a query within the system for the users.. use.. I cannot select that row because it just simply cannot be seen!

We use a system called Empower which is an ex microsoft product.|||There are ten thousand solutions for almost any problem. About nine thousand, nine hundred and ninety four of them are impractical.

You could always take the "outsourcing approach" and ship all of the data overseas, have someone read your data and produce a sorted list, then you can describe the selection criteria (for order of qualifications) and tell them to discard everything except for the highest qualification. You can probably do this in less than a year, and it will probably take a few days for each run of the list, but it will be reasonably priced when it does work!

-PatP|||You can't just edit a result set in Query Analyzer. That tool is really for developers, not for users, so there is no "push" technology in it... Any changes you make are local to your own machine, they are never returned to the database.

If you use another tool to access the table such as Microsoft Access, then you can make the kind of changes you are envisioning. You can also write SQL statements to UPDATE the database, which is a bit of a pain, but is often my tool of preference.

-PatP|||SELECT employees.unique_identifier
, case when exists
( select 1
from training_and_qualifications
where employee_unique_identifier
= employees.unique_identifier
and Qualification_Level = 'MAST' )
then 'Masters'
when exists
( select 1
from training_and_qualifications
where employee_unique_identifier
= employees.unique_identifier
and Qualification_Level = 'ALEV' )
then 'A Level'
when exists
( select 1
from training_and_qualifications
where employee_unique_identifier
= employees.unique_identifier
and Qualification_Level = 'ASLEV' )
then 'AS Level'
when exists
( select 1
from training_and_qualifications
where employee_unique_identifier
= employees.unique_identifier
and Qualification_Level = 'GCSE' )
then 'GCSE'
when exists
( select 1
from training_and_qualifications
where employee_unique_identifier
= employees.unique_identifier
and Qualification_Level = 'CG' )
then 'City and Guilds'
else '*none*'
end as highest_qualification
FROM employeesthe reason this works is because a CASE expression is evaluated linearly, so the first WHEN clause that evaluates true will determine the result, which in this instance is a column called "highest_qualification"

simply create as many WHEN clauses as you have qualifications

simple, yes?

THIS WORKS!
Now to implemment the other 72 select statements :P
Atleast I can now tell them that it, atleast in theory, is possible without editing the database.

I love you ;)

Thank you everyone for all your help (and for putting up with me).|||You can't just edit a result set in Query Analyzer. That tool is really for developers, not for users, so there is no "push" technology in it... Any changes you make are local to your own machine, they are never returned to the database.

If you use another tool to access the table such as Microsoft Access, then you can make the kind of changes you are envisioning. You can also write SQL statements to UPDATE the database, which is a bit of a pain, but is often my tool of preference.

-PatP

They only really want a spreadsheet at the end of it all, so I will just run it and paste it int a spreadheet and voila! (ish) :p

cheers Pat|||Actually once you get the SQL worked out, Excel 2000 includes a nifty feature under Data | Get External Data | Database Query that makes this process a lot easier (and is repeatable too).

-PatP|||Can you give a whole SQL statement an alias so you can use it later?
This is not necessarily a VIEW: views are stored in the database, while you might just want to use the "alias" for the current query only.

In that case a common table expression is more appropriate.
CTEs precede an SQL Select statemant in a WITH subclause.

Example:WITH stat_1 AS
( SELECT e.employee_number AS n1
FROM pwa_master.employee AS e, pwa_master.trgqual AS t
WHERE t.qualification_level = 'ALEV'
),
stat_2 AS
( SELECT e.employee_number AS n2
FROM pwa_master.employee AS e, pwa_master.trgqual AS t
WHERE t.qualification_level = 'GCSE'
)
SELECT stat_1.unique_identifier,
COALESCE(n1, n2)
FROM stat_1 FULL OUTER JOIN stat_2 ON n1 = n2|||if you don't have any other method, you can always rank your items this way:

select * from yourtable order by newid()

;)

EDIT: tell your boss that you are using Bayesian classifiers to determine the ranking.|||This is not necessarily a VIEW: views are stored in the database, while you might just want to use the "alias" for the current query only.

In that case a common table expression is more appropriate.
CTEs precede an SQL Select statemant in a WITH subclause.

Example:WITH stat_1 AS
( SELECT e.employee_number AS n1
FROM pwa_master.employee AS e, pwa_master.trgqual AS t
WHERE t.qualification_level = 'ALEV'
),
stat_2 AS
( SELECT e.employee_number AS n2
FROM pwa_master.employee AS e, pwa_master.trgqual AS t
WHERE t.qualification_level = 'GCSE'
)
SELECT stat_1.unique_identifier,
COALESCE(n1, n2)
FROM stat_1 FULL OUTER JOIN stat_2 ON n1 = n2

I ran this and got the following:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ','.

I understand what the code is supposed to do - but I don't know what the syntax should be!|||you have to wait until SQL Server 2005 to use CTEs|||I have SQL Server 2005 installed on this machine - but I have nno idea how to use it. :p

But thanks for the reply mate - it's all realllllly useful!
Even if it's not something I can use now this is all great to learn!|||There one other thing you can do, provided that you know the order of the ranking.
--Create a temp-table:
CREATE TABLE #tmprank (rank INT IDENTITY, desc VARCHAR(10))

--Fill the temp-table with the ranking in the right order:
INSERT #tmprank (desc) VALUES ('MAST')
INSERT #tmprank (desc) VALUES ('ALEV')
INSERT #tmprank (desc) VALUES ('ASLEV')
INSERT #tmprank (desc) VALUES ('GCSE')
INSERT #tmprank (desc) VALUES ('CG')

--Join your query to this temp-table and order by rank:
SELECT employees.unique_identifier, t.rank, t.desc
FROM employees e
INNER JOIN #tmprank t ON t.desc = e.Qualification_Level
ORDER BY t.rank

--Drop the temp-table (closing the session will also do this)
DROP TABLE #tmprank
This is a non-intrusive way that leaves no traces (does not alter the structure of your db).

Nested SQL with condition logic

I'm trying to combine two posts I found into one solution but I think I'm going for the impossible.

Here's a shell of the nested select and I've indicated where I'd insert the nested logic.

SELECT node_id, fk_id, nd_src_tbl_name, ( ** conditional code ** )
FROM vNodeCollection as vNc

Here's the nested logic I want to insert for each row:

BEGIN
IF [vNc].nd_src_tbl_name = 'tbl_CorporateRegion'
BEGIN
SELECT cr_textFROM tbl_CorporateRegion WHERE cr_id = [vNc].fk_id
END
ELSE IF [vNc].nd_src_tbl_name = 'tblCountry'
BEGIN
SELECT country_text FROM tbl_Country WHERE country_id = [vNc].fk_id
END
END

I'm trying to be dynamic by storing my table names with my node detail information but I'm starting to realize this may not work well.

Any ideas?No, this will not work. If u want conditional statements in a select, use the CASE functionality. See Books Online for details on how to use it. Alternatively, a user defined function may work as well (if u have SQL Server 2000).|||I forgot about custom functions. After some stumbling with the syntax I was able to make it work. I have no idea if it's efficent though.

Thanks for the idea.

Nested Select?

Hi all,
I have the following table
id (autonumber)
category1 (int)
category2 (int)
booking_month (int)
booking_year (int)
I have records in the table for booking_year = 2004 and booking_year = 2005,
for example
id, category1, category2, booking_month, booking_year
1, 20, 30, 4, 2004
1, 20, 31, 10, 2004
1, 20, 30, 4, 2005
I need a SQL statement there lists all those records that are in 2004 but no
in 2005 for a particular category1.
Any ideas?
Thanks,
IvanYou could use a nested sub-query, however, you can also just select from
[booking] as B04 for booking_year = 2004 and then left join [booking] as B05
on booking_year = 2005. Only include records where B05.id is NULL.
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:uW4JVI3DFHA.548@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have the following table
> id (autonumber)
> category1 (int)
> category2 (int)
> booking_month (int)
> booking_year (int)
> I have records in the table for booking_year = 2004 and booking_year =
2005,
> for example
> id, category1, category2, booking_month, booking_year
> 1, 20, 30, 4, 2004
> 1, 20, 31, 10, 2004
> 1, 20, 30, 4, 2005
> I need a SQL statement there lists all those records that are in 2004 but
no
> in 2005 for a particular category1.
> Any ideas?
> Thanks,
> Ivan
>|||"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in
news:uW4JVI3DFHA.548@.TK2MSFTNGP14.phx.gbl:

> Hi all,
> I have the following table
> id (autonumber)
> category1 (int)
> category2 (int)
> booking_month (int)
> booking_year (int)
> id, category1, category2, booking_month, booking_year
> 1, 20, 30, 4, 2004
> 1, 20, 31, 10, 2004
> 1, 20, 30, 4, 2005
> I need a SQL statement there lists all those records that are in 2004
> but no in 2005 for a particular category1.
SELECT * FROM [tablename]
WHERE (booking_year <> 2005) AND (category1 = particular_value)
HTH|||This does not work:
SELECT o1.category2 FROM offline o1
LEFT JOIN offline o2
ON o2.booking_year = 2005
WHERE (o1.category1 = 989 AND o1.booking_month = 4 AND o1.booking_year =
2004)
and o2.id_no is null
"JohnnyAppleseed" <someone@.microsoft.com> schrieb im Newsbeitrag
news:%23qJsJP3DFHA.1188@.tk2msftngp13.phx.gbl...
> You could use a nested sub-query, however, you can also just select from
> [booking] as B04 for booking_year = 2004 and then left join [booking] as
B05
> on booking_year = 2005. Only include records where B05.id is NULL.
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:uW4JVI3DFHA.548@.TK2MSFTNGP14.phx.gbl...
> 2005,
but
> no
>|||Or like this
SELECT * FROM [tablename]
WHERE booking_year =2004 AND category1 not in (select category1 from
[tablename] t1 where year = 2005)
Hth
"Chris Cheney" <cjc1@.nospam%ucs.cam.ac.uk%no%spam%please> wrote in message
news:Xns95F9884DB1851cjc1nospamucscamacu
k@.131.111.8.48...
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in
> news:uW4JVI3DFHA.548@.TK2MSFTNGP14.phx.gbl:
>
> SELECT * FROM [tablename]
> WHERE (booking_year <> 2005) AND (category1 = particular_value)
> HTH|||The compare between category1 and category2 should be in the join.. on..
clause. Try this:
select
B04.booking_year,
B04.category1,
B04.category2
from
offline as B04
left join
offline as B05
-- join both aliases of offline on category1 and category2. also
filter B05 on 2005.
on B05.booking_year = 2005 and
B05.category1 = B04.category1 and
B05.category2 = B04.category 2
where
B04.booking_year = 2004 and
B05.id is NULL -- Doesn't matter which B05 column is null
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:OLdjle3DFHA.3120@.TK2MSFTNGP12.phx.gbl...
> This does not work:
> SELECT o1.category2 FROM offline o1
> LEFT JOIN offline o2
> ON o2.booking_year = 2005
> WHERE (o1.category1 = 989 AND o1.booking_month = 4 AND o1.booking_year =
> 2004)
> and o2.id_no is null
> "JohnnyAppleseed" <someone@.microsoft.com> schrieb im Newsbeitrag
> news:%23qJsJP3DFHA.1188@.tk2msftngp13.phx.gbl...
> B05
> but
>|||The original B04 records total 112.
The original B05 records total 24.
Logic and simple math tell me that I should get 112-24 = 88!!
When I run your suggested statement I get 85 records.
When I run this statement:
SELECT category2 FROM offline WHERE category1 = 989 AND booking_month = 4
AND booking_year = 2004 AND category2 NOT IN
(SELECT category2FROM offline WHERE category1 = 989 AND booking_month = 4
AND booking_year = 2005)
I get 87 records.
Strange indeed!!!
"JohnnyAppleseed" <someone@.microsoft.com> schrieb im Newsbeitrag
news:ubdN7t3DFHA.2232@.TK2MSFTNGP14.phx.gbl...
> The compare between category1 and category2 should be in the join.. on..
> clause. Try this:
> select
> B04.booking_year,
> B04.category1,
> B04.category2
> from
> offline as B04
> left join
> offline as B05
> -- join both aliases of offline on category1 and category2. also
> filter B05 on 2005.
> on B05.booking_year = 2005 and
> B05.category1 = B04.category1 and
> B05.category2 = B04.category 2
> where
> B04.booking_year = 2004 and
> B05.id is NULL -- Doesn't matter which B05 column is null
>
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:OLdjle3DFHA.3120@.TK2MSFTNGP12.phx.gbl...
from
as
=
2004
>|||This was my original statement that I tried but I get always 1 less than the
expected result :(
"AM" <shahdharti@.gmail.com> schrieb im Newsbeitrag
news:e8PTwj3DFHA.1496@.TK2MSFTNGP14.phx.gbl...
> Or like this
> SELECT * FROM [tablename]
> WHERE booking_year =2004 AND category1 not in (select category1 from
> [tablename] t1 where year = 2005)
> Hth
>
> "Chris Cheney" <cjc1@.nospam%ucs.cam.ac.uk%no%spam%please> wrote in message
> news:Xns95F9884DB1851cjc1nospamucscamacu
k@.131.111.8.48...
>|||Ivan,
Can you be more precise about what you want? What does
"in 2004 but no in 2005" mean? I assume it means
Find all rows where booking_year = 2004 but for which
there is not a matching row with booking_year = 2005.
But ... you haven't made it clear what "matching row" means. Does
a matching 2005 row need to have the same category1, category2,
and booking_month, or just some of those columns? And your
data here says id is "autonumber", but you show three identical id
values - does the matching 2005 row have to have the same id value?
You said you think you should get a number of rows that is
the number of 2004 rows minus the number of 2005 rows, but
how do you know every one of the 2005 rows in your table
matches exactly one 2004 row? Maybe some 2004 rows appear
twice, and maybe some 2005 rows have no corresponding 2004
row.
You may know what it means for a 2004 row to be in 2005 as
well, but unless you describe it clearly in terms of the columns of
this table, you can't expect to be able to write a query that will
give you what you want.
Steve Kass
Drew University
Ivan Debono wrote:

>Hi all,
>I have the following table
>id (autonumber)
>category1 (int)
>category2 (int)
>booking_month (int)
>booking_year (int)
>I have records in the table for booking_year = 2004 and booking_year = 2005
,
>for example
>id, category1, category2, booking_month, booking_year
>1, 20, 30, 4, 2004
>1, 20, 31, 10, 2004
>1, 20, 30, 4, 2005
>I need a SQL statement there lists all those records that are in 2004 but n
o
>in 2005 for a particular category1.
>Any ideas?
>Thanks,
>Ivan
>
>|||Perhaps you should be joining on booking_month too ?
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:OzdKJc4DFHA.3536@.TK2MSFTNGP15.phx.gbl...
> The original B04 records total 112.
> The original B05 records total 24.
> Logic and simple math tell me that I should get 112-24 = 88!!
> When I run your suggested statement I get 85 records.
> When I run this statement:
> SELECT category2 FROM offline WHERE category1 = 989 AND booking_month = 4
> AND booking_year = 2004 AND category2 NOT IN
> (SELECT category2FROM offline WHERE category1 = 989 AND booking_month = 4
> AND booking_year = 2005)
> I get 87 records.
> Strange indeed!!!
> "JohnnyAppleseed" <someone@.microsoft.com> schrieb im Newsbeitrag
> news:ubdN7t3DFHA.2232@.TK2MSFTNGP14.phx.gbl...
=
> from
[booking]
> as
booking_year
> =
> 2004
>

Nested Select to Join three tables into one result set

I'll simplify the table structure that I've inherited in order to try to
explain what I need.
Three tables - ISSUES, USERS and ASSIGN:
ISSUES
IDRecord - Primary Key
Description
DateEntered
USERS
IDRecord- Primary Key
LastName
FirstName
ASSIGN
IDRecord - Primary Key
IDDefRec - matches to IDRecord in ISSUES
IDUser - matches to IDRecord in USERS
What I want is a result set for all ISSUES entered after 7/1/2005 (for
example) that includes all of the columns from ISSUES and the FirstName and
LastName of the last user assigned to the ISSUE. The ASSIGN table can
contain many rows per ISSUE as subsequent USERS are assigned to the ISSUE.
So I figure I just need to get the TOP 1 of the ASSIGN table that matches
the ISSUE and get the corresponding USER name. I just can't figure out how
to do it in one SELECT statement.
JeffWhat the first rule of a data model' A data element has one and only
one name in a schema. So what is this magical "record_id" that appears
to be everywhere?
And why don' t you know that a row and record are totally different
concepts? Why don't you use ISO-8601 Standard date formats? Why did
you put the qualifier in the front of the names, in violation of the
ISO-11179 rules for metadata?
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
If you knew what a key was, followed ISO Standards, and underdstood DRI
action your non-existent DDL would look like this:
Why is there no resolution date in your issues? The model of time in
SQL is durations, not single dates.
CREATE TABLE Issues
(issue_nbr INTEGER NOT NULL PRIMARY KEY,
issue_description VARCHAR(255) NOT NULL);
Create a dummy user zero called "To Be Determined" or '{{TBD}}' for
when an issue arrived if you don't assign them immediately.
CREATE TABLE Users
(user_id INTEGER DEFAULT 0 PRIMARY KEY,
last_name VARCHAR(20) NOT NULL,
first_name VARCHAR(20) NOT NULL)
CREATE TABLE Assignments
(issue_nbr NOT NULL
REFERENCES Isuses (issue_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
user_id INTEGER DEFAULT '{{TBD}}' NOT NULL
REFERENCES Users (user_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (issue_nbr, user_id)
assigned_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
resolved_date DATETIME, -- null means still open);
CHECK (assigned_date <= resolved_date));
Now you have a whole tracking history.
SELECT @.my_date, I1.issue_nbr, I1.issue_description,
U1.user_id, U1.last_name, U1.first_name
FROM Issues AS I1, Assignments AS A1, Users AS U1
WHERE @.my_date BETWEEN A1.assigned_date AND A1.resolved_date
AND U1.user_id = A1.user_id
AND A1.issue_nbr = I1.issue_nbr;|||> So what is this magical "record_id" that appears
> to be everywhere?
> And why don' t you know that a row and record are totally different
> concepts? Why don't you use ISO-8601 Standard date formats? Why did
> you put the qualifier in the front of the names, in violation of the
> ISO-11179 rules for metadata?
> Why is there no resolution date in your issues? The model of time in
> SQL is durations, not single dates.
>
It appears that you missed the part where I said that I INHERITED this
structure. This is an application that the school district I work for
purchased and I have NO control over its structure. It is what it is. I
simply need to know if, given the structure that I laid out, is there a way
to return for each item in the ISSUES table beyond a parameterized date the
first and last name of the User last assigned to the Issue in the ASSIGN
table as well as all of the details of that Issue.
js|||You are screwed. Would you like an expert witness for the lawsuit?|||> You are screwed. Would you like an expert witness for the lawsuit?
;}
I guess I'll just write a stored procedure to move the records to a temp
table then and look up the User name against the Temp result set. Just was
looking for a quicker way.
js|||SELECT I.*, U.FirstName, U.LastName
FROM Issues I
INNER JOIN
(SELECT IDDefRec,
MAX(IDUser) As IDUser
FROM Assign
GROUP BY IDDefRec) A
ON I.IDRecord = A.IDDefRec
INNER JOIN USERS U
ON A.IDUser=U.IDRecord
--The above example just get the max of userid. To get the last assigned
userid, you have to add another column
in the ASSIGN table to keep track of the time of assignment.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Jeff Swanberg" <jswanberg@.swanbergcomputing.com> wrote in message
news:%23GMlmu$jFHA.3580@.TK2MSFTNGP09.phx.gbl...
> I'll simplify the table structure that I've inherited in order to try to
> explain what I need.
> Three tables - ISSUES, USERS and ASSIGN:
> ISSUES
> IDRecord - Primary Key
> Description
> DateEntered
> USERS
> IDRecord- Primary Key
> LastName
> FirstName
> ASSIGN
> IDRecord - Primary Key
> IDDefRec - matches to IDRecord in ISSUES
> IDUser - matches to IDRecord in USERS
>
> What I want is a result set for all ISSUES entered after 7/1/2005 (for
> example) that includes all of the columns from ISSUES and the FirstName
> and LastName of the last user assigned to the ISSUE. The ASSIGN table can
> contain many rows per ISSUE as subsequent USERS are assigned to the ISSUE.
> So I figure I just need to get the TOP 1 of the ASSIGN table that matches
> the ISSUE and get the corresponding USER name. I just can't figure out
> how to do it in one SELECT statement.
> Jeff
>
>|||>> I'll just write a stored procedure to move the records [sic] to a temp table t
hen and look up the User name against the Temp result set. <<
That will not work. The schema does not show when someone was assigned
to an issue, only when the issue was first entered. Created on Monday,
assigned to Tom on Tuesday, handed off to Wendy on Wednesday and thrown
to Thomas on Thursday.
The design is flawed.|||> You are screwed. Would you like an expert witness for the lawsuit?
I'm not sure that someone that is mentally unstable would qualify as an expe
rt
witness. ;->
Thomas|||On Mon, 25 Jul 2005 09:03:17 -0700, "Thomas Coleman" <replyingroup@.anywhere.
com>
wrote:
in <eeuGRJTkFHA.1444@.TK2MSFTNGP10.phx.gbl>

>I'm not sure that someone that is mentally unstable would qualify as an exp
ert
>witness. ;->
>
>Thomas
I sure hope your insults are tongue in ch

because they have no place in a
professional newsgroup. Or are you NOT a professional?
Stefan Berglund|||> I sure hope your insults are tongue in ch

because they have no place in ad">
> professional newsgroup. Or are you NOT a professional?
My sententious observations about Don Celko's behavior are as tongue and che
ek
as his remarks. ;->
Thomas

Monday, March 19, 2012

Nested SELECT query that also returns COUNT from related table

OK heres the situation, I have a Categories table and a Products table, each Category can have one or many Products, but a product can only belong to one Category hence one-to-many relationship.

Now I want to do a SELECT query that outputs all of the Categories onto an ASP page, but also displays how many Products are in each category eg.

CatID | Name | Description | No. Products

0001 | Cars | Blah blah blah | 5

etc etc

At the moment I'm doing nesting in my application logic so that for each category that is displayed, another query is run that returns the number of products for that particular category. It works ok!

However, is there a way to write a SQL Statement that returns all the Categories AND number products from just the one SELECT statement, rather than with the method I'm using outlined above? The reason I'm asking is that I want to be able to order by the number of products for each category and my method doesn't allow me to do this.

Many thanks!Use an aggregate query:

select Category.CatID,
Category.Name,
...
count(distinct Product.ProductID) ProductCount
from Categories
left outer join Products on Categories.CategoryID = Products.CategoryID
group by Category.CatID,
Category.Name,
...
order by count(distinct Product.ProductID)|||Absolutely brilliant, it works fantastically, thank you so much!! :D

Now to try and figure out how it actually works :)|||If you use Books Online to figure out how this query works, you can consider yourself to have passed SQL 101. It incorporates the most fundamental aspects of SQL programming.

Nested SELECT HELP....

Hi all,
I have the following databasedesign :

www.marinescu.dk/databasedesign.pdf

which i have a nested SELECT on but i need some more information which i
don't know how to retrieve. I have the following SELECT :

SELECT DISTINCT Resource.ResourceID, Localized.ResourceValue,
Localized.Font, Resource.ResourceName, Resource.Comment, Type.TypeName FROM
Localized, Resource,Type WHERE Localized.ResourceID = Resource.ResourceID
AND Resource.TypeID = Type.TypeID ORDER BY Resource.ResourceID

For some Resources there are Rules. I will like to have a new column named
RulesText in my query where there will be shown the RuleText if there is any
for that particular Resource.

Could anybody help me here ??

Best Regards
Mihai MarinescuSELECT DISTINCT
R.ResourceID, L.ResourceValue, L.Font, R.ResourceName, R.Comment,
T.TypeName, RL.RuleText
FROM Localized AS L
JOIN Resource AS R ON L.ResourceID = R.ResourceID
JOIN Type AS T ON R.TypeID = T.TypeID
LEFT JOIN ResourceRule AS RR ON RR.resourceid = R.resourceid
LEFT JOIN Rules AS RL ON RL.ruleid = RR.ruleid
ORDER BY R.ResourceID

(untested)

--
David Portas
----
Please reply only to the newsgroup
--

Nested SELECT ... FOR XML PATH Return ESCAPED <>s

Hi,
I'm trying to build a complicated web service request using the (much better than FOR XML EXPLICIT) PATH mode, and it's great and all except that when I nest them I am getting &lt, &gt for the nested nodes. Here's a snippit:

BEGIN

SET NOCOUNT ON;

SELECT
'P' AS "Item/DataBlkInd",
'A' AS "Item/PhoneQual/EditTypeInd",
'CITYCODE' AS "Item/PhoneQual/AddPhoneQual/City",
...
(SELECT DISTINCT
'R' AS "DataBlkInd",
'A' AS "EmailQual/EditTypeInd",
'1' AS "EmailQual/LineNum",
'T' AS "EmailQual/Type",
bpe.EmailAddress AS "EmailQual/EmailData"
FROM dbo.BPEmail bpe WHERE bpe.BusPartyId = @.CustomerId
FOR XML PATH('Item')) AS "node()",
...
FOR XML PATH('ItemAry'), ROOT('PNRBFSecondaryBldChgMods')
END

Any idea why the nested FOR XML PATH would be escaped, and how to return it as XML instead of "&lt Item &gt &lt ..."?

Many thanks!
Andy

Hi Andy

FOR XML queries per default return the resulting XML as a string value for backwards-compatibility reasons (regardless of the mode). So you should say

FOR XML PATH('Item'), TYPE

if you need the result to be XML. Also, I think you then will not need the AS "node()". Just leave the column alias away.

Best regards
Michael

Nested Select - Help

I dont have a clue what i'm doing wrong.

SELECT Tbl_Region.REGION, [NEW_HMO_CONTRACTS].[# of New Members] AS [HMO NEW CONTRACTS], [NEW_HMO_MEMBERS].[# of New Members] AS [HMO NEW MEMBERS], [TERMED_HMO_CONTRACTS].[# of Termed Contracts] AS [HMO TERMED CONTRACTS], [TERMED_HMO_MEMBERS].[# of Termed Members] AS [HMO TERMED MEMBERS]
FROM (((Tbl_Region LEFT JOIN [SELECT qry_New_Members_HMO_All_Regions_1.Reg, Count(qry_New_Members_HMO_All_Regions_1.CONTRACT_N UM) AS [# of New Members]
FROM (SELECT tbl_hmo.Reg, tbl_hmo.CONTRACT_NUM
FROM tbl_hmo LEFT JOIN tbl_hmo_History ON tbl_hmo.CONTRACT_NUM = tbl_hmo_History.CONTRACT_NUM
WHERE (((tbl_hmo_History.CONTRACT_NUM) Is Null))
GROUP BY tbl_hmo.reg, tbl_hmo.CONTRACT_NUM

) AS qry_New_Members_HMO_All_Regions_1

GROUP BY qry_New_Members_HMO_All_Regions_1.reg
) AS NEW_HMO_CONTRACTS ON Tbl_Region.REGION = [NEW_HMO_CONTRACTS].reg) LEFT JOIN (SELECT qry_New_Members_HMO_All_Regions_1.reg, Count(qry_New_Members_HMO_All_Regions_1.MEMBER_NUM ) AS [# of New Members]
FROM (SELECT tbl_hmo.reg, tbl_hmo.MEMBER_NUM
FROM tbl_hmo LEFT JOIN tbl_hmo_History ON tbl_hmo.MEMBER_NUM = tbl_hmo_History.MEMBER_NUM
WHERE (((tbl_hmo_History.MEMBER_NUM) Is Null))
GROUP BY tbl_hmo.Aff_Area, tbl_hmo.MEMBER_NUM

) AS qry_New_Members_HMO_All_Regions_1
GROUP BY qry_New_Members_HMO_All_Regions_1.reg) AS 4_NEW_HMO_MEMBERS ON Tbl_Region.REGION = [4_NEW_HMO_MEMBERS].reg) LEFT JOIN (SELECT qry_Termed_Contracts_HMO_All_Regions_1.reg, Count(qry_Termed_Contracts_HMO_All_Regions_1.CONTR ACT_NUM) AS [# of Termed Contracts]
FROM (SELECT tbl_hmo_History.reg, tbl_hmo_History.CONTRACT_NUM
FROM tbl_hmo RIGHT JOIN tbl_hmo_History ON tbl_hmo.CONTRACT_NUM = tbl_hmo_History.CONTRACT_NUM
WHERE (((tbl_hmo.CONTRACT_NUM) Is Null))
GROUP BY tbl_hmo_History.reg, tbl_hmo_History.CONTRACT_NUM
) AS qry_Termed_Contracts_HMO_All_Regions_1
GROUP BY qry_Termed_Contracts_HMO_All_Regions_1.reg) AS TERMED_HMO_CONTRACTS ON Tbl_Region.REGION = [TERMED_HMO_CONTRACTS].reg) LEFT JOIN (SELECT qry_Termed_Members_HMO_All_Regions_1.reg, Count(qry_Termed_Members_HMO_All_Regions_1.MEMBER_ NUM) AS [# of Termed Members]
FROM (SELECT tbl_hmo_History.reg, tbl_hmo_History.MEMBER_NUM
FROM tbl_hmo RIGHT JOIN tbl_hmo_History ON tbl_hmo.MEMBER_NUM = tbl_hmo_History.MEMBER_NUM
WHERE (((tbl_hmo.MEMBER_NUM) Is Null))
GROUP BY tbl_hmo_History.reg, tbl_hmo_History.MEMBER_NUM
) AS qry_Termed_Members_HMO_All_Regions_1
GROUP BY qry_Termed_Members_HMO_All_Regions_1.reg)
AS TERMED_HMO_MEMBERS ON Tbl_Region.REGION = [TERMED_HMO_MEMBERS].reg;

error:
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'FROM'.
Server: Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Line 31
Incorrect syntax near the keyword 'AS'.I haven't a clue what you are doing right. Are you getting paid by the parenthesis?

"WHERE (((tbl_hmo_History.CONTRACT_NUM) Is Null))"?

Isn't this:

"WHERE tbl_hmo_History.CONTRACT_NUM Is Null"

...simpler and easier to read?

And I suspect this may be throwing your first error:

"LEFT JOIN [SELECT qry_New_Members_HMO_All_Regions_1.Reg,..."

The square brackets denote a database object. SELECT is a statement, not an object.

Clean up your code, format it well with indents, and try running the individual components separately before putting them all together. That is the best way to debug.|||it was working in access then i brought it over to sql and then MESS...

Are you getting paid by the parenthesis? -- haha i wish.

i will work through it again tomorrow.

thanks for looking at it.|||THAT was working in ACCESS?!

...but that explains the square brackets. I've had Access throw those into free SQL querys before, and then the query won't work until you take them out again. A bug, for sure.

Was it a single free SQL statement, or were the subqueries manifested as independent views?|||I got it to work.
PLEASE let me know if you see anything wrong with what i did.

SELECT dbo.Tbl_Region.REGION_NAME, [4_NEW_HMO_CONTRACTS].[# of New Members] AS [HMO NEW CONTRACTS],
[4_NEW_HMO_MEMBERS].[# of New Members] AS [HMO NEW MEMBERS],
[5_TERMED_HMO_CONTRACTS].[# of Termed Contracts] AS [HMO TERMED CONTRACTS],
[5_TERMED_HMO_MEMBERS].[# of Termed Members] AS [HMO TERMED MEMBERS]
FROM dbo.Tbl_Region LEFT OUTER JOIN
(SELECT qry_New_Members_HMO_All_Region_Names_1.Region, COUNT(qry_New_Members_HMO_All_Region_Names_1.CONTR ACT_NUM)
AS [# of New Members]
FROM (SELECT tbl_HMO.Region, tbl_HMO.CONTRACT_NUM
FROM tbl_HMO LEFT JOIN
dbo.tbl_HMO_History ON dbo.tbl_HMO.Reg = dbo.tbl_HMO_History.Reg AND
tbl_HMO.CONTRACT_NUM = tbl_HMO_History.CONTRACT_NUM
WHERE tbl_HMO_History.CONTRACT_NUM IS NULL
GROUP BY tbl_HMO.Region, tbl_HMO.CONTRACT_NUM) AS qry_New_Members_HMO_All_Region_Names_1
GROUP BY qry_New_Members_HMO_All_Region_Names_1.Region) [4_NEW_HMO_CONTRACTS] ON
dbo.Tbl_Region.REGION_NAME = [4_NEW_HMO_CONTRACTS].Region LEFT OUTER JOIN
(SELECT qry_New_Members_HMO_All_Region_Names_1.Region, COUNT(qry_New_Members_HMO_All_Region_Names_1.MEMBE R_NUM)
AS [# of New Members]
FROM (SELECT tbl_HMO.Region, tbl_HMO.MEMBER_NUM
FROM tbl_HMO Left JOIN
dbo.tbl_HMO_History ON dbo.tbl_HMO.Reg = dbo.tbl_HMO_History.Reg AND
tbl_HMO.MEMBER_NUM = tbl_HMO_History.MEMBER_NUM
WHERE tbl_HMO_History.CONTRACT_NUM IS NULL
GROUP BY tbl_HMO.Region, tbl_HMO.MEMBER_NUM) AS qry_New_Members_HMO_All_Region_Names_1
GROUP BY qry_New_Members_HMO_All_Region_Names_1.Region) [4_NEW_HMO_MEMBERS] ON
dbo.Tbl_Region.REGION_NAME = [4_NEW_HMO_MEMBERS].Region Left OUTER JOIN
(SELECT qry_Termed_Contracts_HMO_All_Region_Names_1.Region ,
COUNT(qry_Termed_Contracts_HMO_All_Region_Names_1. CONTRACT_NUM) AS [# of Termed Contracts]
FROM (SELECT dbo.tbl_HMO_History.Region, dbo.tbl_HMO_History.Contract_Num
FROM dbo.tbl_HMO RIGHT OUTER JOIN
dbo.tbl_HMO_History ON dbo.tbl_HMO.Reg = dbo.tbl_HMO_History.Reg AND
dbo.tbl_HMO.CONTRACT_NUM = dbo.tbl_HMO_History.Contract_Num
GROUP BY dbo.tbl_HMO_History.Region, dbo.tbl_HMO.CONTRACT_NUM, dbo.tbl_HMO_History.Contract_Num
HAVING (dbo.tbl_HMO.CONTRACT_NUM IS NULL)) AS qry_Termed_Contracts_HMO_All_Region_Names_1
GROUP BY qry_Termed_Contracts_HMO_All_Region_Names_1.Region ) [5_TERMED_HMO_CONTRACTS] ON
dbo.Tbl_Region.REGION_NAME = [5_TERMED_HMO_CONTRACTS].Region Left OUTER JOIN
(SELECT qry_Termed_Members_HMO_All_Region_Names_1.Region, COUNT(qry_Termed_Members_HMO_All_Region_Names_1.ME MBER_NUM)
AS [# of Termed Members]
FROM (SELECT tbl_HMO_History.Region, tbl_HMO_History.MEMBER_NUM
FROM tbl_HMO RIGHT JOIN
dbo.tbl_HMO_History ON dbo.tbl_HMO.Reg = dbo.tbl_HMO_History.Reg AND
tbl_HMO.MEMBER_NUM = tbl_HMO_History.MEMBER_NUM
WHERE tbl_HMO.MEMBER_NUM IS NULL
GROUP BY tbl_HMO_History.Region, tbl_HMO_History.MEMBER_NUM) AS qry_Termed_Members_HMO_All_Region_Names_1
GROUP BY qry_Termed_Members_HMO_All_Region_Names_1.Region) [5_TERMED_HMO_MEMBERS] ON
dbo.Tbl_Region.REGION_NAME = [5_TERMED_HMO_MEMBERS].Region

nested select

is it possible to make this query right?
SELECT name ,(select count(*) FROM NAME) FROM sysobjects
WHERE xtype='U'
I'm trying to get the list of the tables names from the current database and
for each table to get it's rows number. I dont want to use functions. I need
to do it in one query.
Regards
OfirNope, you can't have a nested SELECT where you want the table itself to be a
value from outer SELECT.
Something like this should work, but the rowcount is not 100% accurate:
SELECT o.name, i.rows
FROM sysobjects AS o
INNER JOIN
sysindexes AS i
ON o.id = i.id
WHERE o.type = 'u'
AND i.indid in (1, 0)
You could use sp_spaceused instead. See Books Online for more info. Also,
see if this helps:
http://vyaskn.tripod.com/sp_show_biggest_tables.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"ofir" <ofir@.discussions.microsoft.com> wrote in message
news:FDFA7C45-49BE-4EAE-A2C6-A2C7428E0AC3@.microsoft.com...
is it possible to make this query right?
SELECT name ,(select count(*) FROM NAME) FROM sysobjects
WHERE xtype='U'
I'm trying to get the list of the tables names from the current database and
for each table to get it's rows number. I dont want to use functions. I need
to do it in one query.
Regards
Ofir|||Hi
You can also try using this
create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable
'insert into #rowcount select ''?'', count(*) from ?'
select * from #rowcount
drop table #rowcount
but, please note that sp_msforeachtable is an undocumented stored procedure.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"ofir" wrote:

> is it possible to make this query right?
> SELECT name ,(select count(*) FROM NAME) FROM sysobjects
> WHERE xtype='U'
> I'm trying to get the list of the tables names from the current database a
nd
> for each table to get it's rows number. I dont want to use functions. I ne
ed
> to do it in one query.
> Regards
> Ofir|||what do u mean 'the rowcount is not 100%'
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ucnoqMSYFHA.252@.TK2MSFTNGP12.phx.gbl...
> Nope, you can't have a nested SELECT where you want the table itself to be
> a
> value from outer SELECT.
> Something like this should work, but the rowcount is not 100% accurate:
> SELECT o.name, i.rows
> FROM sysobjects AS o
> INNER JOIN
> sysindexes AS i
> ON o.id = i.id
> WHERE o.type = 'u'
> AND i.indid in (1, 0)
>
> You could use sp_spaceused instead. See Books Online for more info. Also,
> see if this helps:
> http://vyaskn.tripod.com/sp_show_biggest_tables.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "ofir" <ofir@.discussions.microsoft.com> wrote in message
> news:FDFA7C45-49BE-4EAE-A2C6-A2C7428E0AC3@.microsoft.com...
> is it possible to make this query right?
> SELECT name ,(select count(*) FROM NAME) FROM sysobjects
> WHERE xtype='U'
> I'm trying to get the list of the tables names from the current database
> and
> for each table to get it's rows number. I dont want to use functions. I
> need
> to do it in one query.
> Regards
> Ofir
>|||sysindexes maintains a rowcount, but it's not 100% accurate. Only reliable
way is to SELECT COUNT(*) from the table.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"ofir" <ofir@.millenium.org.il> wrote in message
news:u$O0SHTYFHA.1404@.TK2MSFTNGP09.phx.gbl...
what do u mean 'the rowcount is not 100%'
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ucnoqMSYFHA.252@.TK2MSFTNGP12.phx.gbl...
> Nope, you can't have a nested SELECT where you want the table itself to be
> a
> value from outer SELECT.
> Something like this should work, but the rowcount is not 100% accurate:
> SELECT o.name, i.rows
> FROM sysobjects AS o
> INNER JOIN
> sysindexes AS i
> ON o.id = i.id
> WHERE o.type = 'u'
> AND i.indid in (1, 0)
>
> You could use sp_spaceused instead. See Books Online for more info. Also,
> see if this helps:
> http://vyaskn.tripod.com/sp_show_biggest_tables.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "ofir" <ofir@.discussions.microsoft.com> wrote in message
> news:FDFA7C45-49BE-4EAE-A2C6-A2C7428E0AC3@.microsoft.com...
> is it possible to make this query right?
> SELECT name ,(select count(*) FROM NAME) FROM sysobjects
> WHERE xtype='U'
> I'm trying to get the list of the tables names from the current database
> and
> for each table to get it's rows number. I dont want to use functions. I
> need
> to do it in one query.
> Regards
> Ofir
>

Nested select

Can you tell me whats wrong with this query?
select count(*) as CountedOrders
from
(
select distinct [order]
from [OrdersTable]
where
[Customer]='100000' and
[Order Date] between '01/01/2005' and '31/12/2005'
)
It runs perfect in MS Access but not in MS SQL-Server.
I need to create a stored procedure that returns the number of orders from a
specific customer on a specific period.
Thanks in advance for your help.
"John" <John@.discussions.microsoft.com> wrote in message
news:E00259AC-FF4E-44D6-8730-5800CFDAC22A@.microsoft.com...
> Can you tell me whats wrong with this query?
> select count(*) as CountedOrders
> from
> (
> select distinct [order]
> from [OrdersTable]
> where
> [Customer]='100000' and
> [Order Date] between '01/01/2005' and '31/12/2005'
> )
> It runs perfect in MS Access but not in MS SQL-Server.
> I need to create a stored procedure that returns the number of orders from
> a
> specific customer on a specific period.
> Thanks in advance for your help.
SQL requires an alias for the derived table. Also ORDER has to be delimited
because it's a keyword (and therefore not a good choice for a column name).
It's also good practice to use a locale-independent date format like I have
done below.
SELECT COUNT(*) AS countedorders
FROM
(SELECT DISTINCT [order]
FROM OrdersTable
WHERE customer='100000'
AND [order date] BETWEEN '20050101' AND '20051231'
) AS T ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||On Fri, 10 Mar 2006 22:05:19 -0000, David Portas wrote:
(snip)
>SELECT COUNT(*) AS countedorders
> FROM
> (SELECT DISTINCT [order]
> FROM OrdersTable
> WHERE customer='100000'
> AND [order date] BETWEEN '20050101' AND '20051231'
> ) AS T ;
Hi David (& John),
Or even shorter:
SELECT COUNT(DISTINCT [order])
FROM OrdersTable
WHERE customer = '100000'
AND [order date] BETWEEN '20050101' AND '20051231'
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP
|||Ο χρ?στη? "Hugo Kornelis" Xγγραψε:

> On Fri, 10 Mar 2006 22:05:19 -0000, David Portas wrote:
> (snip)
> Hi David (& John),
> Or even shorter:
> SELECT COUNT(DISTINCT [order])
> FROM OrdersTable
> WHERE customer = '100000'
> AND [order date] BETWEEN '20050101' AND '20051231'
> (untested - see www.aspfaq.com/5006 if you prefer a tested reply)
> --
> Hugo Kornelis, SQL Server MVP
>
Thanks you both David & Hugo!
Much appreciated!
John
|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:316912561c41ogeut35basn6lq4h00n58j@.4ax.com...
> On Fri, 10 Mar 2006 22:05:19 -0000, David Portas wrote:
> (snip)
> Hi David (& John),
> Or even shorter:
> SELECT COUNT(DISTINCT [order])
> FROM OrdersTable
> WHERE customer = '100000'
> AND [order date] BETWEEN '20050101' AND '20051231'
> (untested - see www.aspfaq.com/5006 if you prefer a tested reply)
> --
> Hugo Kornelis, SQL Server MVP
I wonder if Order has any nulls?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||On Mon, 13 Mar 2006 21:02:57 -0000, David Portas wrote:
(snip)
>I wonder if Order has any nulls?
Hi David,
Good catch - I hadn't though of that.
(But in a table called "OrdersTable", I would really *hope* that the
Order column is NOT NULL...)
Hugo Kornelis, SQL Server MVP
|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:jjvb12lparlmthnnllpsvrotgleknpm3ra@.4ax.com...
> (But in a table called "OrdersTable", I would really *hope* that the
> Order column is NOT NULL...)
>
Hugo, I would hope so too. But if the table is truly called "OrdersTable"
then I would *expect* that anything is possible. :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Nested Query Troubles...

Hi All,

Can anybody please tell me if a query such as this (Valid in MS Access)
can work in SQL Server:

SELECT Description, Sum(Total) FROM (
SELECT Description, Total FROM Table_A
UNION ALL
SELECT Description, Total FROM Table_B
UNION ALL
SELECT Description, Total FROM Table_C
)
GROUP BY Description

The group of unions work by themselves, but when I try to nest an outer query to do some a Summation(), I have syntax errors.

Any insight would be greatly appreciated. Thank you.You must supply an alias for your subquery:
SELECT Description,
Sum(Total)
FROM (SELECT Description,
Total
FROM Table_A
UNION ALL
SELECT Description,
Total
FROM Table_B
UNION ALL
SELECT Description,
Total
FROM Table_C) AS SUBQUERY
GROUP BY Description|||Thanks BlindMan, u da man.

:)

Wednesday, March 28, 2012

Net-Lib Question

Net-Lib Question

Friday, March 23, 2012

Nested XML with XML Explicit

Nested XML with XML Explicit

Nested XML with XML Explicit

Nested XML with XML Explicit

Nested XML with XML Explicit

Wednesday, March 21, 2012

Nested table concept in SQL Server

Nested SQL(Nested SQL(Nested SQL(Nested SQL)))

Nested SQL with condition logic

Nested Select?

Nested Select to Join three tables into one result set

Monday, March 19, 2012

Nested SELECT query that also returns COUNT from related table

Nested SELECT HELP....

Nested SELECT ... FOR XML PATH Return ESCAPED <>s

Nested Select - Help

nested select

Nested select

Nested Query Troubles...

Need to sum columns

Blog Archive

About Me