Create a table of temperature

Hello,

I don't know how to make this :

1 table containing list of cities
1 table containing date and temperature

both linked by IdCity

how can i create a table to display cities in rows, date in colums and temperature at intersections ?

Thanks !Hi,

as I get to know you, you want to join two tables. To do this you can use JOIN, RIGHT JOIN, LEFT JOIN in your SQL Statement.

it woul look like this ".... JOIN Cities ON Cieties.IdCity=Temperatures.IdCity
....."

look here for more about joins:
http://www.w3schools.com/sql/sql_join.asp|||Please take a look at the pivot operator in Books Online. You can generate the cross-tab type of report using that. This however requires knowing the values of date since that needs to be used in the pivot clause. ex:

select crp.CityName, crp.[20050101], crp.[20050102]
from Cities as c
join CityReadings as cr
on c.IdCity = cr.IdCity
pivot (max(cr.temperature) for cr.date in ([20050101], [20050102])) as crp
order by crp.CityName;
|||Ok but Pivot only works with SQL2K5 not with SQL2K ?sql

Create a SQL2000 Linked server to an EssBase server/cube

Hello,
Please forward any information that you might have on how to do this.
Thank you
Everything you need ot know is in SQL Server 2000 Books on Lline. Of course
you will need either a ODBC driver or OLEDB driver that can be used to
connect the EssBase server.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Create a SQL2000 Linked server to an EssBase server/cube

Hello,
Please forward any information that you might have on how to do this.
Thank you Everything you need ot know is in SQL Server 2000 Books on Lline. Of course
you will need either a ODBC driver or OLEDB driver that can be used to
connect the EssBase server.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Create a linked table in access to SQL server programatically

I'm very sorry to join and ask for help immediately without helping first, but I am at wits end with this.

I need to know how to promatically create a linked table in Microsoft Access 97 to a Microsoft SQL Server 2000 table. The SQL server is set to use trusted connection for authentication. Security is set up correctly because manual creation of the linked table works fine and the table can then be accessed normally. But we have tried many different methods to create this linked table programatically and all have failed.

This Microsoft Access application links tables to both an AS400 and SQL Server 2000 and we are currently using the DoCmd.TransferDatabase function. It works fine to create the linked tables to the AS400 but fails to create the linked table for SQL Server.

The whole purpose of this is because the Access application is coded with a debug mode. At each start, the application destroys linked tables and rebuilds them either locally (debug mode) or to their real homes (live mode).

We have looked at hundreds of knowledge base entries and browsed for nearly 2 days trying to find answers. Please help.

GetutI just opened up an Access97 database and entered this code into a macro
Function CreateLink()
DoCmd.TransferDatabase acLink, "ODBC Database", _
"ODBC;DSN=ATLAS;DATABASE=pubs;Trusted_Connection=Ye s ",_
acTable, "Authors", "dboAuthors"
End Function

Where the DSN = ATLAS is and ODBC connection to SQL Server 2000 on another machine. This code works fine, I ran it and the link was created.

Create a linked server between 2 sql serve

Hi,
Hw can I create a linked server between 2 sql server databases in diferente
machines?
How can I indicate the 2 catalogs and the 2 servers?
Thanks in advance
Pedro
The syntax is:
[LINKEDSERVER].[Databasename].[owner].[Objectname]
e.g. Select * from [LINKEDSERVER].[Databasename].[owner].[Objectname]
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Pedro" <Pedro@.discussions.microsoft.com> wrote in message
news:B828F7E5-C4A1-41B2-9717-D1DC9433B6DB@.microsoft.com...
> Hi,
> Hw can I create a linked server between 2 sql server databases in
> diferente
> machines?
> How can I indicate the 2 catalogs and the 2 servers?
> Thanks in advance
> Pedro
|||Hi Jens,
My question is how can I create a linkedserver.
Imagine that I have 2 sql server's, SQLSERVER1 with a catalog BOOK and
SQLSERVER2 with a catalog PUBLISHER.
I want to link this to server so I can have a trigger on a table of the BOOK
database on SQLSERVER1 invoke a storeprocedure of database PUBLISHER on
SQLSERVER2
How can I do this?
How can I setup this kind of linkedserver?
On Entreprise manager I can add a linkedserver chose "SQL Server" but I
can't setup the address of the 2 servers or the 2 databases on that 2
servers.
"Jens Sü?meyer" wrote:

> The syntax is:
> [LINKEDSERVER].[Databasename].[owner].[Objectname]
> e.g. Select * from [LINKEDSERVER].[Databasename].[owner].[Objectname]
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> news:B828F7E5-C4A1-41B2-9717-D1DC9433B6DB@.microsoft.com...
>
>
|||I wouldn=B4t do that, trigegr behave synchron, they wait for the
transaction started, so if the line between the servers isn=B4t setup
properly or the other server doesn=B4t repsond you will get into trouble
because the transaction is rolled back.
Anyway, the name of the server (in the menu of the linkedserver) is the
name of the server/instance. The database can be reached with the above
mentioned 4 part notation.
otherwise you could use the sp_addlinkedserver command which can be
found (with samples) int he BOL.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de

Create a linked server between 2 sql serve

Hi,
Hw can I create a linked server between 2 sql server databases in diferente
machines?
How can I indicate the 2 catalogs and the 2 servers?
Thanks in advance
PedroThe syntax is:
[LINKEDSERVER].[Databasename].[owner].[Objectname]
e.g. Select * from [LINKEDSERVER].[Databasename].[owner].[Objectname]
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Pedro" <Pedro@.discussions.microsoft.com> wrote in message
news:B828F7E5-C4A1-41B2-9717-D1DC9433B6DB@.microsoft.com...
> Hi,
> Hw can I create a linked server between 2 sql server databases in
> diferente
> machines?
> How can I indicate the 2 catalogs and the 2 servers?
> Thanks in advance
> Pedro|||Hi Jens,
My question is how can I create a linkedserver.
Imagine that I have 2 sql server's, SQLSERVER1 with a catalog BOOK and
SQLSERVER2 with a catalog PUBLISHER.
I want to link this to server so I can have a trigger on a table of the BOOK
database on SQLSERVER1 invoke a storeprocedure of database PUBLISHER on
SQLSERVER2
How can I do this?
How can I setup this kind of linkedserver?
On Entreprise manager I can add a linkedserver chose "SQL Server" but I
can't setup the address of the 2 servers or the 2 databases on that 2
servers.
"Jens Sü�meyer" wrote:
> The syntax is:
> [LINKEDSERVER].[Databasename].[owner].[Objectname]
> e.g. Select * from [LINKEDSERVER].[Databasename].[owner].[Objectname]
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> news:B828F7E5-C4A1-41B2-9717-D1DC9433B6DB@.microsoft.com...
> > Hi,
> >
> > Hw can I create a linked server between 2 sql server databases in
> > diferente
> > machines?
> > How can I indicate the 2 catalogs and the 2 servers?
> >
> > Thanks in advance
> >
> > Pedro
>
>|||I wouldn=B4t do that, trigegr behave synchron, they wait for the
transaction started, so if the line between the servers isn=B4t setup
properly or the other server doesn=B4t repsond you will get into trouble
because the transaction is rolled back.
Anyway, the name of the server (in the menu of the linkedserver) is the
name of the server/instance. The database can be reached with the above
mentioned 4 part notation.
otherwise you could use the sp_addlinkedserver command which can be
found (with samples) int he BOL.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--sql

Create a linked server between 2 sql serve

Hi,
Hw can I create a linked server between 2 sql server databases in diferente
machines?
How can I indicate the 2 catalogs and the 2 servers?
Thanks in advance
PedroThe syntax is:
[LINKEDSERVER].[Databasename].[owner].[Objectname]
e.g. Select * from [LINKEDSERVER].[Databasename].[owner].[Ob
jectname]
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Pedro" <Pedro@.discussions.microsoft.com> wrote in message
news:B828F7E5-C4A1-41B2-9717-D1DC9433B6DB@.microsoft.com...
> Hi,
> Hw can I create a linked server between 2 sql server databases in
> diferente
> machines?
> How can I indicate the 2 catalogs and the 2 servers?
> Thanks in advance
> Pedro|||Hi Jens,
My question is how can I create a linkedserver.
Imagine that I have 2 sql server's, SQLSERVER1 with a catalog BOOK and
SQLSERVER2 with a catalog PUBLISHER.
I want to link this to server so I can have a trigger on a table of the BOOK
database on SQLSERVER1 invoke a storeprocedure of database PUBLISHER on
SQLSERVER2
How can I do this?
How can I setup this kind of linkedserver?
On Entreprise manager I can add a linkedserver chose "SQL Server" but I
can't setup the address of the 2 servers or the 2 databases on that 2
servers.
"Jens Sü?meyer" wrote:

> The syntax is:
> [LINKEDSERVER].[Databasename].[owner].[Objectname]
> e.g. Select * from [LINKEDSERVER].[Databasename].[owner].[
Objectname]
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> news:B828F7E5-C4A1-41B2-9717-D1DC9433B6DB@.microsoft.com...
>
>|||I wouldn=B4t do that, trigegr behave synchron, they wait for the
transaction started, so if the line between the servers isn=B4t setup
properly or the other server doesn=B4t repsond you will get into trouble
because the transaction is rolled back.
Anyway, the name of the server (in the menu of the linkedserver) is the
name of the server/instance. The database can be reached with the above
mentioned 4 part notation.
otherwise you could use the sp_addlinkedserver command which can be
found (with samples) int he BOL.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--

Create a cube- only of linked objects ?

How do you create a new cube, that only exists of linked objects. As i see it, you can only create a cube by selecting source, fact table etc. - and then afterwards add linked objects to the cube....

Hmm seems like the easiest way is to create a cube, is to select at fact and dimension - finish and the delete those again. Then you have e "blank" cube and can add the linked objects....

Create a cached instance of a view from a linked server?

Hi
How do i manage to create a cache of a view from a linked server?
I have this view on a linked server (takes app. 15 secs) and i would like to
have a "local" cache of this view.
What would be the best way to handle this - to ensure both performance and
consistency?
Hope somebody can give me an answer to this.. :-)Hi
Persist the data to a local table, and have a job that refreshes the table
on a regular basis.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"pnp" <pnp@.discussions.microsoft.com> wrote in message
news:ADA26A03-95C5-4737-9176-B4C114E15EA6@.microsoft.com...
> Hi
> How do i manage to create a cache of a view from a linked server?
> I have this view on a linked server (takes app. 15 secs) and i would like
> to
> have a "local" cache of this view.
> What would be the best way to handle this - to ensure both performance and
> consistency?
> Hope somebody can give me an answer to this.. :-)|||Ok, thanks...
Thought that would be the best way - but didn't know if there were any
built-in features that allowed me to do this easier.
But thanks - i'll take this road then... :-)
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Persist the data to a local table, and have a job that refreshes the table
> on a regular basis.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "pnp" <pnp@.discussions.microsoft.com> wrote in message
> news:ADA26A03-95C5-4737-9176-B4C114E15EA6@.microsoft.com...
>
>|||Replication is an option. But I wouldn't do it for just one table. Keep it s
imple.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"pnp" <pnp@.discussions.microsoft.com> wrote in message
news:4D5977DE-FB7A-4C19-8236-6E3BC3B70C8A@.microsoft.com...[vbcol=seagreen]
> Ok, thanks...
> Thought that would be the best way - but didn't know if there were any
> built-in features that allowed me to do this easier.
> But thanks - i'll take this road then... :-)
> "Mike Epprecht (SQL MVP)" wrote:
>

Create a cached instance of a view from a linked server?

