display record if linked record doesnt exist 2

[thompom]

Hi - sounds easy but am stuck ;-)

How would i make the sql below display records with action.typeid = 1 ONLY if a record with action.typeid <> 2.
Table 'event' links the action records together ON action.eventid.

let me know if not making sense

select * 
from event
INNER JOIN 
(SELECT * FROM action WHERE typeid = '1' AND typeid <> '2') AS TA ON TA.eventid = event.eventid

 

[thompom]

line should read

How would i make the sql below display records with action.typeid = 1 only if a record with action.typeid = 2 does not exist and action.eventid is the same

 

[thompom]

Hi - please help with this

 

[towerbase]

I've just finished reading Rudy Limeback's excellent book 'Simply SQL'. Rudy posts on this site with the r937 handle. Although I've used MySQL for several years, I think that I've learnt enough new insights into SQL from reading his book to tackle your problem.

Your problem isn't very clearly stated (small example tables would have helped along with the expected output). If I understand your requirements correctly this is how you should tackle the problem.

So first get a list of eventids where the typeid=2.

SELECT 
  eventid
FROM 
  action
WHERE 
  typeid = 2

Now you actually want rows where the typeid does not equal 2. So use the previous SQL as a subquery.

SELECT 
  eventid
, typeid 
FROM 
  action 
WHERE eventid NOT IN 
  (
    SELECT eventid
      FROM action
    WHERE typeid = 2
  )

I'll leave the final bit where you INNER JOIN the event and action tables to produce the final result to you. If you get stuck then post your SQL (and some sample tables) and I'm sure someone will help.

Andrew
Hampshire, UK

 

[tsuji]

Or maybe, if I read the question correctly, replace this part:
>(SELECT * FROM action WHERE typeid = '1' AND typeid <> '2') AS TA
by this
[tt](SELECT * FROM action WHERE
typeid = (SELECT MAX(typeid) FROM action WHERE typeid>0 AND typeid<3)
) AS TA [/tt]

 

[thompom]

thanks v.much - both your answers led me to the right sql

select * 
from event
INNER JOIN 
(SELECT * FROM action WHERE typeid = 1 
AND
eventid NOT IN 
  (
    SELECT eventid
      FROM action
    WHERE typeid = 2
  )
) 
AS TA ON TA.eventid = event.eventid

 

[tsuji]

I see now better the requirement. Thanks!