CODEWITHSUNDEEP
c#mysqljoin
DatenThielt
DatenThielt

Reputation: 61

Confusing MySql Join on 3 Tables

Morning all,

I am trying to get a 3 table joint to work, using Ef.net (Not core) and Linq but for the life of me cant get a result I am expecting.

The Scenario: We have a web page where people perform a lookup, These users are not logged in but their IP addresses are recorded against the Lookup.

Databases: Accounts (Contains AccountID, Company Name, EmailAddress) Lookups (Contains iPAddress, searchResult, dateSearched) iPAddresses (Contains AccountID, ipAddress)

The Requirement: I have been trying to create a query where I pass it an AccountID, and it tells me how many Lookups the account has done based on the iPAddresses they have searched with.

Attempted Code: The following is about as close as I thought I was to getting it working but still returns no results

public int getLookups(int accountID)
{
        var query = (from p in mySqlEntities.individual_lookups
                    join meta in mySqlEntities.ip_addresses on p.ipAddress equals meta.ip_address
                    where p.accountID == accountID
                    select p.id).Count();
        return query;
}

I'm not quite sure where I am going wrong here, but any help in the matter would be appreciated. Thank you!

Upvotes: 0

Views: 35

Answers (1)

Guy Kempsell
Guy Kempsell

Reputation: 61

If I'm reading you're question correctly, then these are your tables

Databases: Accounts (Contains AccountID, Company Name, EmailAddress) Lookups (Contains iPAddress, searchResult, dateSearched) iPAddresses (Contains AccountID, ipAddress)

Which means that mySqlEntities.individual_lookups doesn't have an accountId column, but your mySqlEntities.ip_addresses does.

Change your WHERE clause to the below and see if you get any further:

where meta.accountID == accountID

Upvotes: 1

Related Questions