[SOLVED] Selecting records that are referenced in 2nd table, but not the exact reference.

[justravis]

The solution is probably related to these posts, but my problem is slightly different:

http://www.phpfreaks.com/forums/index.php/topic,113742.0.html

http://www.phpfreaks.com/forums/index.php/topic,214586.0.html

 

QUESTION

id    name

1    Who is...

2.  What is...

 

QUEST-FORM

id    form_id  quest_id

1    1          1

2    2          2

 

How do I find the question NOT linked to form 1, which is question 2?

 

Here are the queries I have tried without success?

 

SELECT question.id, question.name FROM question, `quest-form` WHERE `quest-form`.form_id=1 AND question.id!=`quest-form`.quest_id

 

SELECT question.id, question.name FROM question LEFT JOIN `quest-form` ON question.id=`quest-form`.quest_id WHERE `quest-form`.quest_id IS NULL AND `quest-form`.form_id=1

 

select question.id, question.name from question left join `quest-form` using (`quest-form`.quest_id) where `quest-form`.form_id=1 AND `quest-form`.quest_id IS NULL

 

MySQL client version: 4.1.22

 

THANK YOU!

[fenway]

You need:

 

SELECT question.id, question.name FROM question LEFT JOIN `quest-form` ON (question.id=`quest-form`.quest_id AND `quest-form`.form_id=1 )WHERE `quest-form`.quest_id IS NULL

[justravis]

u r a god!  i hereby offer u my first born!

[fenway]

u r a god!  i hereby offer u my first born!

No problem... glad you got it working.  Basically, all the left join-ed fields are NULLed out on non-matches, so adding a condition to the where clause will always evaluate to false -- the join condition is evaluated *before* the NULLing out (necessarily, because otherwise it couldn't find them!).

[justravis]

Here are the solutions posted in the threads I mentioned above:

DELETE genres FROM genres LEFT JOIN movie_genres ON genres.genre_id = movie_genres.genre_id WHERE movie_genres.genre_id IS NULL

select s.* from staff as s left join depts as d using ( staffID ) where d.staffID IS NULL

 

I'm confused.  What is the difference between "ON table1.pri_key=table2.for_key" & "USING(foreign_key)"?

 

 

 

THANKS!

[fenway]

You can only use USING if both fields are named identically in both tables... ON allows you to specify the column names explicitly (e.g. when one is called "id" in the staff table and "staffID" in the dept table).

[justravis]

I am trying to find events that MAY (or may not) be linked to a seeker in event-seeker, but not seeker 1

 

this sql statement stopped working:

SELECT event.id, title, UNIX_TIMESTAMP(start) AS start, UNIX_TIMESTAMP(reg_end) AS reg_end FROM event LEFT JOIN `event-skr` ON (event.id=`event-skr`.event_id AND `event-skr`.skr_id=1) WHERE `event-skr`.event_id IS NULL AND  event.coor_id=1 AND event.id!='0' AND event.dmd=1 AND public=1 AND CURRENT_DATE() > event.reg_start AND CURRENT_DATE() < event.reg_end ORDER BY event.reg_end

 

no errors, just 0 results when entered into phpmyadmin.

 

i did accidently delete the event-skr table and i dont recall if it stopped working immediately afterwards...the foreign key types match thier primary key counterpart.

 

can u think of any pitfalls i could hav ran into when recreating the table?

 

 

THANKS!

[justravis]

this did not turn out to be a Left Join issue...

 

since reg_start was EQUAL (vs <) to today's date, no rows selected.

 

even tho i solved this issue, i still appreciate all you have given...thx

 

hopefully others can learn from this mistake.