get result set from two tables without compare

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

get result set from two tables without compare

Author

Topic

seany
Starting Member

15 Posts

Posted - 2008-01-26 : 15:15:18

Hi there,

Can someone please help me figure out how I can write a select statement that returns a row from one table and the count of records from the second table using the row name from the first table?

In other words:
First table has only one column "name" and that table name is called "namelist"
Second table has multiple columns, and the table is called "name"
so, what i want to do is something like:
select name from namelist and select count(*) from [name]

results should be:
sean,0
bob,1
tom,5
etc...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-26 : 16:24:20

sounds like all you need is the [name] table:
select name, count(*) from [name] group by name

EDIT:
maybe I misunderstood the question...Is there a [name] column in the [name] table? If not is there anything to correlates a row from one table to a row in the [namelist] table?

anyway here is some syntax to join the two tables assuming there is a [name] column in both tables:


select nl.[name]
	,count(*) as nameCount
from	[namelist] nl
inner join [name] n
	on n.[name] = nl.[name]
group by nl.[name]

Be One with the Optimizer
TG

seany
Starting Member

15 Posts

Posted - 2008-01-26 : 16:46:30

thanks for the reply, no there is no primary or foreign key associated with the two tables.

Data looks like:

Table 1 called names has 3 rows with the row called namelist

namelist
--------
a
b
c

table 2 with name a contains 0 records
table 3 with name b contains 3 records
table 4 with name c contains 2 records

output should be:

a,0
b,3
c,2

seany
Starting Member

15 Posts

Posted - 2008-01-26 : 16:48:46

Just to clarify,

I only know about the names of table 2, 3, and 4 from the contents that is in table namelist. So I somehow need to grab the names first before running a count on each one of them. I really hope this makes sense.

Thanks, again.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-26 : 17:59:03

quote:
I really hope this makes sense

probably does but I'm slow

why don't you present the problem as DDL statements because you seem to be mis-using the term "Rows".
"with the row called namelist" but your diagram describes this as a Column. And when you say "table 2 has name a" I'm not sure if that is the name of the table or if the table called "table2" has a column called [name] or what.

ie:


create table [names] ( namelist varchar(10))
insert [names] (namelist) values ('a')
insert [names] (namelist) values ('b')
insert [names] (namelist) values ('c')

create table ????

Be One with the Optimizer
TG

seany
Starting Member

15 Posts

Posted - 2008-01-26 : 20:41:30

Ok, I will begin over, sorry for this.

Let's say that I have a table called "mytable" that consists of:

mytable -- this is the table name
namelist - this is the column name
--------
a
b
c

So this means that there is only one column in the table with 3 rows (a,b,c)

Each one of the values of the rows/records in the table "mytable" has its own table with the row value as its name, so

a - this is the table name (with no rows/records)
column1 - this is the column name
-------

b - this is the table name (with 1 row/records)
column1 - this is the column name
-------
duck

c - this is the table name (with 3 row/records)
column1 - this is the column name
-------
duck
duck
goose

What i am trying to do is run a select statement so that I can first determine the value of the row in table "mytable" row "mylist", and then once I have that information, run a count to see if there are any records in each one of the other tables

result:

a,0
b,1
c,3

I need to run a statement through a csharp application hence the need for a select statement that does it all in one shot.

I am hoping that this clarifies some stuff a bit

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-26 : 21:16:00

ugh - ok how about this:


--counts of any tables whose name matches what is in myTable
select t.nameList
	,i.rowcnt 
from	myTable t
left outer join sysindexes i
		on i.id = object_id(t.nameList)
where i.indid < 2


EDIT:

--all table in database
select t.table_name
	,i.rowcnt 
from	information_schema.tables t
left outer join sysindexes i
		on i.id = object_id(t.table_name)
where i.indid < 2

Be One with the Optimizer
TG

seany
Starting Member

15 Posts

Posted - 2008-01-26 : 21:44:15

Ok, the first part works fantastic. Is there a way to return a zero if nothing gets back? TG, I really appreciate that you are assisting me with this. Thank you so much for your time.

seany
Starting Member

15 Posts

Posted - 2008-01-26 : 21:50:33

TG, I just realized that it is returning only a record if it has at least one row. I still want the records to show eventhough they don't contain records

a,0
b,1
c,3

The script i used was:

select t.nameList
,i.rowcnt
from myTable t
left outer join sysindexes i
on i.id = object_id(t.nameList)
where i.indid < 2

seany
Starting Member

15 Posts

Posted - 2008-01-26 : 22:19:22

Ok, so I took the where clause out and now I get everything back which is fantastic. The only thing is that if there is nothing there, its empty and I would like it to return a zero. Is that possible?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-26 : 22:25:09

Yes, just change "i.rowcnt" to "isNull(i.rowcnt,0)"

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-26 : 22:28:18

sorry, I should have put that "where" clause as part of the JOIN criteria since it is an outer join. You should probably leave it in so that if you have additional indexes on your tables the rows won't show up more than once.


select t.nameList
	,isNull(i.rowcnt, 0) as rowCount
from	myTable t
left outer join sysindexes i
		on i.id = object_id(t.nameList)
		and i.indid < 2

Be One with the Optimizer
TG

seany
Starting Member

15 Posts

Posted - 2008-01-26 : 22:59:00

Thank you so very much TG. You were a great help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources