select query across multiple tables

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

SQL Server 2000 Forums

Transact-SQL (2000)

select query across multiple tables

Author

Topic

krisis
Starting Member

6 Posts

Posted - 2005-02-03 : 22:46:44

I wish to do a select query that grabs the count(data) from a table, and also grabs the data details from another table and links them together. Then order by the count. The problem is that I can't find the right query that will also return the rows whereby there is no matching data in the count table (i.e. count=0).

i.e.
table_COUNT has columns DATA, TIME
table_INFO has columns DATA, USER, ORIGIN, CATEGORY

I want to be able to
SELECT * FROM table_INFO where data LIKE '%websitename%'
and also to grab the count of how many times data is in table_COUNT, so:
SELECT count(table_COUNT.data) FROM table_COUNT, table_INFO WHERE table_COUNT.data = table_INFO.data
and in the cases where there is no matching data in table_COUNT, to return a value of 0.

So results I want would look something like:

count, data, user, origin, category
23, websitenameABC, john, 127.0.0.1, finance
16, websitenameMND, fred, 127.0.0.1, health
3, websitenameFFD, paul, 127.0.0.1, finance
0, websitenameXYZ, peter, 127.0.0.1, computers

How can I do this?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-02-03 : 22:51:51

MAybe something like:

SELECT
	isnull(d.ct,0) [count],
	i.data,
	i.user,
	i.origin,
	i.category
FROM
	table_INFO i
LEFT OUTER JOIN 
(
	SELECT
		data, 
		count(data) ct
	FROM
		table_COUNT
        GROUP BY 
                data
) d ON d.data = i.data
WHERE
	i.data LIKE 'websitename%'
ORDER BY 
        isnull(d.ct,0) DESC

krisis
Starting Member

6 Posts

Posted - 2005-02-03 : 23:00:46

I get the error:

Column 'table_COUNT.data' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-02-03 : 23:02:12

Oops, I forgot the GROUP BY in the derived query.

Copy the code again and try it again.

krisis
Starting Member

6 Posts

Posted - 2005-02-03 : 23:05:53

Works great!

Thanks for the very prompt response.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-02-03 : 23:06:37

Your welcome

krisis
Starting Member

6 Posts

Posted - 2005-03-21 : 00:02:22

Is it also possible to return (in the same query) the TIME field from table_COUNT corresponding to the most recent datetime that DATA was entered?

i.e. it should basically be the TIME result equal to
SELECT TOP 1 time FROM table_COUNT WHERE data = 'datestuff' ORDER BY time DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources