Database relationship for tables
-
Now the GlobalEntityID is a seperate table that holds the company demographics. So each company can have multiple license attached to company. They can also have multiple invoice attached to company. My question is the end user wants a report that shows company info, license number and invoice attached to each license number. But It does not work cause the invoice is attache to the company not each individual license number. Is it possible to create get data for each license number when invoices are related to the company table only and not the license number. The report needs to show license numbers with or with out an invoice? When I run the report in SQL Query I can see same invoices for all license numbers, but in the user application I can pull a license number up and see that there is no invoice attached to it. How is this possible when there is no relationship between one license to one invoice? License Table Fields Invoice Table Fields PK LICENSEID PK INVOICEID LICENSENUMBER CASTATUSID ISSUEDBY INVOICENUMBER LICENSETYPEID GLOBALENTITYID LICENSESTATUSID INVOICETOTAL ISSUEDATE INVOICEDATE EXPIRATIONDATE INVOICEDESCRIPTION LASTCHANGEDON ROWVERSION LASTCHANGEDBY LASTCHANGEDON ROWVERSION LASTCHANGEDBY GLOBALENTITYID INVOICEDUEDATE LICNESEPARENTID DISTRICTID LICENSECLASSIFICATIONID APPLIEDDATE LASTRENEWALDATE LICENSEYEAR LICENSEGROUPID DESCRIPTION GLOBALENTITYACCOUNTID CALENDARDUEDATE ISAPPLIEDONLINE IMPORTEDCUSTOMER
-
Now the GlobalEntityID is a seperate table that holds the company demographics. So each company can have multiple license attached to company. They can also have multiple invoice attached to company. My question is the end user wants a report that shows company info, license number and invoice attached to each license number. But It does not work cause the invoice is attache to the company not each individual license number. Is it possible to create get data for each license number when invoices are related to the company table only and not the license number. The report needs to show license numbers with or with out an invoice? When I run the report in SQL Query I can see same invoices for all license numbers, but in the user application I can pull a license number up and see that there is no invoice attached to it. How is this possible when there is no relationship between one license to one invoice? License Table Fields Invoice Table Fields PK LICENSEID PK INVOICEID LICENSENUMBER CASTATUSID ISSUEDBY INVOICENUMBER LICENSETYPEID GLOBALENTITYID LICENSESTATUSID INVOICETOTAL ISSUEDATE INVOICEDATE EXPIRATIONDATE INVOICEDESCRIPTION LASTCHANGEDON ROWVERSION LASTCHANGEDBY LASTCHANGEDON ROWVERSION LASTCHANGEDBY GLOBALENTITYID INVOICEDUEDATE LICNESEPARENTID DISTRICTID LICENSECLASSIFICATIONID APPLIEDDATE LASTRENEWALDATE LICENSEYEAR LICENSEGROUPID DESCRIPTION GLOBALENTITYACCOUNTID CALENDARDUEDATE ISAPPLIEDONLINE IMPORTEDCUSTOMER
The short answer is: No it can't be done, unless you have some other data that allows you to backtrack the relations. There should be a table for the invoice rows, what content does that have?
Wrong is evil and must be defeated. - Jeff Ello
-
Now the GlobalEntityID is a seperate table that holds the company demographics. So each company can have multiple license attached to company. They can also have multiple invoice attached to company. My question is the end user wants a report that shows company info, license number and invoice attached to each license number. But It does not work cause the invoice is attache to the company not each individual license number. Is it possible to create get data for each license number when invoices are related to the company table only and not the license number. The report needs to show license numbers with or with out an invoice? When I run the report in SQL Query I can see same invoices for all license numbers, but in the user application I can pull a license number up and see that there is no invoice attached to it. How is this possible when there is no relationship between one license to one invoice? License Table Fields Invoice Table Fields PK LICENSEID PK INVOICEID LICENSENUMBER CASTATUSID ISSUEDBY INVOICENUMBER LICENSETYPEID GLOBALENTITYID LICENSESTATUSID INVOICETOTAL ISSUEDATE INVOICEDATE EXPIRATIONDATE INVOICEDESCRIPTION LASTCHANGEDON ROWVERSION LASTCHANGEDBY LASTCHANGEDON ROWVERSION LASTCHANGEDBY GLOBALENTITYID INVOICEDUEDATE LICNESEPARENTID DISTRICTID LICENSECLASSIFICATIONID APPLIEDDATE LASTRENEWALDATE LICENSEYEAR LICENSEGROUPID DESCRIPTION GLOBALENTITYACCOUNTID CALENDARDUEDATE ISAPPLIEDONLINE IMPORTEDCUSTOMER
There are definitely other tables in the mix, as is clear from the License table. I suspect that you can walk a relationship between those. All of those ID fields point to something, chase them down. My suggested starting points are LICENSETYPEID, LICENSESTATUSID, and LICENSECLASSIFICATIONID. Keep in mind that you may need to join on GLOBALENTITYID to get useful results.