Can someone help me compile a query that will select a table/file based on item in a row.
For example, I have attached three tables. These tables arrive in folder.
As you can see TableOne has a load_id of 1111, TableTwo has a load_id of 1122, and TableThree has a load_id of 1133.
I'm trying to write a query that will select a table in the folder based on the value of the load_id number. So, if TableThree arrived in the folder before before TableTwo I 'don't' want my query to select that table. Likewise, if table TableTwo arrived in the folder before before TableOne I don't want my query to select that table.
The best way I think I can explain it is like this:
select * from TableThree where LOAD_ID = 1133, but only if in a previous select statement I checked for LOAD_ID from a previous table(TableTwo in this case) and successfully found LOAD_ID = 1122.
I hope want I've describe makes sense.
Please let me know if you need further clarification
As regards, tables "coming/arriving" into "folders", basically TableOne etc.. is a .csv file that is copied into a folder - I hope that makes it a little clearer
Thanks again for getting in touch. I think too much emphasis is placed on the statement " tables "coming/arriving" into folders.
If the community can focus on helping write a sql query that will **
select * from TableThree where LOAD_ID = 1133, but only if in a previous select statement I checked for LOAD_ID from a previous table(TableTwo in this case) and successfully found LOAD_ID = 1122
Dirty solution using dynamic SQL.
harishgg1's solution is cleaner.
CREATE TABLE dbo.Loaded(
id int identity(1, 1) NOT NULL,
blabla CHAR(20),
load_id int NOT NULL,
CONSTRAINT PK_Loaded PRIMARY KEY (id)
)
GO
INSERT into dbo.Loaded(blabla, load_id) VALUES
('Hello world!', 1122)
GO
CREATE TABLE dbo.map_load_id_2_table(
id int identity(1, 1) NOT NULL,
load_id int NOT NULL,
table_name sysname NOT NULL,
CONSTRAINT PK_map_load_id_2_table PRIMARY KEY (id)
)
GO
INSERT INTO dbo.map_load_id_2_table(load_id, table_name) VALUES
(1111, 'TableOne'),
(1122, 'TableTwo'),
(1133, 'TableThree');
GO