SQL question

ct5amr2ig1nfhp

Hi,
I'm trying to write an sql statement and I'm not too sure how to do it.

I've a dropdown box list say A,B,C

Say I've 5 records. (In reality its about 1.5 million records)
ID NAME CLASS
1 Joe A
2 Joe B
3 Mary B
4 Rob A
5 Karen C

A user has selected "A" from the dropdown.

I want to retrieve all records that are class "A". If a record isn't found for "A" try "B" else try"C".

So on the above data my resultset will have

1 Joe A
3 Mary B
4 Rob A
5 Karen C

I can do it, but its slow and just not good at all.

I hope someone will understand. If anyone can help it would be great. Thanks!

ambrose :cool:

Evil Phil

What RDBMS are you using? Sounds like something a stored procedure would be better for.

You probably need to do some optimising too so create an index on your table for the Class field. You've 1.5 mil records in there already so I'm assuming you can't split the Class field out to another table. But if you can split it out then you could something like:

table 1

---

ID NAME CLASSID
1 Joe 1
2 Joe 2
3 Mary 2
4 Rob 1
5 Karen 3

Class

---

CLASSID Description
1 A
2 B
3 C

Create indexes on table 1.CLASSID and Class.CLASSID (a numeric index is always faster than char based) and use a join in your query. That should speed things up some.

Could you post the actual SQL code?

ct5amr2ig1nfhp

Thanks for the prompt reply. We're using Sql Server 2k.

I have tried to split the tables already, like you suggested but I'm still in the same position to get the expected result.

A better example of what I want:

SELECT * FROM table_name WHERE name = 'Joe' AND class = 'A' OR name = 'Karen' AND class = 'A'

Result: 1 | Joe | A

Matches 'Joe' and 'A'. However for Karen, if it doesn't find a match.

Instead I'd like it to try and match

'Karen' and 'A', else if not found
'Karen' and 'B', else if not found
'Karen' and 'C'

So the final result will be

1 | Joe | A
5 | Karen | C

That make any sense?

ambrose

Evil Phil

Kinda makes sense. It stick something like the T-SQL code below into a stored proc and call that. Better than static/inline sql.

IF ((SELECT COUNT(*) FROM table_name WHERE Name = 'Joe' 
			OR NAME = 'Karen' AND Class = 'A') > 0)
BEGIN
	SELECT * FROM table_name WHERE Name = 'Joe' 
			OR NAME = 'Karen' AND Class = 'A'
	RETURN 0
END 

IF ((SELECT COUNT(*) FROM table_name WHERE Name = 'Joe' 
			OR NAME = 'Karen' AND Class = 'B') > 0)
BEGIN
	SELECT * FROM table_name WHERE Name = 'Joe' 
			OR NAME = 'Karen' AND Class = 'B'
	RETURN 0
END 

IF ((SELECT COUNT(*) FROM table_name WHERE Name = 'Joe' 
			OR NAME = 'Karen' AND Class = 'C') > 0)
BEGIN
	SELECT * FROM table_name WHERE Name = 'Joe' 
			OR NAME = 'Karen' AND Class = 'C'
	RETURN 0
END 

RETURN -1 /* Where Return Value Indicates 
		That No Records Were Found */

I'm assuming you know the ins and outs of stored procs but if not its a good time to learn. The code above should not be taken as is btw - its just to illustrate an example.