Preserved Topic: Foreign Keys in MySQL (Page 1 of 1)

I just noticed that my main query fails to pull 15 out of 165 events. The only possible explanation I can think of is that those events have non-existent foreign keys so that they get dropped out on the WHERE Event.foreignKey=foreignTable.foreignKey clause.

The problem is how do you check for foreign key integrity? Or in other words, how do you query for rows where one of the values doesn't exist in another table...

-jiblet

if i understood you correctly then this would just go by a "where ... is not ..." using the <> operator.
but im not sure i understood you.

Basically what I want is a query like this:

SELECT * FROM Table WHERE id_field IS NOT (a value present in Table2.id_field).

So for instance, if a row of Table has the value 1 for id_field, I only want the row to be displayed if Table2.id_field does not contain 1 ON ANY ROW.

I figure this might not be possible because MySQL doesn't support foreign key integrity checks per se, and I know this would be easy with sub-selects, but maybe there's a function in MySQL that does what I need here.

-jiblet

The poster has demanded we remove all his contributions, less he takes legal action.
We have done so.
Now Tyberius Prime expects him to start complaining that we removed his 'free speech' since this message will replace all of his posts, past and future.
Don't follow his example - seek real life help first.

hey jiblet, this is straight from the mysql Manual, Join Syntax section.

select table1.* from table1
LEFT JOIN table2 ON table1.id=table2.id
where table2.id is NULL;
(this assumes you have declared table2.id as not null.)

hope this helps,

Tyberius Prime

The poster has demanded we remove all his contributions, less he takes legal action.
We have done so.
Now Tyberius Prime expects him to start complaining that we removed his 'free speech' since this message will replace all of his posts, past and future.
Don't follow his example - seek real life help first.

Good one, thanks tyb. I can't believe i didn't think of that myself. I knew that it shouldn't have to be complicated.

BTW Ini- I been working on modularizing my events calendar today, and testing out speed on queries. I noticed an interesting fact, if you join an extra table that you aren't using in the SELECT or WHERE clauses, it still slows down the query. Even using SELECT DISTINCT syntax which should allow MySQL to ignore consideration of the extra rows generated.

I was thinking about making a dynamic query that only queried for the specific information needed according to input parameters. But I noticed that the difference in speed between selecting 1 field, and 15 fields (several of which are functions) is barely 2x (.04 vs .08 secs w/ 165 events). Considering the parameter processing overhead and code size increase it just doesn't seem justifiable (especially since the DB will never grow by more than 300 events a year).

-jiblet

The poster has demanded we remove all his contributions, less he takes legal action.
We have done so.
Now Tyberius Prime expects him to start complaining that we removed his 'free speech' since this message will replace all of his posts, past and future.
Don't follow his example - seek real life help first.