Page 1 of 1
Which WHERE clause is true?
Posted: Thu Jun 03, 2004 3:00 pm
by bironeb
Code: Select all
SELECT computer_name, date, user_id, department
FROM client_software
WHERE Software_1 = 'Microsoft Office' OR
Software_2 = 'Microsoft Office' OR Software_3 = 'Microsoft Office' OR Software_4 = 'Microsoft Office' OR Software_5 = 'Microsoft Office' OR
Software_6 = 'Microsoft Office' OR Software_7 = 'Microsoft Office' OR Software_8 = 'Microsoft Office' OR Software_9 = 'Microsoft Office' OR
Software_10 ='Microsoft Office' ORDER BY computer_name
Is there a way I can change this statement or use another statment to find out where it found the WHERE clause to be true?.
For example, if it found Microsoft Office in Software_4, it would also return that field name.
Posted: Thu Jun 03, 2004 3:07 pm
by feyd
seems more like you need to rethink/redesign the table structure..
Posted: Thu Jun 03, 2004 4:41 pm
by bironeb
Well I know my structure is not exactly texbook normalized, but I thought there maybe a way to return the field where it found the WHERE clause to be true.
Posted: Thu Jun 03, 2004 4:54 pm
by pickle
I don't think there's anyway to do it query-wise, but you could modify your query a little to ask for the software columns as well, then traverse the returned rows to see which one matches.
Posted: Fri Jun 04, 2004 6:48 am
by JAM
Just curious before continuing:
For example, if it found Microsoft Office in Software_4, it would also return that field name.
As you allready know that "Microsoft Office" is found (as you used it in the where-clause), why not just echo "Microsoft Office"; it out? Sounds like overdoing the work.
Posted: Fri Jun 04, 2004 8:18 am
by mendingo
I'm pretty sure there's no way to do that in SQL.
What you should be doing is altering the table, so you have in one table, (which I'll call Computer)
computer_id (key),
computer_name,
date,
user_id,
department,
and in another table (called software),
software_ID (key)
software_number (int)
software_title (varchar)
computer_id
The computer id in the second table corresponds to the record for which it's a key in the first table, but there can be multiple values in the second table.
there will be 10 rows for each computer_id, with software_number being 1-10. These 10 rows replace the 10 fields in your original tables.
Then you can use
Code: Select all
SELECT software_ID, software_number
FROM software
WHERE software_title = 'Microsoft Office'
to get the software number, which you were originally after.
If you want the rest of the data, you use
Code: Select all
SELECT *
FROM computer
WHERE computer_id = <the ID from the previous query>
That will get what you want and will have a much neater table structure.
Try this, its much easier, and it should work
Posted: Fri Jun 04, 2004 3:57 pm
by Calimero
SELECT computer_name, date, user_id, department
FROM client_software
WHERE (Software_1 OR Software_2 OR ... Software_10 )= '$field-input'
//comment - $field-input or any other variable you use, but still stands to change table structure, and possibly use like in the SQL statement...
// to continue blah, blah on this, need more info.