0

SQL Database Tables IMAGE

In this relationship, Contacts may have different phone numbers, addresses and email addresses.

I need a query that shows [Contact Details][Telephone Number][Email][Address]etc.

I dont want to show all of the related numbers or emails etc. I would like to show just the default Number/Email/address.

So far, This is what I have:

var contacts =
            from con in dc.Contacts_Tables
            join com in dc.Companies_Tables on con.Company_ID equals com.Company_ID into comp from compa in comp.DefaultIfEmpty() 
            join em in dc.Email_Tables on con.Contact_ID equals em.Contact_ID into ema from emai in ema.DefaultIfEmpty()  
            join ad in dc.Contact_Address_Tables on con.Contact_ID equals ad.Contact_ID into add from addr in add.DefaultIfEmpty()
            join tel in dc.Contacts_Telephone_Tables on con.Contact_ID equals tel.Contact_ID into tele from telep in tele.DefaultIfEmpty() 

            orderby con.Contact_FirstName
            select new
            {
                ID = con.Contact_ID,
                FirstName = con.Contact_FirstName,
                MidName = con.Contact_Midname,
                Surname = con.Contact_Surname,
                Company = compa.Company_Name,
                Telephone = telep.Telephone_Number,
                Email = emai.Email_Address,
                Address = addr.Address,
                Notes = con.Notes
            };

This query will show more than a row for contacts who have more than one telephone associated etc.

How can I filter the query so it shows those with "Telephone_Default_ID" even if this column is empty?

Any suggestion is appreciated.

AJ GS
  • 11
  • 4
  • can you show the actual and expected result? – Ehsan Sajjad Jul 06 '18 at 13:54
  • Sure. Here is the link to the picture: https://drive.google.com/file/d/1o83cWOXRAmi8-yYY7WJXZUuI0MiYO2vT/view?usp=sharing The expected result is one row per contact. If I query the default telephone / email / address, it should show only one row (even if the defaults are empty) – AJ GS Jul 06 '18 at 14:04
  • you should join then on Default_Telephone_Id column – Ehsan Sajjad Jul 06 '18 at 14:12
  • Could you please give me an example on how to do it? (There is no need include all the tables for the example.) Thank you in advance – AJ GS Jul 06 '18 at 14:21
  • See my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786), particularly point 6, use anonymous objects for multiple conditions (e.g. `on new { con.Contact_ID, Tel = con.Telephone_Default_ID } equals new { tel.Contact_ID, Tel= tel.Telephone_Number }`). – NetMage Jul 06 '18 at 17:34

0 Answers0