Hi
How do i manage to create a cache of a view from a linked server?
I have this view on a linked server (takes app. 15 secs) and i would like to
have a "local" cache of this view.
What would be the best way to handle this - to ensure both performance and
consistency?
Hope somebody can give me an answer to this.. :-)Hi
Persist the data to a local table, and have a job that refreshes the table
on a regular basis.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"pnp" <pnp@.discussions.microsoft.com> wrote in message
news:ADA26A03-95C5-4737-9176-B4C114E15EA6@.microsoft.com...
> Hi
> How do i manage to create a cache of a view from a linked server?
> I have this view on a linked server (takes app. 15 secs) and i would like
> to
> have a "local" cache of this view.
> What would be the best way to handle this - to ensure both performance and
> consistency?
> Hope somebody can give me an answer to this.. :-)|||Ok, thanks...
Thought that would be the best way - but didn't know if there were any
built-in features that allowed me to do this easier.
But thanks - i'll take this road then... :-)
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Persist the data to a local table, and have a job that refreshes the table
> on a regular basis.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "pnp" <pnp@.discussions.microsoft.com> wrote in message
> news:ADA26A03-95C5-4737-9176-B4C114E15EA6@.microsoft.com...
> > Hi
> >
> > How do i manage to create a cache of a view from a linked server?
> >
> > I have this view on a linked server (takes app. 15 secs) and i would like
> > to
> > have a "local" cache of this view.
> >
> > What would be the best way to handle this - to ensure both performance and
> > consistency?
> >
> > Hope somebody can give me an answer to this.. :-)
>
>|||Replication is an option. But I wouldn't do it for just one table. Keep it simple.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"pnp" <pnp@.discussions.microsoft.com> wrote in message
news:4D5977DE-FB7A-4C19-8236-6E3BC3B70C8A@.microsoft.com...
> Ok, thanks...
> Thought that would be the best way - but didn't know if there were any
> built-in features that allowed me to do this easier.
> But thanks - i'll take this road then... :-)
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Persist the data to a local table, and have a job that refreshes the table
>> on a regular basis.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "pnp" <pnp@.discussions.microsoft.com> wrote in message
>> news:ADA26A03-95C5-4737-9176-B4C114E15EA6@.microsoft.com...
>> > Hi
>> >
>> > How do i manage to create a cache of a view from a linked server?
>> >
>> > I have this view on a linked server (takes app. 15 secs) and i would like
>> > to
>> > have a "local" cache of this view.
>> >
>> > What would be the best way to handle this - to ensure both performance and
>> > consistency?
>> >
>> > Hope somebody can give me an answer to this.. :-)
>>

Create a cached instance of a view from a linked server?

Hi
How do i manage to create a cache of a view from a linked server?
I have this view on a linked server (takes app. 15 secs) and i would like to
have a "local" cache of this view.
What would be the best way to handle this - to ensure both performance and
consistency?
Hope somebody can give me an answer to this.. :-)
Hi
Persist the data to a local table, and have a job that refreshes the table
on a regular basis.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"pnp" <pnp@.discussions.microsoft.com> wrote in message
news:ADA26A03-95C5-4737-9176-B4C114E15EA6@.microsoft.com...
> Hi
> How do i manage to create a cache of a view from a linked server?
> I have this view on a linked server (takes app. 15 secs) and i would like
> to
> have a "local" cache of this view.
> What would be the best way to handle this - to ensure both performance and
> consistency?
> Hope somebody can give me an answer to this.. :-)
|||Ok, thanks...
Thought that would be the best way - but didn't know if there were any
built-in features that allowed me to do this easier.
But thanks - i'll take this road then... :-)
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Persist the data to a local table, and have a job that refreshes the table
> on a regular basis.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "pnp" <pnp@.discussions.microsoft.com> wrote in message
> news:ADA26A03-95C5-4737-9176-B4C114E15EA6@.microsoft.com...
>
>
|||Replication is an option. But I wouldn't do it for just one table. Keep it simple.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"pnp" <pnp@.discussions.microsoft.com> wrote in message
news:4D5977DE-FB7A-4C19-8236-6E3BC3B70C8A@.microsoft.com...[vbcol=seagreen]
> Ok, thanks...
> Thought that would be the best way - but didn't know if there were any
> built-in features that allowed me to do this easier.
> But thanks - i'll take this road then... :-)
> "Mike Epprecht (SQL MVP)" wrote:

