July 6, 2009 at 2:40 pm
I have the following three tables (simplified for this example). I want to select the records out of the Variables table if they are listed as a Variable in the Mods table. I should end up with FormID/Name of 1/Var1 and 2/Var2. Instead, I am getting all three because the last record 2/Var1 matches both criteria separately, but I need to specify that they must match all criteria as a group.
[font="Courier New"]Forms Table
ID Name
1 Form1
2 Form2
Variables Table
FormID NameValue
1 Var1A
2 Var2B
2 Var1C
Mods Table
ObjNameTypeFormName
Var1VariableForm1
Var2VariableForm2
Str1StringForm1
Str1StringForm2[/font]
I can see why the following fails, but how can I do this without using joins. The tool I am using only allows me to specify the table and the where clause. It selects the entire row and creates insert statements for me.
from Variables
where Name IN
(
select ObjName
from Mods
where Type = 'Variable'
)
and FormID IN
(
select ID
from Forms
where Name IN
(
select FormName
from Mods
where Type = 'Variable'
)
)
Thanks...
July 6, 2009 at 9:17 pm
Use INNER JOIN instead of IN.
_____________
Code for TallyGenerator
July 7, 2009 at 7:38 am
I can't use a join. The utility I am calling expects one table name to be passed in the from parameter, and all the logic needs to be included in the where parameter.
July 7, 2009 at 8:08 am
begin tran
create table Forms(ID int,Name1 varchar(30))
insert into forms values(1,'Form1')
insert into forms values(2,'Form2')
create table variables(FormID int,Name varchar(30),Value varchar(30))
insert into variables values(1,'Var1','A')
insert into variables values(2,'Var2','B')
insert into variables values(2,'Var1','C')
create table Mods(ObjName varchar(30),Type varchar(30),FormName varchar(30))
insert into variables values('Var1','Variable','Form1')
insert into variables values('Var2','Variable','Form2')
insert into variables values('Str1','String','Form1')
insert into variables values('Str1','String','Form2')
--rollback tran
--from Variables
--where Name IN
-- (
-- select ObjName
-- from Mods
-- where Type = 'Variable'
-- )
--and FormID IN
-- (
-- select ID
-- from Forms
-- where Name IN
-- (
-- select FormName
-- from Mods
-- where Type = 'Variable'
-- )
-- )
OK NOW ITS FINE.........
July 7, 2009 at 8:20 am
seems to me you might be better creating a view on your sql server and then let your application read from the view
that way you get some decent sql that people can debug and will perform....
MVDBA
July 7, 2009 at 9:05 am
mjarsaniya,
I don't see what you did, other than adding code to create the tables with data. I assume you are implying that I need to do this whenever I post sample tables?
michael,
I think a view will work. I will give this a try.
Thanks, mpv
July 7, 2009 at 11:51 am
The View works. My only concern is that two of my tables are in one DB and the third is in a different DB. Are there problems creating a View over two DBs or is this typical?
July 7, 2009 at 12:47 pm
mpv (7/7/2009)
mjarsaniya,I don't see what you did, other than adding code to create the tables with data. I assume you are implying that I need to do this whenever I post sample tables?
Yes. For more on this, please read the first article I reference below in my signature block regarding asking for help.
July 7, 2009 at 2:15 pm
mpv (7/7/2009)
The View works. My only concern is that two of my tables are in one DB and the third is in a different DB. Are there problems creating a View over two DBs or is this typical?
This is fairly typical - shouldn't be any issues.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply