results from 2 tables that does not match

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

results from 2 tables that does not match

Author

Topic

gjja
Starting Member

17 Posts

Posted - 2004-08-29 : 02:17:49

I have 2 tables, MEETINGS and EVENTS. Some of the meetings have entries in the events table. I want to show only the MEETINGS table results that does not have any EVENTS records.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-29 : 02:48:54

select m.*
from Meetings m
left outer join Events e
on e.Meeting_id = m.Meeting_id
where e.Meeting_id is null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

gjja
Starting Member

17 Posts

Posted - 2004-08-29 : 15:37:20

It does not work like I want it to work. Here is the example.

MEETINGS Table
==============
MTEVENT (PK)
AGE

EVENTS Table
============
EVENTS (PK)
MTEVENT
PERSON

On a webform a have all MEETINGS as options to select. When form is submitted, the results are written to the EVENTS table, and the MEETINGS.MTEVENT is written into the EVENTS.MTEVENT field. When a user goes back to the webform I need to do a lookup into both tables and only populate the webform with values that is not entered.

I want to do a lookup where the results must be only data from the MEETINGS Table and only the rows that does not have any values (MTEVENT) in the EVENTS table.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-29 : 16:27:38

So either this is a programming issue, or you did not understand nr's sample sql.
It's good that you posted the table design, what is the sql that You have so far ?

/rockmoose

/* Chaos is the nature of things...Order is a lesser state of chaos */

gjja
Starting Member

17 Posts

Posted - 2004-08-29 : 16:45:02

When useing the nr sample the where clause does not work if I select WHERE AGE = 24. It shows the entire database. The fields with no MTEVENTS data does get filtered out.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-29 : 16:51:54

Well do you have
WHERE AGE = 24 AND EVENTS.MTEVENT IS NULL ?

Just post the Sql you have.

/rockmoose

/* Chaos is the nature of things...Order is a lesser state of chaos */

gjja
Starting Member

17 Posts

Posted - 2004-08-29 : 17:03:50

SELECT MEETINGS.MtEvent, MEETINGS.Meet, MEETINGS.Age
FROM MEETINGS LEFT JOIN EVENTS ON MEETINGS.MtEvent = EVENTS.MtEvent
WHERE ((EVENTS.MtEvent) Is Null) AND ((MEETINGS.Age)=24)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-29 : 17:16:05

quote:
Originally posted by gjja

SELECT MEETINGS.MtEvent, MEETINGS.Meet, MEETINGS.Age
FROM MEETINGS LEFT JOIN EVENTS ON MEETINGS.MtEvent = EVENTS.MtEvent
WHERE ((EVENTS.MtEvent) Is Null) AND ((MEETINGS.Age)=24)

Ok, let me understand the problem.
If You run that query in Query Analyzer, it does not give you what you want ?.

It should show you all records in the MEETINGS table that:
1. Have no matching records in EVENTS table ( matched on MtEvent )
2. Have Age = 24.

Is this not what You wanted ?

/rockmoose

/* Chaos is the nature of things...Order is a lesser state of chaos */

gjja
Starting Member

17 Posts

Posted - 2004-08-29 : 17:23:08

I think because of "JOIN EVENTS ON MEETINGS.MtEvent = EVENTS.MtEvent" it shows me everything that is true for that condition.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-29 : 17:32:28

Yes.
Adding the "LEFT JOIN EVENTS ON MEETINGS.MtEvent = EVENTS.MtEvent"
Will also show all the records from the MEETINGS table where there is no corresponding match in the EVENTS table.
Then the clause "WHERE ((EVENTS.MtEvent) Is Null)" will filter this result to only show the MEETINGS with no EVENTS.

patience is a virtue,
/rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources