Which WHERE clause is true?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
bironeb
Forum Commoner
Posts: 59
Joined: Thu Nov 20, 2003 12:02 pm

Which WHERE clause is true?

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

seems more like you need to rethink/redesign the table structure..
User avatar
bironeb
Forum Commoner
Posts: 59
Joined: Thu Nov 20, 2003 12:02 pm

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
User avatar
mendingo
Forum Commoner
Posts: 28
Joined: Sun May 23, 2004 1:27 pm

Post 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.
User avatar
Calimero
Forum Contributor
Posts: 310
Joined: Thu Jan 22, 2004 6:54 pm
Location: Milky Way

Try this, its much easier, and it should work

Post 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.
Post Reply