I'm working on a Laravel/ Angular application, and have a form on one of pages displaying a table with information regarding a number of accounts. One of the columns in the table is titled 'Contact', and each cell in the column displays the name of the default contact for that table entry.
The cells in this column also display an 'Edit' button, which will open a dialog when clicked to allow the user to add another contact to that table entry- the dialog displays a form with the fields 'first name' 'surname' & 'email', and has an 'Add Contact' button.
There is also a short 'preview' of how the name should appear displayed in a letter generated to send to that user, i.e "Hi forename surname" or "Hi forename", depending on the the type of contact, and the letter template being used.
I'm having a bit of trouble getting the relevant name to be displayed, because I'm not sure how to write the SQL query I'll need to retrieve the relevant name.
The tables are set up such that the account.account table contains the values for:
'account ID', 'account name', account type ID',
and the account.property table contains the values for
'property ID', property label', 'property tag', 'property description'.
In the account.property table, there are two rows which hold the names to be used for different methods of communication- one with the 'property tag' addresseename, and the other with the 'property tag' addresseenamepdf. I want to display these values in the 'preview' on the dialog, but I'm not sure how to write the SQL to retrieve them.
As far as I can see, there is no primary/ foreign key relationship between the tables, but I have been told that they are linked using the View account.vAccount. It's been several years since I've done anything more than very basic database programming with SQL, and I haven't come across views before, so am unsure how to use them in a query...
In MS SQL SMS, if I right-click the `account.vAccount view, and select the top 1000 rows, I see that the query:
SELECT TOP (1000) [accountId]
,[name]
,[typeId]
,[accountType]
,[accountTypeTag]
,[typeParentId]
,[parentAccountType]
,[parentAccountTypeName]
,[balanceDate]
, // several other values here
FROM [myDB].[account].[vAccount]
is run, and am presented with a list of the results, which I can filter by adding a where clause to the end of the query:
FROM [myDB].[account].[vAccount] where accountId = 53092;
and this just returns the view row for that particular account. However, I can't see the addresseename & addresseenamepdf columns whose data I want to retrieve from the account.property table.
My (limited) understanding of SQL tells me that I will need to create a relationship between these two tables, in order to be able to query the addressename & addresseenamepdf values that I want using just the accountId value (i.e. have a foreign key from one of the tables in the other table).
Is my understanding correct here, or can I use the view to retrieve this data without creating a relationship between the tables? If I can, how would I do this?
--Edit--
So it seems I'd missed that these two tables are actually related through a third table (I've only just started working on this application)- the tables are:
account
propertyValue
property
The tables have the relationships:
account one-to-many propertyValue many-to-one property
How would I write a query that would return two particular properties (the properties whose propertyId values are 48 & 49 for an account where the accountId value is 53092?
I've tried running the query:
SELECT TOP (1000) [accountID]
,[name
...
FROM [myDB][account].[account] where accountID = 53092
INNER JOIN [myDB].[account].[propertyValue] where propertyTag = 'ADDRESSEENAME' | propertyTag = 'ADDRESSEENAMEPDF';
but when I try to execute, I get an error that says:
Incorrect syntax near the keyword 'INNER'.
Why am I getting this? What am I doing wrong?
Structure of the query should be
Select
From
join on
where
group by
Having.
You have the where clause, before the join clause.
Change to:
SELECT TOP (1000) [accountID]
,[name
...
FROM [myDB][account].[account]
INNER JOIN [myDB].[account].[propertyValue] ON … (you are missing the on clause)
where accountID = 53092 and (
propertyTag = 'ADDRESSEENAME' | propertyTag = 'ADDRESSEENAMEPDF');