Returning a list of values as differnet rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Returning a list of values as differnet rows

Author

Topic

saketg
Starting Member

8 Posts

Posted - 2010-08-22 : 11:20:00

Hi All,

I am a business analyst with scratchy development skills so you will have to bear with me if I do not explain clearly. I have two tables with several million records. They have a one to many relationship. To get required information, I wrote a query to join them and filter data based on a specified value for a column in child table.

I used to filter on at most five values and so I used to pass them as a list of values in WHERE clause specifying FIELD IN ('value1', 'value2', 'value3'). Basically, hardcoding the values. This time I got over 50000 values for analysis. I used the same skills. I dumped them all in a text file and then somehow managed to put them in the same way. After reading several articles I could optimize the query a little bit and it executes in 15 or 20 minutes. Is there an alternate to achieve this?

Now this works well to get details for values I could find. But, this way does not identify values that were not in FIELD. For smaller sets (upto 5), I used to mark them myself. Now, for 50000 I cannot do this. Is there a way to identify values in my list that did not match anything in two big tables?

Constraints on my side:
1. I have only read access to database (SELECT only)
2. I cannot create another temp table on main database
3. I cannot use ODBC to link my tables to another database
4. Creating Cursor is not allowed
5. I can only use SQL Client (no programming language)

For clarity of my desired result, let us assume that we had another table with these values as records. Now, I could have used a LEFT OUTER JOIN on this table and two big tables to get desired data. Is there a way I can get this in a single SELECT query?

Presently, I could think only an inefficient two step approach. I am extracting resultset from my previous query into a text file. Then I import that in a MS Access database on my desktop and here I can use cross join to get that data. Because the number of records are a lot, MS Access is literally hanging. Anyone please help.

I am unable to find online but another way that might work is if database engine can return a string like ('value1', 'value2', 'value3') as 3 rows with one value per row. Is this possible?

Thanks for the help in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-22 : 11:24:06

Without seeing the actual query, it will be hard to assist.

N 56°04'39.26"
E 12°55'05.63"

saketg
Starting Member

8 Posts

Posted - 2010-08-22 : 11:54:29

quote:
Originally posted by Peso

Without seeing the actual query, it will be hard to assist.

N 56°04'39.26"
E 12°55'05.63"

If I simplify the query as simple cross join, then Query is something like:

SELECT table2.filterField, table1.id1, table1.id2, table1.fields [20 columns], table2.fields [30 columns]
FROM table1, table2
WHERE table1.id1 = table2.id1
AND table1.id2 = table2.id2
AND table2.filterField IN ('value1', 'value2', 'value3', 'value4', 'value5', ..., 'value50000')
ORDER BY table2.filterField

In practicality, I have two sets of these two tables (big ones) with mutually exclusive data. Both sets have exactly same schema. So my actual query looks like:

SELECT table2.filterField, table1.id1, table1.id2, table1.fields [20 columns], table2.fields [30 columns]
FROM table1, table2
WHERE table1.id1 = table2.id1
AND table1.id2 = table2.id2
AND table2.filterField IN ('value1', 'value2', 'value3', 'value4', 'value5', ..., 'value50000')
UNION
SELECT table4.filterField, table3.id1, table3.id2, table3.fields [20 columns], table4.fields [30 columns]
FROM table3, table4
WHERE table3.id1 = table4.id1
AND table3.id2 = table4.id2
AND table4.filterField IN ('value1', 'value2', 'value3', 'value4', 'value5', ..., 'value50000')
ORDER BY filterField

So, let us say that data for value1 is found in tables 1 & 2, value2 is found in tables 3 & 4 and value3 is not present in both.
With present query I am getting details for value1 and value2. I want to get value3 also in resultset. I know values of all other fields in select will be NULL.

I hope this helps.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-22 : 12:17:43

as per your scenario it seems like what you need is LEFT OUTER JOIN rather than INNER JOIN which is what you're doing above.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

saketg
Starting Member

8 Posts

Posted - 2010-08-22 : 13:13:39

quote:
Originally posted by visakh16

as per your scenario it seems like what you need is LEFT OUTER JOIN rather than INNER JOIN which is what you're doing above.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

What you said is correct if filter values exist in table2. My problem is not with data in table1 and table2. The long array or set of values for fieldFilter are coming from an external source and I am looking for those values.

For every combination of primary key in table1, there are one or more rows in table2. Similarly, every primary key combination in table 2 can be linked to a unique row in table1.

A way is through dumping filter values in a temporary table. But, I am not sure if without DDL permission will I be able to create a temp table and use it as a part of query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-22 : 13:22:05

can you be more specific? may be show some data sample and explain your problem? are you trying to match table values against a comma separated list passed from parameter?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

saketg
Starting Member

8 Posts

Posted - 2010-08-23 : 08:08:36

quote:
Originally posted by visakh16

can you be more specific? may be show some data sample and explain your problem? are you trying to match table values against a comma separated list passed from parameter?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes, we can assume filter values to be a list of CSV. And intentional is to figure out which of these CSV is not in table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 08:49:24

then isnt it enough to use a udf which parses csv and returns table of values and left join with it.
if your table is tbl, then use like


SELECT t.*
FROM YourTable t
LEFT JOIN dbo.ParseValues(@YourCSVParam,',')f
ON f.Val = t.field
WHERE f.Val IS NULL

ParseValues can be found in below link
http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-23 : 09:55:05

Visakh, that would violate #1 - Read only access to the database.

Saketg, how are you accessing the 50,000 values? There are things you are not telling us, which makes this much MUCH harder to assist than needed.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources