Multiple table query based on an argument 1

[FatalExceptionError]

Hello

I am trying to design a query that selects data from multiple tables. I have run into a snag as one of the possible conditions of the select statement pulls from a seperate table.

Here is an example of what I am doing. Assume the variables have already been declared.

Select ID, Name, date, batch,type,
(select batchdate from anotherDB where batchnum = batch)
from someDB
where
(type = 'A' or type ='B') 
and (ID like @someID) 
and (date > @begin_date and date < @end_date)
and (batchnum like @batch_number)

Of course you can't do this because you get an error but is there a way I can structure the query so that I can accomplish this.

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[vongrunt]

The only possible error I see here is "subquery returns more than 1 value".... assuming that "someDB" actually means "someTable".

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[FatalExceptionError]

Yes someDB is another table.

I have it running from a procedure and the error comes at

(batchnum like @batch_number)

Invalid column name 'batchnum'.

P.S. Sorry for not posting error earlier.

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[vongrunt]

> Invalid column name 'batchnum'.

And batchnum is supposed to be in someDB or anotherDB?

Personally I'd check table structure first - maybe column does not exist or is named differently.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[FatalExceptionError]

it is in anotherDB which is not the DB I am querying on. Basically what I am trying to do is possibly filter results based on a number that is another database. The entire query is dynamic.

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[vongrunt]

Another database... then use otherdbname.owner.tablename notation.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[FatalExceptionError]

Can I use an alias (I think that is what it is called)

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[SQLBill]

I don't think you can. The problem is that nowhere in your script are you JOINing to the other table. The only places the other table is used is the SUBSELECT and the WHERE. One problem is that SQL Server 'processes' the WHERE before the SELECT. (It has to get all the matching data (WHERE) before it displays it (SELECT)). So, in effect, SQL Server thinks this is all being done from ONE table.

Look at it this way...you told SQL Server you want this from someDB...

Select ID, Name, date, batch,type,
from someDB
where
(type = 'A' or type ='B') 
and (ID like @someID) 
and (date > @begin_date and date < @end_date)
and (batchnum like @batch_number)

AND this from anotherDB

(select batchdate from anotherDB where batchnum = batch)

and then display both results.

So in the first part, you use batchnum, but it doesn't exist in the table.

What does the schema of the two tables look like? Maybe you would be better off doing a JOIN.

-SQLBill



Posting advice: FAQ481-4875

 

[FatalExceptionError]

It is a one to one relationship with batchnum/batch being the foreign key.

If I were to join would it join just that date from the other table or the entire row?

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[SQLBill]

See if this gives you what you need:

Select sdb.ID, sdb.Name, sdb.date, sdb.batch,sdb.type,
adb.batchdate
from someDB sdb
 left outer join anotherDB adb
   on sdb.batch = adb.batchnum
where
(type = 'A' or type ='B') 
and (sdb.ID like @someID) 
and (sdb.date > @begin_date and date < @end_date)
and (adb.batchnum like @batch_number)

Since you didn't provide the schema for the tables, I'm guessing at what columns belong to which table. If I got any wrong, just change the alias.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLSister]

and if you are still getting an error at @batchnum consider what data type the variable is vice what datatype the field has. You nmight be sending a varchar when it is looking for an integer.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[FatalExceptionError]

Sorry it took so long for me to get back something else came up that required my attention. The left join worked. Thank you guys so much for your help.

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't