Simplifying query across multiple columns

I have a table of classroom names that I want to compare against several known instruments. My table has a total of 11 columns but four of the columns contain the section leader instrument names(instrument1...instrument4). I want to create a SQL statement that will compare the two instruments I am looking for against the four names present in each column per row.

ex. I need to find Trumpet and trombone

I wo开发者_开发知识库uld think it would look something like this.

Here is the statement that I have

SELECT *
FROM   section_info
WHERE  ( instrument1 = trombone
          OR instrument2 = trombone
          OR instrument3 = trombone
          OR instrument4 = trombone )
       AND ( instrument1 = trumpet
              OR intsrument2 = trumpet
              OR instrument3 = trumpet
              OR instrument4 = trumpet ) 

Is there a way to simplify?

Given your database structure, this code is just fine. Just make sure you format it nicely and spell trombone correctly.

You always have the option of adjusting your database structure, however.

You could:

1. Create a Player table, which has fields PlayerID and PlayerName.
2. Create an Instruments table, which has fields InstrumentID and InstrumentName.
3. Finally, create a Player_Instruments table, which has fields PlayerID and InstrumentID. This maps Instruments to Players.

Select Players who own a trumpet OR trombone:

SELECT *
FROM   players AS P
       INNER JOIN player_instruments AS PI ON PI.PlayerID = P.PlayerID 
       INNER JOIN instruments AS I ON I.InstrumentID = PI.InstrumentID
WHERE  I.InstrumentName = 'trumpet'
        OR I.InstrumentName = 'trombone'  

Select Players who own a trumpet and trombone:

SELECT *
FROM   players AS P
       INNER JOIN player_instruments AS TRUMP ON TRUMP.playerid = P.playerid
       INNER JOIN instruments AS TRUMP_I ON TRUMP_I.instrumentid = TRUMP.instrumentid AND TRUMP_I.InstrumentName = 'trumpet'
       INNER JOIN player_instruments AS TROM ON TROM.playerid = P.playerid
       INNER JOIN instruments AS TROM_I ON TROM_I.instrumentid = TROM.instrumentid AND TROM_I.InstrumentName = 'trombone'  

The Inner Join here causes only those players who have a valid mapping to a trumpet and trombone be included in the result set.

 SELECT * FROM SECTION_INFO
 WHERE INSTRUMENT1 in ('TROMBONE', 'TRUMPET')
 or INSTRUMENT2 in ('TROMBONE', 'TRUMPET')
 or INSTRUMENT3 in ('TROMBONE', 'TRUMPET')
 or INSTRUMENT4 in ('TROMBONE', 'TRUMPET')

Not a lot simpler, but since you are comparing multiple columns, I don't see much alternative.

EDIT: Now that I reread the question, this may not work well if you have to have both a Trombone AND a Trumpet. So I may have failed on this. :) Sorry.

Given the following table schemas:

CREATE TABLE `instruments` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `instrument` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB

CREATE TABLE `section_info` (
    `instrument1` INT(10) NULL DEFAULT NULL,
    `instrument2` INT(10) NULL DEFAULT NULL,
    `instrument3` INT(10) NULL DEFAULT NULL,
    `instrument4` INT(10) NULL DEFAULT NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB

You could do the following:

SELECT si.*, inst_1.instrument, inst_2.instrument, inst_3.instrument, inst_4.instrument      
FROM section_info si                                                                         
    LEFT JOIN instruments as inst_1 ON si.instrument1=inst_1.id                               
    LEFT JOIN instruments as inst_2 ON si.instrument2=inst_2.id                               
    LEFT JOIN instruments as inst_3 ON si.instrument3=inst_3.id                               
    LEFT JOIN instruments as inst_4 ON si.instrument4=inst_4.id                               

WHERE                                                                                        
    # instrument 1 is a trombone...                                                           
    ( si.instrument1 = 1 OR si.instrument2 = 1 OR si.instrument3 = 1 OR si.instrument4 = 1 )  
    AND                                                                                       
    # instrument 2 is a trumpet...                                                            
    ( si.instrument1 = 2 OR si.instrument2 = 2 OR si.instrument3 = 2 OR si.instrument4 = 2 )    

If you mean you want to simplify the query script for yourself, then you could rewrite the WHERE clause like this:

WHERE 'trombone' IN (instrument1, instrument2, instrument3, instrument4)
  AND 'trumpet'  IN (instrument1, instrument2, instrument3, instrument4)

But I must say, this may prevent the DB engine from using indexes on the said columns, if any.