Create (U)SP in database on linked server

Hi,
I'm using a couple of linked servers.
I want to create a stored procedure on all of the linked servers in a database with a name which exists on all of the linked servers.
For executing SQL on all of the linked servers I'm using:

declare @.x int
declare @.dbname varchar(500)
declare @.SQL nvarchar(600)
set @.x = 1
create table #databases (ID int IDENTITY,name varchar(500))
insert #databases select instancelongname from instances
while @.x <= (select max(id) from #databases)
begin
select @.dbname = name from #databases where id = @.x
select @.SQL='blabla bla bla create PROCEDURE [dbo].[usp_xxxx]'
execute @.SQL
set @.x = @.x + 1
end
drop table #databases

Is it possible to use a create procedure in this construction?
Can anybody give me some help how to create a proper syntax for it?

Any help is kindly appreciated!Your code assumes that the database IDs are sequential and continuous, which they are probably not, so you are going to generate a lot of errors with this. But as far as creating the sprocs, your dynamic SQL will need to start with a USE statement to set the scope to your target database.

CR XI and VB6 prints some blank rows

I've been trying to move some more complex reports from VB DataReports to CR.

I have a CR report that is linked at runtime to a ADO recordset. Although the recordset has some NULL data, entire rows of information will come up blank. If I put in a null substitution in the fields, the substitution shows up. The correct number or rows are printed in the report, just some are blank.

Here some basic facts:
1. If I link to a VB6 DataReport, all data is displayed.
2. I setup a form with a listView to print out the exact recordset that I'm linking and all the data is present.
3. If I just run the query in SQL Enterprise Manager, it returns the correct data.
4. It's not random, it is always the same records (not matter how their selected) that show up blank.

Is there something that would tell CR to ignore a whole line if some or any of the values are null?Sorry...this appears to be a ADO issue...my mistake.

CPU Usage on AS 2005 Server

I have 5 cubes with 15 dimensions and one cube (like Virtual cube before) with 5 linked cubes.

My client is Excel 2003 Pivot table.

From Excel/Pivot table if I run a query which is taking long time. I press "Esc" key to cancel my query. However if I look into my Server "CPU Usage" even after canceling my query is very high. Looks it doesn't cancel my query on server. I am the only user using this server.

If I restart my Analysis Services 2005 the CPU Usage drops in 90 degrees to almost 0%.

Where is the problem any idea?

Thank you - Ashok

Looks like you have a classic case of "runaway query". It is always hard to get to the bottom of who sent the query and why the query is running that long.

Oten the one of the better recommendations for improving query performance and therefore improving responcivness of your cube is to desing aggregations. Aggregations are the key to Analysis Services performance.
Try and run Aggregation Design wizard and create aggregations for your cube. On top of that you can capture queries sent in the query log and then use Aggregation Manager sample application to desing aggregations supporting exact set of queries.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

In other words how to cancel/kill these "runaway query" without restarting server?

I do have aggregation setup. In SQL Server database we can kill any running query... can we do same in Analysis Services?

|||I had the same problem, if you run a query and you close the tool executing it then the query will still keep on running and slowing down the system. I don't think you can kill it. Maybe you can try to set the timeout much lower so that it will kill any query running longer than 30 seconds. I'm not sure if this will work but it should sure prevent the query of running longer than 30 seconds protecting the server from a resource eating *** query from hell.

|||

you can use the MSAS Activity Viewer tool and you can kill sessions.

|||

What is MSAS Activity Viewer tool ?

CPU Usage on AS 2005 Server

I have 5 cubes with 15 dimensions and one cube (like Virtual cube before) with 5 linked cubes.

My client is Excel 2003 Pivot table.

From Excel/Pivot table if I run a query which is taking long time. I press "Esc" key to cancel my query. However if I look into my Server "CPU Usage" even after canceling my query is very high. Looks it doesn't cancel my query on server. I am the only user using this server.

If I restart my Analysis Services 2005 the CPU Usage drops in 90 degrees to almost 0%.

Where is the problem any idea?

Thank you - Ashok

Looks like you have a classic case of "runaway query". It is always hard to get to the bottom of who sent the query and why the query is running that long.

Oten the one of the better recommendations for improving query performance and therefore improving responcivness of your cube is to desing aggregations. Aggregations are the key to Analysis Services performance.
Try and run Aggregation Design wizard and create aggregations for your cube. On top of that you can capture queries sent in the query log and then use Aggregation Manager sample application to desing aggregations supporting exact set of queries.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

In other words how to cancel/kill these "runaway query" without restarting server?

I do have aggregation setup. In SQL Server database we can kill any running query... can we do same in Analysis Services?

|||I had the same problem, if you run a query and you close the tool executing it then the query will still keep on running and slowing down the system. I don't think you can kill it. Maybe you can try to set the timeout much lower so that it will kill any query running longer than 30 seconds. I'm not sure if this will work but it should sure prevent the query of running longer than 30 seconds protecting the server from a resource eating *** query from hell.

|||

you can use the MSAS Activity Viewer tool and you can kill sessions.

|||

What is MSAS Activity Viewer tool ?

country city area ..... help

hi all :

i am working in HR system , the user table is linked to the counter table and the city and the area tables . i want to fill this tables with all the country and cities and its area all over the world , i think that i could find such data on the internet , can any one help me how to find this tables and the data . beside the nationality table .... any help please

I am not sure you will get it on single location. May be wikipedia help you.. http://en.wikipedia.org/wiki/List_of_countries|||

And...

http://en.wikipedia.org/wiki/List_of_cities

Counting Items in Categories

Ive got this monster which will give me a parent categoryName and the number of records linked to a child of that category, I want to use it for a directory where the list of categories has the number of records in brackets next to them. Note: a A listing will show up in each category count it is associated with

Like

Accommodation (10)
Real Estate(30)
Automotive(2)
Education(1)...

Select trade_category.iCategory_Name,Listing_category.iPa rentID,count(Listing_category.iCategoryID) as num
from Listing_category,trade_category Where Listing_category.iParentID = trade_category.iCategoryID Group by
Listing_category.iParentID,trade_category.iCategor y_Name
Union ALL
Select Freecategory.sName,Listing_category.iParentID,coun t(Listing_category.iCategoryID) as num
from Listing_category,Freecategory Where Listing_category.iParentID = Freecategory.iFreeID Group by
Listing_category.iParentID,Freecategory.sName

Which Produces

Real Estate 12401 12
Extreme Sports 3 4

I would Like to get the same query to produce a list of all the empty records too.
so
ID Count
Accommodation 6112 0
Real Estate 12401 12
retail 12402 0
Extreme Sports 3 4
Cycling 5 0There is no such concept of an 'empty record'. If you were to describe to me an 'empty record', what would it be? In situations similar to what you describe, a record can have one of the following characteristics:

- Contain null values for one or more of its fields
- Not be returned with respect to some given criteria.

But there is no mention of an 'empty record' in relational theory or in any Database implementation. Looking at your query, I can't think of what it is you're actually trying to achieve, except in the case where a parent category may have no children, you need to return a result set similar to the following:

{ParentID, ChildCount}
ParentA, 0

When viewed in this way, the problem becomes a trivial LEFT JOIN query that will return all rows from set A irrespective of the contents of set B. In your example however, instead of returning the rows from Set B you will just return a count of the rows.

Select
SetA.columnA,
count(SetB.columnA)
from
SetA

left outer join SetB
on SetA.ColumnA = SetB.ColumnB

Remember: Simplification should be the goal of every developer. A paraphrased quote I once heard said: Perfection is reached not when you can no longer add to it, but when you can no longer take anything away.|||Ive found a short term solution will look at speeding it up when I have some spare time, and I have the live version working so it makes a bit more sense.

Start of December