5
SQL INNER JOIN with the WHERE clause in LINQ format
source link: https://www.codesd.com/item/sql-inner-join-with-the-where-clause-in-linq-format.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
SQL INNER JOIN with the WHERE clause in LINQ format
advertisements
How would I convert this sql query to LINQ?
SELECT company.ticker, company.primary_analyst,
personnel.last_name, company.research_associate,
company.secondary_associate, company.coverage_status
FROM company
INNER JOIN personnel ON company.primary_analyst = personnel.dpinitials
WHERE personnel.last_name='marley' AND company.associate='ml'
ORDER BY company.coverage_status
It's pretty similar:
var results = from c in company
join p in personnel on c.primary_analyst equals p.dpinitals
where p.last_name == 'marley' and c.associate == 'ml'
orderby c.coverage_status asc
select new
{
c.ticker, c.primary_analyst, p.last_name, c.research_associate,
c.secondary_associate, c.coverage_status
};
Above projects to an anonymous class with the properties you want - if you have an equivalent POCO class in your model you should project to that, if not in many cases you probably should create one.
Related Articles
GROUP_CONCAT and INNER JOIN with the WHERE clause
I've got a problem with getting data. I would like to find all courses, which contain the flag in where clause and get all flags course it contains. Records for the course table: +----+----------+ | id | name | +----+----------+ | 1 | Spring | | 2 |
Is there a logical difference between placing a condition in the ON clause of an inner join and the where clause of the main query?
Consider these two similar SQLs (condition in ON clause) select t1.field1, t2.field1 from table1 t1 inner join table2 t2 on t1.id = t2.id and t1.boolfield = 1 (condition in WHERE clause) select t1.field1, t2.field1 from table1 t1 inner join table2 t2
MySQL Inner Join with the IN clause
Is this sentence optimized? select d.id, d.wfid, d.docid , k.keyword, k.value from keywords k inner join documents d where k.document_id in (23,24) AND k.document_id = d.id; I have some ID's via POST (23,24) and I need to get the info for only them,
Left Join with the Where clause returns an empty result
I have got a post table and it's schema is like this: CREATE TABLE IF NOT EXISTS `post` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(20) DEFAULT NULL, `site_id` bigint(20) DEFAULT NULL, `parent_id` bigint(20) DEFAULT NULL, `title` long
Left join with the where clause for the right table (Must return NULL from the right) - Oracle
I have 2 tables ID and Comm. The tables are as follows ID AppID Name 1 James 2 John . . 100 Jeff Comm AppID Comment 1 abc 1 def 1 pqr 2 abc 2 def 2 pqr 3 def I want all appID from ID (First table) and from Comm(Second table) I want only those comment
SQL Query does not return rows when count (*) = 0 with the WHERE clause
I'm trying to write a query that returns the name of the school, the year of the payment, the cost of the payment and the number of students there was the year of the payment. The problem I encounter is that for a certain year there is 0 student so t
IN Clause with the WHERE clause does not get the appropriate results in SQL Server
SELECT Col1, Col2, Col3, Col4 FROM Table1 WHERE User1 = @Owner AND group1 = @Group AND date1 BETWEEN @startDate AND @endDate AND Mail LIKE @email AND def IN (CASE @InvoiceMethod //Problem is Here WHEN '' THEN def ELSE (@InvoiceMethod) END) A piece of
How to combine LEFT JOIN and the Where clause with JPQL?
I have two JPA entities : Schedule (containing a list of reservations) Reservation (containing a Date field : Date resDate) My goal is to only retrieve reservations matching a date parameter (planningDate) while retrieving all schedules no matter if
Why MySQL LEFT JOIN returns & ldquo; & rdquo; logs when with the WHERE clause?
Today I've tried some more complex MySQL queries and I've noticed that MySQL's LEFT JOIN is not working with WHERE clause. I mean, it does return some records but it does not return the ones which are empty on the right side. For example let's say we
Filter the SQL SELECT statement with a WHERE clause
I have this code a ripped somewhere but I am having issues trying to filter the results using a WHERE clause. What I would like to do is include a WHERE clause like WHERE name='John' in the statement to get only specific records associated with a per
Oracle SQL - How to build the where clause with optional search parameters
There are four field in the page lets say EMPLOYEE ID DEPT LOCATION UNIT: User might enter any of the field values all are optional, if he enter EMPLOYEE ID then the query should return rows related to that EMPLOYEE ID. If he enters only LOCATION the
Sql Query to retrieve single lines with the where clause and display the number of rows
I have a table with following fields:- device_name, system_name, alert_text, direction, arrival_time, ack_time, ack_status, Currentval_analog, Currentval_digital, Lower_limit, Upper_limit I want to write a query which retrieves the unique or distinct
(Firebird) SQL: 100x constraints in the WHERE clause make the query extremely slow
I'm using Firebird and created a table, called EVENTS. The columns are: id (INT) | name (VARCHAR) | category (INT) | website (VARCHAR) | lat (DOUBLE) | lon (DOUBLE) Now a user wants to search for events in a certain radius around him, but entered onl
Optimizing queries (MySql / Sql): moving functions outside the where clause
UPDATE : For anyone reading this , as @dnoeth stated below indexing makes a huge difference when it comes to query time. In some cases it cut row scans down to 1 row. You just have to pick the flavor of indexing that suits you best . Clustered vs Non
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK