Mysql/php Help

jlsports

HI,

I am new to PHP and MYSQL and I am having a small problem that I can't seem to figure out or find here on this board.

I have two tables
SELECTIONS
RESULTS

both tables have a team field and I want to show all the teams in RESULTS as long as the team IS NOT in the SELECTIONS table

FOR EXAMPLE

SELECTIONS
DATE TEAM

2003-05-01 Los Angeles
2003-05-02 New York
2003-05-03 Oakland
2003-05-04 Florida

RESULTS

DATE TEAM

2003-05-08 Tampa Bay
2003-05-08 Oakland
2003-05-08 Chicago
2003-05-08 Texas

I don't want Oakland to come up when I QUERY RESULTS since it is in SELECTIONS.

I have a feeling I am not making myself very clear but any help would be greatly appreciated.

Jason

EricC

Without knowing your schema here's a small example off the top of my head.. (Note this relies on there being a common id field between the two tables)

SELECT 
  results.id, 
  results.stuff,
  results.more_stuff
FROM
  results
LEFT JOIN selections ON selections.id=results.id
WHERE 
  ISNULL selections.id

Good luck.. 🙂

barand

I think this may do it

SELECT r.date, r.team FROM results r LEFT JOIN selection s
ON r.name = s.name WHERE s.name IS NULL

jlsports

HI,

Thanks Barand that helped a lot and at the very least it go me into the mode to really study JOINS. I did have another problem though. There is a field in SELECTIONS called ID, I want to only compare data in the SELECTIONS table with the ID of 1

This forum has been a huge help to me. If there is a better way for me to explain my data-bases any suggestions would be appreciated.

Thanks again

jlsports

Still having problems here is my code

SELECT results.starttime,results.team,selections.userid
FROM results
LEFT JOIN selections ON results.team = selections.team
WHERE selections.team is NULL and results.date = '2003-05-09'
AND selections.userid='1'

This does not work

I only want to show where selections.userid=1

barand

The problem with that query is if there is no match (which is what you are looking for) then all fields from selection table will be null ('cause no record) therefore selection.team is null and selection.id=1 are mutually exclusive.

If you have mySql 4 you could use a subquery.

If not, extract the selection.id = 1 records into a temporary table and use that instead of selections.