Drill down searching

[Borvik]

I am having some trouble constructing a query, and I'm starting to not think straight about everything at this point.

Here is an example table with values:

Table
----------
A B
----------
1 1
1 2
2 2
2 3
3 1
3 4


Here is what I am trying to accomplish.

I am trying to end up with a unique value for A (can be multiple at first).

Example: User selects value 1 (for column B) this results in either a 1 or a 3 for column A. This isn't good enough for the User then need to select another value for column B (2 or 4), the combination of which gets them a unique value for A.

I can already tell this wasn't phrased good - if you can interpret it great, if not I can try to explain better when I'm thinking isn't as fuzzy.

 

[johnv20]

How do you know which value for column A should be picked ?

Also can you post what you already have in order to aid in understanding the problem ?

 

[Borvik]

I don't really have much of anything at the moment - I'm not that great at SQL writing. But here is a better description of what I'm looking for (I hope).

A B
---------
1 1
1 2
1 3
2 1
2 2
2 4
3 1
3 4
3 3
4 2
4 3
4 4


OK - hopefully this is a better example. I've expanded it a little to be a little more detailed.

At first the user will see a list of possible end results (column A) and choices (column B).
The user wants 1 end result, by making choosing the choices (column B).
By choosing choice 1 the user eliminates result 4 from the visible list of end results.
The user must continue to choose a choice (column B) until only one possible result remains.
Example: choosing 1 then 3 and then 4 (all from column B) would yield the result (from column A) 3 as that is the only one with that unique pattern.


I would assume part of the SQL would use the IN comparison ( B IN (1,3,4) ) or something like that, but I'm not all that fluent in SQL - and just can't figure it out.

Maybe it can't be done in SQL and my program will have to sort through it - I just thought it better if it could be done in the SQL.

Any ideas?

 

[johnv20]

Ok - you'll need ome way to store the choices made either in your code (preferably) or within the database structure itself.

Assuming you will store each selected value within the app code then the following sql should work

All it does is allow for up to 3 values to be passed & return the unique value from this
You then need to pass a 4th value which is the result of A that the user requires

CREATE PROCEDURE  dbo.testing
@val1 int = 0,
@val2 int = 0,
@val3 int = 0,
@val4 int = 0

 AS

if @val4 = 0
	begin
	select ColA
	from TestTable where ColB != @val1
	and ColB != @val2
	and ColB != @val3
	end
else
	begin
	select ColA
	from TestTable where ColB != @val1
	and ColB != @val2
	and ColB != @val3
	and ColA = @val4
	end
GO

As you can see as each val integer is given a value the number of potential choices diminishes

Hope this helps

 

[Catadmin]

If you're looking for a call-response type of code for SQL, there isn't really one. I assume that's what you're looking for, anyway. Let me see if I get this correct.

The program gives a list of all possible values in columns A & B. The user, for whatever reason, searches on Column B and chooses one of the values. At which point the program pauses, does some sorting, and gives up a list of all possible values in both A & B which remain consistant with the user's initial choice.

So if the user chooses (from second example) 1, the resultant list would be:

A B
---------
1 1
2 1
3 1

Correct?

Then the user decides if he wants the values of 1, 2 or 3 from Column A and gets the final result set, correct?

If this is the case, the best way to do it is in the application code rather than SQL.

If you do it in SQL, you have to write at least two stored procedures, one called by the Column B value and the second called by Column A value which somehow retains the initial Column B value. This retention of a variable is very hard to do in SQL when separate pieces of code, which may be using different connection threads, are run.

However, if you do it in the program, the program can retain the value of Column B no matter which connection thread it's using for a particular query. You may still have to write two stored procedures for SQL, but at least at that point, you don't have to worry how to connect the two with that variable that disappears when a session ends.

Does that make sense?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Borvik]

Thanks for trying to help johnv20, but the user doesn't know what the end result will be - they just need it, so passing the 4th value to compare to ColA just won't work.

Also, it won't be limited to just a 3 combo - that was just simple test environment I set up to emulate what I need. It could be as few as 1 or as many as 10 or higher.

 

[Borvik]

Catadmin,

You are correct about the first selection results.

You are partially correct. The user will end up choosing 1 or more values of column B, so to continue the example the user would then choose 4 resulting in.

A B
------
2 1
2 4
3 1
3 4


Then the user would choose the last value, say 2, which would result in the final answer:

A B
------
2 1
2 2
2 4

In all cases choosing the Distinct value of A for display.


The following SQL works for the first choice:

SELECT * FROM Test WHERE B IN (1)

But that stops working as intended when I add the other choices in:

SELECT * FROM Test WHERE B IN (1,4,2)



I figured I may have to handle that in the application (web app), but was hoping I would be able to avoid that route to make it quicker.

 

[Catadmin]

Sorry, but your best bet is in the Web App given your scenario.

That's not to say you couldn't do it in SQL. It'd just take a lot of time to code it correctly.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Borvik]

That's what I figured.

Thanks for all the help so far.

Given more time to think (and my head clearing), I came up with another solution (though it could be unwieldy).

SELECT tbl2.* FROM Test AS tbl1 INNER JOIN Test AS tbl2 ON tbl1.A = tbl2.A WHERE tbl1.B = 1

Gets me the results of one iteration. I start thinking, I can run further queries on this using it as a sub-query. So I wrote a quick function to build the sql query for me.

I then embed that into two spots in an sql template following the original replace Test with itself resulting in the following query:

SELECT tbl4.* FROM (SELECT tbl2.* FROM Test AS tbl1 INNER JOIN Test AS tbl2 ON tbl1.A = tbl2.A WHERE tbl1.B = 1) AS tbl3 INNER JOIN (SELECT tbl2.* FROM Test AS tbl1 INNER JOIN Test AS tbl2 ON tbl1.A = tbl2.A WHERE tbl1.B = 1) AS tbl4 ON tbl3.A = tbl4.A WHERE tbl3.B = 4

While the SQL could size could grow exponentially it does produce the required results.

Can this be optimized? Would it be easier to do the filter in the app?

 

[Catadmin]

Honestly, there's no good answer to that. You'd have to play with it to see if it works for you. And throw a huge load against it, then check it in Profiler while it's running to verify it is doing what you want it to do.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Borvik]

Well, thanks for the help guys - it at least started getting me thinking along the right track.