Find not matched - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Find not matched

Author

Topic

nobby
Yak Posting Veteran

58 Posts

Posted - 2006-10-17 : 01:13:45

I am very new to MsSQL having only used Access before.
I am trying to find records in the second select statement that aren't in the first select statement. Using the

dbo.Tbl_01035_Tour_Players.TourTypeId (1st select stat)
Tbl_01020_Tour_Types.TourTypeId (2nd select stat)

Have been working on it for 15 hours now and still can't find a way to do it. Thanxs in advance

@_MemberId int

AS
BEGIN
SET NOCOUNT ON;
Begin
SELECT dbo.Tbl_01035_Tour_Players.TourTypeId, dbo.Tbl_01035_Tour_Players.MemberId, dbo.Tbl_01030_Tour_Schedule.Sch_TourId,
dbo.Tbl_01030_Tour_Schedule.Sch_TourStartDate, dbo.Tbl_01030_Tour_Schedule.Sch_TourEndDate,
dbo.Tbl_01030_Tour_Schedule.Sch_TourGame
FROM dbo.Tbl_01035_Tour_Players INNER JOIN
dbo.Tbl_01030_Tour_Schedule ON dbo.Tbl_01035_Tour_Players.TourId = dbo.Tbl_01030_Tour_Schedule.Sch_TourId
WHERE (dbo.Tbl_01030_Tour_Schedule.Sch_TourEndDate >= GETDATE()) and dbo.Tbl_01035_Tour_Players.MemberId = @_MemberId
End

Begin
SELECT Tbl_01020_Tour_Types.TourTypeId, Tbl_01020_Tour_Types.TourGame, Tbl_01020_Tour_Types.TourDescr, Tbl_01020_Tour_Types.TourDaysDur,
Tbl_01020_Tour_Types.TourMinPot
FROM Tbl_01020_Tour_Types CROSS JOIN
Tbl_01035_Tour_Players
WHERE (NOT (Tbl_01020_Tour_Types.TourTypeId = db.Tbl_01035_Tour_Players.TourTypeID))
End

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-17 : 01:22:44

[code]Select T2.* from Tbl_01020_Tour_Types T2
where not exists (select * from Tbl_01035_Tour_Players T1
where T2.TourTypeId = T1.TourTypeId)[/code]

---------------- or -----------------------

[code]Select *
from Tbl_01020_Tour_Types
Where TourTypeId not in (select TourTypeId from Tbl_01035_Tour_Players)[/code]

---------------- or -----------------------

[code]Select T2.*
From Tbl_01020_Tour_Types T2
Left Join
Tbl_01035_Tour_Players T1
on T2.TourTypeId = T1.TourTypeId
Where T1.TourTypeId is Null[/code]

Harsh Athalye
India.
"Nothing is Impossible"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-10-17 : 01:24:34

translating...if you have a unique id in table A and referencing ID in table B

select * from tableA
where id not in (select id from tableB)

you can also try joining the tables

--------------------
keeping it simple...

nobby
Yak Posting Veteran

58 Posts

Posted - 2006-10-17 : 01:27:12

Sorry being totally new to sql and at the risk of making myself look stupid, what is TI and T2

mk.vignesh
Starting Member

3 Posts

Posted - 2006-10-17 : 01:32:57

T1 and T2 are aliases for the 2 tables u hav used. instead of typing 'dbo.Tbl_01035_Tour_Players.TourTypeId' in ur query u can now simply type
T1.TourTypeId...

Nobody is Perfect in this world,I am Nobody

nobby
Yak Posting Veteran

58 Posts

Posted - 2006-10-17 : 01:46:05

Thanks everyone for you quick response
I have it working now except if the top select returns no records how do i get the second select to show all records, at the moment if none are returned on the first select for some reason the secon select returns only 2 records instead of all records

Begin
SELECT dbo.Tbl_01035_Tour_Players.TourTypeId, dbo.Tbl_01035_Tour_Players.MemberId, dbo.Tbl_01030_Tour_Schedule.Sch_TourId,
dbo.Tbl_01030_Tour_Schedule.Sch_TourStartDate, dbo.Tbl_01030_Tour_Schedule.Sch_TourEndDate,
dbo.Tbl_01030_Tour_Schedule.Sch_TourGame
FROM dbo.Tbl_01035_Tour_Players INNER JOIN
dbo.Tbl_01030_Tour_Schedule ON dbo.Tbl_01035_Tour_Players.TourId = dbo.Tbl_01030_Tour_Schedule.Sch_TourId
WHERE (dbo.Tbl_01030_Tour_Schedule.Sch_TourEndDate >= GETDATE()) and dbo.Tbl_01035_Tour_Players.MemberId = @_MemberId
End

--
Begin
select * from dbo.Tbl_01020_Tour_Types
where TourTypeId not in (select TourTypeId from dbo.Tbl_01035_Tour_Players)
end

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-10-17 : 01:55:18

will both tables be identical except for data? then you're looking for a union instead

select * from tableA
union
select * from tableB

--------------------
keeping it simple...

nobby
Yak Posting Veteran

58 Posts

Posted - 2006-10-17 : 02:00:05

I hope this explanation makes sense

Table a has tournaments a member has already joined

Table b is the type of tournaments they can join

If they have joined 3 out of 4 tournaments then table b would only show the 1 they haven't joined, if they haven't joined any yet then table b needs to show all its records

And we pass the membersId to the first select using a parameter

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-17 : 02:23:21

quote:
Originally posted by nobby

Thanks everyone for you quick response
I have it working now except if the top select returns no records how do i get the second select to show all records, at the moment if none are returned on the first select for some reason the secon select returns only 2 records instead of all records

Begin
SELECT dbo.Tbl_01035_Tour_Players.TourTypeId, dbo.Tbl_01035_Tour_Players.MemberId, dbo.Tbl_01030_Tour_Schedule.Sch_TourId,
dbo.Tbl_01030_Tour_Schedule.Sch_TourStartDate, dbo.Tbl_01030_Tour_Schedule.Sch_TourEndDate,
dbo.Tbl_01030_Tour_Schedule.Sch_TourGame
FROM dbo.Tbl_01035_Tour_Players INNER JOIN
dbo.Tbl_01030_Tour_Schedule ON dbo.Tbl_01035_Tour_Players.TourId = dbo.Tbl_01030_Tour_Schedule.Sch_TourId
WHERE (dbo.Tbl_01030_Tour_Schedule.Sch_TourEndDate >= GETDATE()) and dbo.Tbl_01035_Tour_Players.MemberId = @_MemberId
End

--
Begin
select * from dbo.Tbl_01020_Tour_Types
where TourTypeId not in (select TourTypeId from dbo.Tbl_01035_Tour_Players)
end

What first SELECT has to do with second one?

Have you tried my solutions?

Harsh Athalye
India.
"Nothing is Impossible"

nobby
Yak Posting Veteran

58 Posts

Posted - 2006-10-17 : 02:43:08

Harsh

Ihave tried this one, but if @_Memberid = a member who has nothing in the first select then the second select only returns 2 records instead of 4 because other members

Table A MemberID TourTypeID
1 10
1 11

Table B TourTypeId TourDescrip Etc etc
10 descrip10
11 descrip11
12 descrip11

2nd select works fine for MemberId=1 it returns tOurTypeId 12

But if MemberId=5 and he is going to choose his first TourId to Join he should See all 3 TourTypeID's but it only returns TourTypeID 12

begin
Select T2.*
From Tbl_01020_Tour_Types T2
Left Join
Tbl_01035_Tour_Players T1
on T2.TourTypeId = T1.TourTypeId
Where T1.TourTypeId is Null
end

PS. The first select lets them see the TourTypeId's they already have. and the second select lets them see TourTypeId's they don't have yet.

nobby
Yak Posting Veteran

58 Posts

Posted - 2006-10-17 : 03:22:23

Harsh is not online anymore can someone else please help with this, I've been up nearly 24 hours now just trying to do this one procedure. Lets hope i learn the rest of sql quicker.

Thanxs

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-17 : 03:29:46

nobby,

quote:
If they have joined 3 out of 4 tournaments then table b would only show the 1 they haven't joined, if they haven't joined any yet then table b needs to show all its records

If this is the case, then LEFT JOIN query should work. It will show you all those member records who has not joined at least one or all tournaments.

Please post some sample data and expected output, to get better picture.

Harsh Athalye
India.
"Nothing is Impossible"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-10-17 : 03:36:08

[code]
declare @memberid int
set @memberid=10

if not exists(select * from @t1 where memberid=@memberid)
select * from @t2
else
select tourdescrip from @t1 t1
join @t2 t2
on t1.tourtypeid=t2.tourtypeid
where memberid=@memberid
[/code]

--------------------
keeping it simple...

nobby
Yak Posting Veteran

58 Posts

Posted - 2006-10-17 : 03:57:04

quote:

Originally posted by jen


declare @memberid int
set @memberid=10

if not exists(select * from @t1 where memberid=@memberid)
   select * from @t2
else
   select tourdescrip from @t1 t1
   join @t2 t2
   on t1.tourtypeid=t2.tourtypeid
   where memberid=@memberid

--------------------
keeping it simple...

Jen as you know i am new to this, do i have to set @memberid=10 this parameter will change we will be passing this via asp page..eventually

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-17 : 04:02:58

You can create Stored Procedure and pass @memberid as input parameter to it.

Harsh Athalye
India.
"Nothing is Impossible"

nobby
Yak Posting Veteran

58 Posts

Posted - 2006-10-17 : 04:04:34

Jen or Harsh
I have really simplified the first select it only uses one tablee now but i still don't get the results i expect i am sure its to do with the memberid filter. because the 2 records it never returns are being used by some memberid's it only ever returns the two that know one is using where as for a new MemberId it should return all four records from the Tbl_01020_Tour_Types table

@_MemberId int

AS
BEGIN
SET NOCOUNT ON;
Begin
SELECT TourTypeId, MemberId, TourId, TourStartDate, TourEndDate
FROM Tbl_01035_Tour_Players
WHERE (MemberId = @_MemberId) AND (TourEndDate >= GETDATE())
End

begin
Select T2.*
From Tbl_01020_Tour_Types T2
Left Join
Tbl_01035_Tour_Players T1
on T2.TourTypeId = T1.TourTypeId
Where (T1.TourTypeId is Nul

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-10-17 : 04:07:45

did you try the approach provided checking for existence of records in table A?

if it's not existing then return all rows in tableB
if existing, just return the rows in tableB that corresponds to the tourtypeid in tableA

the concept is quite simple unless you're not providing the entire picture... post the data that should return 4 records

--------------------
keeping it simple...

nobby
Yak Posting Veteran

58 Posts

Posted - 2006-10-17 : 04:35:02

Thanxs everyone for your help i hope i have explained this a bit better this time.

Tbl_01035_Tour_Players

ID MemberId TourTypeId TourId TourStartDate TourEndDate

1 10000028 10000002 10000273 21/10/2006 00:00:00 30/10/2006 00:00:00
2 10000043 10000003 10000137 01/10/2006 00:00:00 05/10/2006 00:00:00
3 10000043 10000003 10000279 16/10/2006 00:00:00 20/10/2006 00:00:00
4 10000043 10000003 10000280 21/10/2006 00:00:00 25/10/2006 00:00:00

Tbl_01020_Tour_Types

TourTypeId TourGame TourDescription

10000002 BF2_01 Rolling 10 day tournament
10000003 Pool_01 Rolling 5 day tournamnet
10000004 Pool_02 Rolling 3 day Tournament
10000005 Pool_03 One off 1 day Tournament

The first select returns returns a list of Tournaments they have joined using table Tbl_01035_Tour_Players filtered by the @MemberId And the TourEndDate >= todays date.

If a member isn't entered in a tournament then the second Select will list any TourTypeId that is not in select 1 also they could be a new user MemberId 10000018 and they have nothing in the Tbl_01035_Tour_Players table so therefore they should see all entries from the Tbl_01020_Tour_Types table

ALTER PROCEDURE [dbo].[test]

@_MemberId int

AS
BEGIN
SET NOCOUNT ON;

--Select 1
Begin
SELECT TourTypeId, MemberId, TourId, TourStartDate, TourEndDate
FROM Tbl_01035_Tour_Players
WHERE (MemberId = @_MemberId) AND (TourEndDate >= GETDATE())
End

--Select 2
begin
Select T2.*
From Tbl_01020_Tour_Types T2
Left Join
Tbl_01035_Tour_Players T1
on T2.TourTypeId = T1.TourTypeId
Where (T1.TourTypeId is Null)
end

nobby
Yak Posting Veteran

58 Posts

Posted - 2006-10-17 : 18:12:20

Jen, Harsh or anyone can you help with this......Thanxs

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-10-17 : 21:15:29

i already did...did the query not return the expected results?
just add your other filter in the where clause...


create procedure sprocName(@memberid int)
as

if not exists(select * from tableA where memberid=@memberid)
   select tourdescrip from tableB
else
   select tourdescrip from tableA t1
   join tableB t2
   on t1.tourtypeid=t2.tourtypeid
   where memberid=@memberid

--------------------
keeping it simple...

nobby
Yak Posting Veteran

58 Posts

Posted - 2006-10-17 : 22:14:30

Sorry Jen
I don't know what i am doing wrong but i can't get any of the examples to return what i want.
Here is a scenario

Using the data below if @_MemberId= 10000028

There is one entry for this member
Tbl_01035_Tour_Players
ID MemberId TourTypeId TourId TourStartDate TourEndDate
1 10000028 10000002 10000273 21/10/2006 00:00:00 30/10/2006 00:00:00

now because he already has a TourTypeId of 10000002 i only want to display The TourTypes that don't match Thus it should only return these 3.

Tbl_01020_Tour_Types

TourTypeId TourGame TourDescription
10000003 Pool_01 Rolling 5 day tournamnet
10000004 Pool_02 Rolling 3 day Tournament
10000005 Pool_03 One off 1 day Tournament

if a new user had no entries in the table Tbl_01035_Tour_Players then it needs to display all records from Tbl_01020_Tour_Types

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources