Im trying to create a views relationship with a few issues.
i have 2 database tables, clients, and client revisions, every time the client is updated a new revision is created and the client revisions table is updated with the latest id of the revision
what i need to do is create a relationship with the clients and client revisisions tables to perform a left join the client id and the revision id.
my tables are like this
cid | vid | First Name | Last Name
1 1 Nick test
1 2 Nick Test
cid | vid
as you can see the revisions table says that the clients revision is 2 where cid is 1, meaning that the client table has been updated and the vid is the most recent entry to display.
the query should be something like
join clients on revisions.vid = client.vid and revisions.cid = client.cid
i will keep looking but a nudge in the right direction would be helpful