Need syntax help in join from two DB

Hi

I have two databases on my server, I need a simple query with one join between one table from each database.
I looked in the help of FROM clause and found the Argument "table_source" where it explains this :
"If the table or view exists in another database on the same computer running Microsoft SQL Server, use a fully qualified name in the form database.owner.object_name".

Can someone please help me fill the variants ??
My DB name is "Forum" the owner is "DBRND\Administrator" and the table name is "TblUsers", so I tried to write in the FROM clause :
"FROM Forum.DBRND\Administrator.TblUsers" but it doesn't work... so anyone have any idea how should it be ?

Thanks,

Inon.select a.col, b.col
from db1..table1 a, db2..table2 b
where a.colX = b.colX
etc...|||Your object owner is DBRND\Administrator?

Look at the list of tables in Enterprise Manager. They are most likely owned by dbo:

select * FROM Forum.dbo.TblUsers

AND PLEASE DON'T LINK TABLES IN THE WHERE CLAUSE! (My pet peeve...)

select a.col, b.col
from db1..table1 a
inner join db2..table2 b on a.colX = b.colX

Code like a pro!|||(My pet peeve...)Oh, oh! I had a peeve once! Everybody kept feeding it, until it almost ate me.

I've gotten a wee bit jaded since then, but I suspect that you'd noticed.

-PatP|||Thanks A LOT!

Sorry about pushing this thread up... :)

BTW, is joining from two databases on the same server is recommended ? Because I have an option to combine the two databases, so how bad is it (if at all) to leave it like it is.

Inon.

Need help writing search query

I am not very familiar with the syntax of MS SQL and I am trying to write a stored procedure which would do a search and return matching records.
This is what I need to achieve:
for instance I create a form with 4 text fields
- First Name
- Last Name
- Employee ID
- Date

I am interested in writing a stored procedure that would run a select query based on the input in the text fields
e.g.
- if the user enters First Name and the Last Name (leaving Employee ID and Date fields blank) query should be something like
select * from Employee where FirstName like @.FirstName and LastName like @.LastName
- or if the user enters only the Employee ID stored procedure should run a query similar to
select * from Employee where EmployeeID like @.EmployeeIDselect *
from Employee
where (FirstName like @.FirstName and LastName like @.LastName)
or
(EmployeeID like @.EmployeeID)|||Thanks for the your time blindman .. that was absolutely brilliant, shows you know your SQL :cool:
This query would sure work for the example I posted, but I was just wondering if I can give user more flexibility and allow him to enter lets say [partial first name (some string with wildcard characters) and partial ID] or [partial first name, last name and ID] or some such wierd combination. The query should adapt to the input and return result accordingly.
What is the best way to go about doing this, again thanks for any help I can get.
Thank you.
Have a great day.|||Yes.

You will need to add more complexity to your WHERE clause to accomplish the logic you want.

You will also need to use the LIKE operator if you want to allow wildcards.

You will also need to expect this query not to run very fast, if you include lots of logical operators and LIKE comparisons in your criteria...|||Since this is in a stored procedure you might use dynamic SQL to create the query on the fly and then execute it.

eg.

create x @.empid int, ...

declare @.sql varchar(200)

set @.sql = 'select * from Employee where '

if empid is not null set @.sql = @.sql & 'EmployeeID =' & @.empid

... etc etc

exec (@.sql)

...|||Thanks ejustuss and blindman, you guys were a tremendous help.
Since I am not very comfortable writing stored procedures this was my solution to the problem .. nothing ingenious but hey as long it works thats all I care about :D.
So I wrote this insanely strict stored procedure which would except user to input all the parameters (first name, last name, Employee ID, Date ... everything) and wrote a query something similar to this:
select * from Employee where FirstName like @.FirstName and LastName like @.LastName and EmployeeID like @.EmployeeID and ....
Since my stored procedure is not giving any flexibility to the user, I added flexibility on the client side in the web form code by replacing all fields left blank by the user with wildcard '%'. So if a user wanted to search by employee's first name, he would just enter the first name leaving other field blanks. These blank fields will be replaced with % and passed to the stored procedure.|||Not sure how effecient this is :

SELECT * FROM Employee
WHERE EmployeeID LIKE ISNULL('%' + @.EmployeeID + '%', EmployeeID)
AND FirstName LIKE ISNULL('%' + @.FirstName + '%', FirstName)
AND LAstName LIKE ISNULL('%' + @.LastName + '%', LastName)
AND DATEDIFF(Day, ISNULL(@.Date, Date), Date) = 0;|||Hey afx thanks for posting your version of the solution. I am sorry but I really did not understand the code you posted. Though your input was definetely useful since using ISNULL is by far a way better more efficient option :cool:
This is how I would use ISNULL

SELECT * FROM Employee
WHERE EmployeeID LIKE ISNULL( @.EmployeeID, '%')
AND FirstName LIKE ISNULL( @.FirstName, '%')
AND LAstName LIKE ISNULL( @.LastName, '%')

Thank you again for the input and effort :) :)|||Hi all,

I need help on building query statement. below is my table called inventory.

idx fabidx coloridx qty isReserved
1 1 1 15 Y
2 1 2 20
3 1 1 10
4 1 1 25 Y
5 1 2 23 Y
6 1 3 26
This is the output that i'm expecting.
fabidx coloridx qty isReserved
1 1 50 2
1 2 43 1
1 3 26 0
I need to get all distinct fabidx and coloridx, i need to get the sum of the distinct fabidx and coloridx, and i need to get the count of "Y" in the isReserved column of the distinct fabidx and coloridx.

Please help. Thanks.|||A simple aggregation query. Look up aggregation function in Books Online, and then post this as a new thread if you still need help.

Need help with TSQL syntax and JOIN.

Hey all,
I'm having problems with a TSQL query, and I know someone out there can
help.
Note the following:
--
CREATE TABLE #temp
( ID int,
ParentID int,
Text varchar(50)
)
INSERT INTO #temp (ID, ParentID, Text) VALUES (1,null,'Top')
INSERT INTO #temp (ID, ParentID, Text) VALUES (2,1,'Middle')
INSERT INTO #temp (ID, ParentID, Text) VALUES (3,2,'Bottom')
SELECT t1.Text + ':' + t2.Text + ':' + t3.Text
FROM #temp t1
INNER JOIN #temp t2 ON t1.ID = t2.ParentID
INNER JOIN #temp t3 ON t2.ID = t3.ParentID
DROP TABLE #temp
--
This temp table allows me to define a series of Text values, and change them
together within a Parent-Child relationship. The select returns
"Top:Middle:Bottom", which is exactly what I want.
But, let's pretend that I have 5 more levels. to get the select statement
to work, I'd have to have 5 more inner joins. Let's say I had X more
levels -- how am I to know how many levels to continue to add to my SQL
statement?
Hopefully you can see the problem.
I want to write a SELECT statement that accomplishes the same thing (i.e.
concatenating the TEXT together for X number of parent-child relationships),
but without having to explicitly account for every possible level.
Does anyone know how I can accomplish this?
Thank you very much for your help!
WadeFigured out a way:
use tempdb
CREATE TABLE temptbl
( ID int,
ParentID int,
Text varchar(50)
)
GO
CREATE FUNCTION f_categoryPath
( @.iID int,
@.sText varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @.sRetval varchar(8000),
@.sSlash char(1),
@.t_id int,
@.t_parentid int,
@.t_text varchar(50)
SET @.sSlash = ''
SET @.sRetval = ''
SELECT @.t_id = ID,
@.t_parentid = ParentID,
@.t_text = Text
FROM temptbl
WHERE ID = @.iID
set @.sRetVal = @.sSlash + @.t_text + @.sText
if @.t_parentid is not null
begin
set @.sRetval = dbo.f_categoryPath(@.t_parentid,@.sRetVal)
end
return @.sRetval
END
GO
INSERT INTO temptbl (ID, ParentID, Text) VALUES (1,null,'Top')
INSERT INTO temptbl (ID, ParentID, Text) VALUES (2,1,'Middle')
INSERT INTO temptbl (ID, ParentID, Text) VALUES (3,2,'Bottom1')
INSERT INTO temptbl (ID, ParentID, Text) VALUES (4,3,'Bottom2')
INSERT INTO temptbl (ID, ParentID, Text) VALUES (5,4,'Bottom3')
INSERT INTO temptbl (ID, ParentID, Text) VALUES (6,5,'Bottom4')
INSERT INTO temptbl (ID, ParentID, Text) VALUES (7,6,'Bottom5')
SELECT dbo.f_categoryPath(7,'')
DROP TABLE temptbl
DROP FUNCTION f_categoryPath
--
thanks anyway!
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:epq8QZpIGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hey all,
> I'm having problems with a TSQL query, and I know someone out there can
> help.
> Note the following:
> --
> CREATE TABLE #temp
> ( ID int,
> ParentID int,
> Text varchar(50)
> )
> INSERT INTO #temp (ID, ParentID, Text) VALUES (1,null,'Top')
> INSERT INTO #temp (ID, ParentID, Text) VALUES (2,1,'Middle')
> INSERT INTO #temp (ID, ParentID, Text) VALUES (3,2,'Bottom')
> SELECT t1.Text + ':' + t2.Text + ':' + t3.Text
> FROM #temp t1
> INNER JOIN #temp t2 ON t1.ID = t2.ParentID
> INNER JOIN #temp t3 ON t2.ID = t3.ParentID
> DROP TABLE #temp
> --
> This temp table allows me to define a series of Text values, and change
> them together within a Parent-Child relationship. The select returns
> "Top:Middle:Bottom", which is exactly what I want.
> But, let's pretend that I have 5 more levels. to get the select statement
> to work, I'd have to have 5 more inner joins. Let's say I had X more
> levels -- how am I to know how many levels to continue to add to my SQL
> statement?
> Hopefully you can see the problem.
> I want to write a SELECT statement that accomplishes the same thing (i.e.
> concatenating the TEXT together for X number of parent-child
> relationships), but without having to explicitly account for every
> possible level.
> Does anyone know how I can accomplish this?
> Thank you very much for your help!
> Wade
>|||Have you gotten a copy of TREES & HIERARCHIES IN SQL yet? There are
several other ways to do this without procedural code at all.