Query - list of stolen vehicles + JOINS

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

Query - list of stolen vehicles + JOINS

Author

Topic

rdl
Starting Member

25 Posts

Posted - 2011-05-18 : 09:29:52

Hi All,
I have the following table which includes reportvehicleID (autonumber), report reference, vehicle registration number, relation (stolen, recovered) and date of relation.

ReportVehicleID ReportRef RegistrationNo Relation RelationDate
1 4 AAA342 Stolen 2011-02-02
2 5 BBS222 Stolen 2011-02-20
3 4 AAA342 Recovered 2011-05-17

Now I want a list of stolen vehicles i.e. if AAA342 has been recovered I don't want it in the report.
Thus the report would only show the BBS222.

Can someone help me through this pls cause I have been trying a lot.

THANKYOU FOR YOUR HELP.

Regards,
rdl

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-18 : 09:34:05

SELECT * FROM Table o WHERE relation = 'Stolen' AND NOT EXISTS (SELECT * FROM Table i WHERE o.VehicleID = i.VehicleID and relation = 'Recovered')

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-18 : 09:36:11

[code]
select *
from table1 t1
where t1.Relation = 'Stolen'
and not exists (select * from table1 x where x.RegistratioNo = t1.RegistrationNo and x.Relation = 'Recovered')
[/code]

KH
[spoiler]Time is always against us[/spoiler]

rdl
Starting Member

25 Posts

Posted - 2011-05-18 : 09:39:33

Dear khtan,

can you tell me what x is please cause I didn't understand that part. Please note that my table name is reportvehicle and all above data is stored in it (i.e. in one table).

rdl
Starting Member

25 Posts

Posted - 2011-05-18 : 09:40:15

quote:
Originally posted by khtan

select *
from table1 t1
where t1.Relation = 'Stolen'
and not exists (select * from table1 x where x.RegistratioNo = t1.RegistrationNo and x.Relation = 'Recovered')

KH
[spoiler]Time is always against us[/spoiler]

Please note that these are all in one table named 'reportvehicle'

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-18 : 09:41:11

quote:
Originally posted by khtan

select *
from table1 t1
where t1.Relation = 'Stolen'
and not exists (select * from table1 x where x.RegistratioNo = t1.RegistrationNo and x.Relation = 'Recovered')

KH
[spoiler]Time is always against us[/spoiler]

Brilliant

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-18 : 09:42:02

quote:
Originally posted by rdl

Dear khtan,

can you tell me what x is please cause I didn't understand that part. Please note that my table name is reportvehicle and all above data is stored in it (i.e. in one table).

Are you going to tell me you are incapable of cutting and pasting and changing the table name?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

rdl
Starting Member

25 Posts

Posted - 2011-05-18 : 09:46:13

Ok sorry as I just got confused with the x and table 1.
I did as follows:
SELECT *
FROM reportvehicle
WHERE reportvehicle.Relation = 'Stolen'
AND NOT EXISTS (SELECT * from reportvehicle
WHERE reportvehicle.RegistrationNo = reportvehicle.RegistrationNo AND
reportvehicle.Relation = 'Recovered')

HOWEVER RETURNING 0 RESULTS :(

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-05-18 : 09:52:05

You need to do some aliasing so it can distinguish between the references to the same table...

Also.. I would add the blue part to handle cases where vehicle is stolen more than once. (though probably unlikely)

quote:
Originally posted by rdl

Ok sorry as I just got confused with the x and table 1.
I did as follows:
SELECT *
FROM reportvehicle A
WHERE ~~reportvehicle~~A.Relation = 'Stolen'
AND NOT EXISTS (SELECT * from reportvehicle B
WHERE ~~reportvehicle~~B.RegistrationNo = ~~reportvehicle~~A.RegistrationNo AND ~~reportvehicle~~B.Relation = 'Recovered' AND B.RelationDate > A.RelationDate)

HOWEVER RETURNING 0 RESULTS :(

Corey

I Has Returned!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-18 : 09:52:48

quote:
Originally posted by X002548

quote:
Originally posted by khtan

select *
from table1 t1
where t1.Relation = 'Stolen'
and not exists (select * from table1 x where x.RegistratioNo = t1.RegistrationNo and x.Relation = 'Recovered')

KH
[spoiler]Time is always against us[/spoiler]

Brilliant

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

i copied yours

KH
[spoiler]Time is always against us[/spoiler]

rdl
Starting Member

25 Posts

Posted - 2011-05-18 : 09:55:34

SELECT *
THANKYOU VERY MUCH...IT WORKED :))))

FROM reportvehicle A
WHERE A.Relation = 'Stolen'
AND NOT EXISTS (SELECT * from reportvehicle B
WHERE B.RegistrationNo = A.RegistrationNo AND
B.Relation = 'Recovered')

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-18 : 09:55:44

quote:
Originally posted by Seventhnight

You need to do some aliasing so it can distinguish between the references to the same table...

Also.. I would add the blue part to handle cases where vehicle is stolen more than once. (though probably unlikely)

quote:
Originally posted by rdl

Ok sorry as I just got confused with the x and table 1.
I did as follows:
SELECT *
FROM reportvehicle A
WHERE ~~reportvehicle~~A.Relation = 'Stolen'
AND NOT EXISTS (SELECT * from reportvehicle B
WHERE ~~reportvehicle~~B.RegistrationNo = ~~reportvehicle~~A.RegistrationNo AND ~~reportvehicle~~B.Relation = 'Recovered' AND B.RelationDate > A.RelationDate)

HOWEVER RETURNING 0 RESULTS :(

Corey

I Has Returned!!

Corey...o and i for outer and inner

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-18 : 09:56:47

quote:
Originally posted by rdl

SELECT *
THANKYOU VERY MUCH...IT WORKED :))))

FROM reportvehicle A
WHERE A.Relation = 'Stolen'
AND NOT EXISTS (SELECT * from reportvehicle B
WHERE B.RegistrationNo = A.RegistrationNo AND
B.Relation = 'Recovered')

Good Job Corey....I can see where my code wouldn't have worked now

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-05-18 : 10:00:00

quote:
Originally posted by X002548

quote:
Originally posted by Seventhnight

You need to do some aliasing so it can distinguish between the references to the same table...

Also.. I would add the blue part to handle cases where vehicle is stolen more than once. (though probably unlikely)

quote:
Originally posted by rdl

Ok sorry as I just got confused with the x and table 1.
I did as follows:
SELECT *
FROM reportvehicle A
WHERE ~~reportvehicle~~A.Relation = 'Stolen'
AND NOT EXISTS (SELECT * from reportvehicle B
WHERE ~~reportvehicle~~B.RegistrationNo = ~~reportvehicle~~A.RegistrationNo AND ~~reportvehicle~~B.Relation = 'Recovered' AND B.RelationDate > A.RelationDate)

HOWEVER RETURNING 0 RESULTS :(

Corey

I Has Returned!!

Corey...o and i for outer and inner

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Yeah yeah...

You should see a large query with lots of joins and nesting...


From A
Join
	(
	From Z
	Join
		(
		From A
		Join B
		Join C
		Join D
		) Y
	Join 
		(
		From A
		Join B
		Join C
		Join D
		) X
	) B
...

Corey

I Has Returned!!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-05-18 : 10:02:36

You forgot your tags...

quote:
Originally posted by X002548

quote:
Originally posted by rdl

SELECT *
THANKYOU VERY MUCH...IT WORKED :))))

FROM reportvehicle A
WHERE A.Relation = 'Stolen'
AND NOT EXISTS (SELECT * from reportvehicle B
WHERE B.RegistrationNo = A.RegistrationNo AND
B.Relation = 'Recovered')

[sarcasm]
Good Job Corey....I can see where my code wouldn't have worked now
[/sarcasm]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Corey

I Has Returned!!

rdl
Starting Member

25 Posts

Posted - 2011-05-18 : 10:12:27

yeh you were all ok in your answers but i am new to sql.....and didn't understand but now ok :))

Thanks all for fast responses :)

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-18 : 12:20:17

I'm just bustin Corey...been a looooong time since I've heard from him...I've been kind on and off as well

ahem????


From A
Join
	(
	From Z
	Join
		(
		From A
		Join B
		Join C
		Join D
		) Y
	Join 
		(
		From A
		Join B
		Join C
		Join D
		) X
	) B

ON????

But I understand your point

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-05-18 : 13:15:41

I finally got to move to newer versions of SQL, so I returned.

No offense Brett, but I'm glad I don't work in the same dbs as you...

I don't enjoy SCREAMING at my server


SELECT someCol FROM someTable -- DO IT NOW! I SAID NOW!!

Corey

I Has Returned!!

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-18 : 13:27:56

None taken...thing fly's if that's what you're implying...

In any case you're onto R2 now?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

rdl
Starting Member

25 Posts

Posted - 2011-05-18 : 14:08:34

Hi,

I am finding it a problem to do joins with the below query.

I want to show the details of the owner in the stolen vehicles report.

I have the following:

Table 1: vehicle has fields RegistrationNo, ID (identity card of owner which is foreign key)

Table 2: person has fields ID, Surname, Name.

Can someone help me to include also the ID, Surname, Name of the owner please.

Thankyou.
rdl

quote:
Originally posted by Seventhnight

You need to do some aliasing so it can distinguish between the references to the same table...

Also.. I would add the blue part to handle cases where vehicle is stolen more than once. (though probably unlikely)

quote:
Originally posted by rdl

Ok sorry as I just got confused with the x and table 1.
I did as follows:
SELECT *
FROM reportvehicle A
WHERE ~~reportvehicle~~A.Relation = 'Stolen'
AND NOT EXISTS (SELECT * from reportvehicle B
WHERE ~~reportvehicle~~B.RegistrationNo = ~~reportvehicle~~A.RegistrationNo AND ~~reportvehicle~~B.Relation = 'Recovered' AND B.RelationDate > A.RelationDate)

HOWEVER RETURNING 0 RESULTS :(

Corey

I Has Returned!!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-05-18 : 14:32:02

Yeah, I changed jobs...

So here, our production is still 2005 at the moment, but dev is 2008 R2...

But either way, I get to play with some of the goodies since 2000.

Still trying to catch up to all of you guys...

quote:
Originally posted by X002548

None taken...thing fly's if that's what you're implying...

In any case you're onto R2 now?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Corey

I Has Returned!!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources