SQL Query Help to Select a table based on ID

[carltonp]

Hello Community,

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

Cheers

Carlton

[yosiasz]

Is this for microsoft sql server

[dcristut]

Sorry but I don't get the idea of tables "coming/arriving" into "folders" ..
@yosiasz is right: is that MS SQL Server?

[carltonp]

Hi guys, thanks for reaching out.

Yes, this is for MS SQL Server.

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

[harishgg1]

hi tables "coming/arriving" into folders

you can use

folder create date ..

does this help ?

[carltonp]

Hi Harish,

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

**.

That would be great.

Really appreciate your feedback

[carltonp]

Hi Guys,

I think the answer might be 'for loop' or something like that

[carltonp]

Hi guys,

When I say folder I mean database

[harishgg1]

hi

will this help ????

old style programming !!!! i

if you want SQL i can try ,,,

declare @rowcnt1 int = 0
declare @rowcnt2 int = 0
declare @rowcnt3 int = 0

select @rowcnt1 = count(*) from table1 where LOAD_ID = 1111

if @rowcnt1 = 0
select @rowcnt2 = count(*) from table2 where LOAD_ID = 1122

if @rowcnt1 <> 0
select * from table1 where LOAD_ID = 1111

if @rowcnt2 = 0
select @rowcnt3= count(*) from table3 where LOAD_ID = 1133

if @rowcnt2 <> 0
select * from table2 where LOAD_ID = 1122

if @rowcnt3 <> 0
select * from table3 where LOAD_ID = 1133

[carltonp]

harishgg1,

I will give this a try in about an hour and let you know how I get on.

In the meantime thanks for this.. I'm very excited to see it work

[Wim_Leys]

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

Dynamically creating SELECT script

DECLARE @load_id int;
DECLARE @SQL VARCHAR(1000);
DECLARE @table_name SYSNAME;

SELECT @load_id = load_id FROM dbo.Loaded

SELECT @table_name = table_name FROM dbo.map_load_id_2_table WHERE load_id = @load_id

-- Warning! Dirty dynamic SQL ahead:
SET @SQL = '
SELECT * 
FROM dbo.' + @table_name

--EXEC (@sql)
print @SQL
---- output:
--SELECT * 
--FROM dbo.TableTwo

-- Dirty dynamic SQL ends here.

Clean up.

DROP TABLE dbo.map_load_id_2_table;
DROP TABLE dbo.Loaded;
GO

[carltonp]

Wow... Wim_leys I'm looking forward to trying out this solution

[carltonp]

Going attempt this solutions today.

Thanks guys.

I will keep you posted.

[carltonp]

harishgg1

This worked perfectly.

Thank you very much

Thank you to Wim_leys