How do I query one MSSQL 2005 database for a value to use in query in another one?

I've got two tables.  I need to query the content_meta_field with a meta_type_id and content_id (both of which I'll feed into the query) and get the contents of the meta_value field.  Then I need to take that value and query the content table with the meta_value and the content_id to get the content_titlel and content_html values for use in a site I'm building.

I've included the schema for both tables below and could use some help with the query.  I probably have one somewhere but am under a tight deadline with too little time and too little to do.

Thanks for the help in advance...
Content_meta_tbl
==============
meta_type_id            [int]	pk
content_id	  [int]	pk with content table
content_language      [int]	pk with content table
meta_value	   [nvarchar(2000)]
active	          [int]
row_id	          [varchar(30)]
row_changed_date  [datetime]
 
 
 
content
===================================
content_id                [int] pk
content_title	          [nvarchar(200)]
content_html	          [ntext]
content_status	          [nvarchar(1)]
content_meta_data         [ntext
content_language          [int] pk
date_created	          [datetime]
last_edit_lname	          [nvarchar(50)]
last_edit_fname	          [nvarchar(50)]
last_edit_comment         [nvarchar(255)]
last_edit_date            [datetime]
user_id                   [int]
folder_id                 [int]
inherit_permissions       [int]
inherit_permissions_from  [int]
inherit_xml               [int]
inherit_xml_from          [int]
private_content           [int]
content_teaser            [ntext]
published                 [int]
go_live                   [datetime]
content_text              [ntext]
end_date                  [datetime]
content_type              [int]
approval_method           [int]
searchable                [int]
end_date_action           [int]
alias_id                  [int]
contentfulltextkey        [varchar(30)]
asset_id	          [varchar(50)]
asset_version	          [varchar(50)]
xml_config_id	          [int]
template_id	          [int]
last_replicated_date	  [datetime]
image                     [nvarchar(550)]
private                   [bit]
row_changed_date          [datetime]

Open in new window

saabStoryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dublingillsCommented:
To query one database from another you simply add the database name in front of the schema and table name, i.e.

database.schema.tablename
0
dublingillsCommented:
To avoid any confusion with the word schema, in most databases I;ve encountered, dbo is the schema so you would use

databaseName.dbo.tableName
0
TeggertCommented:
saabStory, If I understand correctly, you have two tables in the same database and need to join those tables to get combined data out based upon the two variables you provide. Forgive me if I'm missing it, but here is a SQL statement that may answer your question. If it does not produce the desired results, I just need to know what your meta_value in the content_meta_tbl matches to in the content table.

select cmt.content_title, cmt.content_html, c.meta_value
from content_meta_tbl cmt
	inner join content c on c.content_id = cmt.content_id
where cmt.meta_type_id = @meta_type_id AND cmt.content_id = @content_id

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

TeggertCommented:
Note in the above @meta_type_id and @content_id should be your supplied values, passed into the stored procedure and declared above the query, or inserted into the query you are running by some other fashion.
0
saabStoryAuthor Commented:
Sorry for the long delay getting back - couldn't get back on after I got home.  As to the query - I see that I wasn't totally clear - comes from being in a hurry - my apologies.  Perhaps a bit more explanation.
All of the content for this site is held in the content table - everything.  In my case I need to get a content block  and a banner to display on the page - both of these records are held in the content table.  The relationship between the two is defined in the content_meta_tbl table.  
So when I query the content_meta_table, I'll query it with the meta_type_id (which I know is 130) and the content_id (5588 for example).  That gives me the other id (4136 for example - the one for the header) associated with the content_id.  Then I need to take both ids and query the content table to pull back the records for both content_ids - 5588 and 4136.
I know I can do it two or 3 separate queries but I was hoping there might be a better way to do it in one.
Thanks.
 
0
TeggertCommented:
Sounds like a subquery situation. If this is way off base, can you maybe give the individual queries so I can take a look and see if we can combine them?
select c.*
from content c
where c.content_id in (select content_id from content_meta_tbl cmt where
cmt.meta_type_id = 130 AND cmt.content_id = 5588)

Open in new window

0
saabStoryAuthor Commented:
Sorry - I'm not explaining this well.  I'll try the individual queries like you suggested.
Since we know the meta typ id (130) and the id of the content block (5538) we first need to get the id of the banner associated with the content block.  I've added in everything I would do in the real query - such a getting the folder id for another query, checking to make sure the content was active and whether or not it's marked as private.
 

Since we know the meta typ id (130) and the id of the content block (5538) we first need to get the id of the banner associated with the content block.
--------------------------------------------------------------------
SELECT meta_value
FROM content_meta_tbl as cmt
WHERE (meta_type_id = 130) AND (content_id = 5588) AND (active = 1)
 
Then we query the content table for the contents of id #5588 (the content block) and the value returned from the query above (#4136 for example)
--------------------------------------------------------------------
SELECT content_id, content_title, content_html, folder_id, private
FROM [content] AS c
WHERE private = 0) AND (content_id = 5588) OR (content_id = 4136)

Open in new window

0
TeggertCommented:
Ah, I see. Well to combine them, basically take the part where you put the returned value from the first query, and run it as a subquery inside of the where statement as follows:
SELECT content_id, content_title, content_html, folder_id, private
FROM [content] AS c
WHERE private = 0 
	AND (
		(content_id = 5588) 
		OR 
		(content_id = 
			(
				SELECT TOP 1 meta_value
				FROM content_meta_tbl as cmt
				WHERE (meta_type_id = 130) 
				AND (content_id = 5588) 
				AND (active = 1)
		
			)
		)
	    )

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
saabStoryAuthor Commented:
Perfect!  Thanks so much for sticking with this.  Have a great weekend.
0
TeggertCommented:
You, too. Have a great holiday weekend. Glad I could be of assistance.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.