SQL Statement

<i><b>Originally posted by : Mark Smith</b></i><br />I have a .asp page that I am using to show users data once they log in. The data is coming from an Access 2000 database containing 4 tables:<br /><br />Details (Primary key = user)<br />Notes (Primary key = noteid)<br />Vehicle (Primary key = regno)<br />History (Primary key = bookingid)<br /><br />The relationships between these are:<br /><br />Details to Vehicle = Field named user<br />Details to Notes = Field named user<br />Vehicle to History = Field named regno<br /><br />So far I can view data from the Details table and the Notes table when a user logs in by using this statement:<br /><br />sqltemp="select * from details left outer join notes " & _ <br /> " on details.USER = notes.USER where details.USER = '" <br />sqltemp=sqltemp & myname & "'"<br /><br />(i.e Notes field is sometimes blank)<br /><br />I am having trouble modifying this statement that would allow me to view details from Vehicle and the History table for the user that logged in. Can anyone help with the SQL statement as I really am stuck on this one!<br /><br />Thanks<br />Mark<br /><br />

<i><b>Originally posted by : Mage (mage@baltnet.ru)</b></i><br />Hi, Mark!<br /><br />Try to make it simple! As far as I understand you need all the data related to one user from all your 4 tables. If so try this:<br /><br />SELECT * FROM details,notes,vihicle,history WHERE vihicle.user='" & myname & "' AND vihicle.regno=history.regno AND details.user='" & myname & "' AND notes.user='" & myname & "'"<br /><br />And LEFT JOIN doesn't need to be OUTER - it's the same: LEFT JOIN == LEFT OUTER JOIN<br /><br /><br />Hope that helps (if it doesn't write again :-)<br /><br />Mage<br /><br /><br /><br />------------<br />Mark Smith at 4/11/2001 5:09:24 PM<br /><br />I have a .asp page that I am using to show users data once they log in. The data is coming from an Access 2000 database containing 4 tables:<br /><br />Details (Primary key = user)<br />Notes (Primary key = noteid)<br />Vehicle (Primary key = regno)<br />History (Primary key = bookingid)<br /><br />The relationships between these are:<br /><br />Details to Vehicle = Field named user<br />Details to Notes = Field named user<br />Vehicle to History = Field named regno<br /><br />So far I can view data from the Details table and the Notes table when a user logs in by using this statement:<br /><br />sqltemp="select * from details left outer join notes " & _ <br /> " on details.USER = notes.USER where details.USER = '" <br />sqltemp=sqltemp & myname & "'"<br /><br />(i.e Notes field is sometimes blank)<br /><br />I am having trouble modifying this statement that would allow me to view details from Vehicle and the History table for the user that logged in. Can anyone help with the SQL statement as I really am stuck on this one!<br /><br />Thanks<br />Mark<br /><br />