SQL queryproblem - 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

SQL queryproblem

Author

Topic

rdl
Starting Member

25 Posts

Posted - 2011-05-21 : 10:01:13

Hi All,
I have the following query:

SELECT * FROM report, reportperson, reportofficer
WHERE
report.ReportRef = reportperson.ReportRef
AND
reportperson.ReportRef = reportofficer.ReportRef

Now I have another table 'reportvehicle' which holds stolen vehicles in it.
Note that a report can have a vehicle to it or not (not necessarily).
Now I need to get also details from the report vehicle details to this query..obviously where there is a vehicle involved.

I reasoned it this way...adding to the above query...

AND WHERE EXISTS
(SELECT * from reportvehicle
WHERE report.Ref = reportvehicle.ReportRef)

however i am getting a syntax error...

Thankyou for your help,
rdl

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-21 : 11:46:29

quote:
Originally posted by rdl

Hi All,
I have the following query:

SELECT * FROM report, reportperson, reportofficer
WHERE
report.ReportRef = reportperson.ReportRef
AND
reportperson.ReportRef = reportofficer.ReportRef

Now I have another table 'reportvehicle' which holds stolen vehicles in it.
Note that a report can have a vehicle to it or not (not necessarily).
Now I need to get also details from the report vehicle details to this query..obviously where there is a vehicle involved.

I reasoned it this way...adding to the above query...

AND ~~WHERE~~ EXISTS
(SELECT * from reportvehicle
WHERE report.Ref = reportvehicle.ReportRef)

however i am getting a syntax error...

Thankyou for your help,
rdl

No, you're never too old to Yak'n'Roll if you're too young to die.

rdl
Starting Member

25 Posts

Posted - 2011-05-21 : 13:45:48

Hi,
did it like that, but I get only the reports which have a vehicle in them. I have some reports which do not have a vehicle. and I want to display all report details . i.e. those which don't have a vehicle show only the report, officer and person details.

Let me explain better - list of tables and important fieldnames

Table 'report': ReportRef
Table 'vehicle': RegistrationNo
Table 'person': ID
Table 'officer': OfficerID

Table 'reportvehicle': ReportRef, RegistrationNo
Table 'reportperson': ID
Table 'reportofficer': Officer ID

Hope someone helps me please.

Thanks,
rdl

rdl
Starting Member

25 Posts

Posted - 2011-05-21 : 13:47:02

************* PLEASE USE THIS FOR TABLES AS I've forgotten 2 foreign keys in my last post*********

Hi,
did it like that, but I get only the reports which have a vehicle in them. I have some reports which do not have a vehicle. and I want to display all report details . i.e. those which don't have a vehicle show only the report, officer and person details.

Let me explain better - list of tables and important fieldnames

Table 'report': ReportRef
Table 'vehicle': RegistrationNo
Table 'person': ID
Table 'officer': OfficerID

Table 'reportvehicle': ReportRef, RegistrationNo
Table 'reportperson': ReportRef, ID
Table 'reportofficer': ReportRef, Officer ID

Hope someone helps me please.

Thanks,
rdl

rdl
Starting Member

25 Posts

Posted - 2011-05-21 : 13:48:30

I want to show all details of table report, reportvehicle, reportperson, reportofficer where they all join on ReportRef; however not every report has a vehicle!

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2011-05-21 : 14:41:50

This should work

-- left join will bring back all rows in report tables and only those that match in the reportvehicle table

SELECT * FROM report r inner join reportperson rp
on r.ReportRef = reportperson.ReportRef
inner join reportofficer ro
on reportperson.ReportRef = reportofficer.ReportRef
left join reportvehicle
on report.Ref = reportvehicle.ReportRef

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2011-05-21 : 14:43:47

I forgot to update my alias table names in the join

SELECT * FROM report r inner join reportperson rp
on r.ReportRef = rp.ReportRef
inner join reportofficer ro
on rp.ReportRef = ro.ReportRef
left join reportvehicle rv
on r.Ref = rv.ReportRef

rdl
Starting Member

25 Posts

Posted - 2011-05-21 : 22:16:17

THANKS IT WORKED :)))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources