Not really VB - more SQL but.....

[ca8msm]

I basically need to change an SQL statement to include data from 4 tables - all of which may or may not contain a record relating to the user who logged in. I need to set it up via 1 SQL statement and not by using several data controls or adodc connections.

The data is coming from an Access 2000 database containing 4 tables:

Details (Primary key = user)
Notes (Primary key = noteid)
Vehicle (Primary key = regno)
History (Primary key = bookingid)

The relationships between these are:

Details to Vehicle = Field named user
Details to Notes = Field named user
Vehicle to History = Field named regno

So far I can view data from the Details table and the Notes table when a user logs in by using this statement:

sqltemp="select * from details left outer join notes " & _
" on details.USER = notes.USER where details.USER = '"
sqltemp=sqltemp & myname & "'"

(i.e Notes field is sometimes blank)

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!

[forty7]

This one depends. Is there any of the 4 that always has data for the user? I'm gonna assume yes for Details, since it has the primary key of user. If I'm wrong, this won't work.

SELECT * FROM details LEFT OUTER JOIN notes ON details.user = notes.user LEFT OUTER JOIN vehicle ON details.user = vehicle.user LEFT OUTER JOIN history ON vehicle.regno = history.regno WHERE details.user =

I'll keep an eye out to see if this worked for you.

[Wak]

I'm not too sure hey, cause like I'm really lazy, and I hate to think heaps. But I'll give it a go.


I think what you're looking for is a double Inner Join. Not too sure.

SELECT * FROM ((DETAILS INNER JOIN VEHICLES ON DETAILS.user  = VEHICLES.user) INNER JOIN NOTES ON VEHICLES.user = NOTES.user) INNER JOIN HISTORY ON DETAILS.user = HISTORY.user;

I must warn you I haven't actually tried a triple join before, but I have a double join, and in theory it shoudl work.

[ca8msm]

Sounds good but I have been told that

"the result will be all combinations from the 4 tables that matches. For instance, if a user has 5 matching rows in each of the other three tables, you will end up with 5*5*5 = 125 rows for that user only. So maybe you should pull the historic data in a separate query"

Does this sound right? And are there any ways around it?

[forty7]

It sounds like you may need to rethink the database structure.
If the notes are actually just associated with the user, and the vehicle can be one to many, it's correct for you to get the 125 records.
example:
userA has 2 notes 2 vehicles (one of which has 2 history records)
userA note1 vehicle1 hist1
userA note1 vehicle1 hist2
userA note1 vehicle2
userA note2 vehicle1 hist1
userA note2 vehicle1 hist1
userA note2 vehicle2
How do you know which note goes with which vehicle?

How is this data being displayed? Maybe there is another solution.

[ca8msm]

The notes arent related to the vehicle. sorry havent really explained well.

Have attached copy of relationships. Would you please look at it and that will explain a lot better than I can

[ca8msm]

I tried this as was told before:



sqltemp = "select * from details left outer join notes " & _ "on details.USER = notes.USER left outer join vehicle " & _ "on notes.USER = vehicle.USER left outer join history " & _ "on vehicle.REGNO = history.REGNO " & _ "where details.USER = '" sqltemp=sqltemp & myname & "'"

and I get an error. i.e.

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'details.USER = notes.USER left outer join vehicle on notes.USER = vehicle.USER left outer join history on vehicle.REGNO = history.REGNO'.
/securityloginrespond.asp, line 24

Any ideas?

[forty7]

Yes. They aren't related, which is why you have the problem. There's no way to know by username alone which record to give.
That's why i asked how you wanted to display the data.

[ca8msm]

Ok sorry...

What happens is the user logs in and sees details about themselves (i.e the details table), notes about themselves (because user is linked between the two tables - (details and notes)).

They would also see details about their vehicles (again user is linked between details table and vehicle table). Finally they could click on a link to view history of a particular vehicle (by clicking on Reg No).

Am I explaining well or not?

[forty7]

Sorry about the SQL syntax. That syntax seems to work for me. You might try.
sqltemp = "select * from notes right outer join details " & "on notes.USER = details.USER left outer join vehicle " & "on details.USER = vehicle.USER left outer join history " & "on vehicle.REGNO = history.REGNO " & _ "where details.USER = '" & myname & "'"

you may have to add in some parens in places. i never use em, but whatever is parsing for you may require it.

[forty7]

Yes, much better explanation.
Is the user able to edit any of this info?

if not i'd use a treeView (for drill down effect).
i'd have use separate functions that do the following.

main
getdetails(currentuser)

getDetails(user as string)
'fill detail into tree view
if find details then
getNotes(user, treeRow)
getVehicles(user, treeRow)

getNotes(user as string, treeRow) ' treeRow would be an index into the treeview
'fill in each note record

getVehicles(user as string, treeRow)
'for each vehicle
'fill in vehicle record
getHistory(regNo)

getHistory(regNo as string)
'fill in the history records



This is excruciating. Do you have msn messenger?