query to display identical data from two columns in one table?

[gerryf19]

I have a table of a sports schedule like this called SCHEDULE

 

Home, Away,Location,Date,Time

team1, team2,field1,June 9, 9am

 

team3, team4,field2,June 9, 9am

team2, team4,field2,June 19, 9am

 

team3, team1,field1,June 19, 9am

 

...

 

I want to extract just the schedule for team1, both home and away with the following

 

SELECT *  FROM `SCHEDULE` WHERE `Home`='team1' AND Away='team1'

 

the only result I get is

Home, Away,Location,Date,Time

team1, team2,field1,June 9, 9am

 

Shouldn't I be getting

 

Home, Away,Location,Date,Time

team1, team2,field1,June 9, 9am

team3, team1,field1,June 19, 9am

 

with this query?

 

 

 

 

[Psycho]

No, you shouldn't even be getting ANY result with that query because none of the records have Team1 as the Home team AND the Away team. That would only occur if Team1 was playing themselves! So, I am confused as to why you are even getting a result. There must be some other problem causing that. You should be using an OR condition.

Edited July 1, 2013 by Psycho

[gerryf19]

or for Pete's sake...nevermind....there was a "space" in the beginning of the AWAY field that was preventing the query from finding the text.....grrrr..bad data,,,,

[gerryf19]

Thanks Psycho, I actually had OR in there, not AND....just typed it wrong. As I stated above, the problem was that the AWAY field was imported from a CSV file and everything in it is preceded by a space, i.e., " Team1", " Team2" etc.....That's why I was tearing my hair out, OR  should have worked, but I had bad data....

[Psycho]

OK, well, if you haven't already figured out how you are going to solve this, I would suggest the following:

 

1. Update the import process to trim the values before inserting into the database.

2. Run a query to correct all the current values

 

UPDATE `SCHEDULE`
SET `Home` = TRIM(`Home`),
    `Away` = TRIM(`Away`)