Following query runs well in MySQL 5.x SELECT m_area.id, m_area.cn_areaName, m_area.de_areaName, m_area.en_areaName,m_area.jp_areaName,t_shop.count FROM m_area left join ( select t_shop.areaID, count(areaID) AS count from t_shop group by t_shop.areaID ) t_shop on m_area.id = t_shop.areaID However, when I have to run it in a 4...
Hi folks, I have two tables in the clients mssql database. The first is a job table - so I created an Job entity which contains the load type and load weight and all that stuff - works fine. My problem now is that there is a second table that includes informations about the load and unload point. The second table, I call it JEP, has a...
I'm not sure if this is possible from with a SQL query, but I'll give it a go. I'm developing a SharePoint web part in C# that connects to a SQL database and runs a query, then databinds that result set to a gridview. It's working fine, but I have a small snag. For the most part, my query will return exactly one result for every field. ...
Will do my best to describe the problem Im having :) Each thread/topic in my forum represents one disc. Registered members of the forum use a series of checkboxes (one displayed next to each disc) to tick each disc that they have in their collection. When the form is $_POST'ed it stores the information in a table like so: | user_id - ...
We are having a debate in our company. I use left outer joins quit frequently. I was told by another programmer that there is too much over head and I should not use them. Example, Lets say I have two tables, they would rather me hit the database twice get the information I need from each table store it in memory and join the data on ...
Given a database with two tables X and Y, I have a query that should LEFT JOIN the two tables on attributes X.a1 and Y.b1. I used the following query: SELECT X.a1, X.a2, Y.b1, Y.b2 FROM X LEFT JOIN Y ON (X.a1 = Y.b1) I thought that would be good enough to work, even if Y is currently an empty table. However, the query breaks because...
I have the following Access 2002 query I'm running through OLE DB in .NET: SELECT TOP 25 tblClient.ClientCode, tblRegion.Region FROM (tblClient LEFT OUTER JOIN tblRegion ON tblClient.RegionCode = tblRegion.RegionCode) ORDER BY tblRegion.Region There are 431 records within tblClient that have RegionCod...
Hi Everyone, I've been at this for a bit now. Basically, I'm needing to add a derived column to count the hits to a weblog entry in the database. The problem is, the hits are being totaled and shown on only on the first record. Any Ideas? I've emboldened the parts of the query I'm talking about. The query is below: SELECT DISTINCT(t.en...
I'd like to know if having to conditionals when using a JOIN keyword is a good practice. I'm trying to filter this resultset by date but I'm unable to get all the branches listed even if there's no expense or income for a date using a WHERE clause. Is there a better way of doing this, if so how? SELECT Branches.Name ,SUM(Expenses....
I'm stuck on a query with a join. The client's site is running mysql4, so a subquery isn't an option. My attempts to rewrite using a join aren't going too well. I need to select all of the contractors listed in the contractors table who are not in the contractors2label table with a given label ID & county ID. Yet, they might be listed i...
This is fine, it produces a left join var q = from c in categories join p in products on c equals p.Category into ps from p in ps.DefaultIfEmpty() select new { Category = c, ProductName = p == null ? "(No products)" : p.ProductName }; But what about if I wanted to do something like this: ... on p.date between c.s...
I am trying to do a join with a sub query and can't seem to get it. Here is what is looks like working in sql. How do I get to to work in linq? SELECT po.*, p.PermissionID FROM PermissibleObjects po INNER JOIN PermissibleObjects_Permissions po_p ON (po.PermissibleObjectID = po_p.PermissibleObjectID) INNER JOIN Permissions p ON (po_p.P...
Hi All, I have an Entity model with Invoices, AffiliateCommissions and AffiliateCommissionPayments. Invoice to AffiliateCommission is a one to many, AffiliateCommission to AffiliateCommissionPayment is also a one to many I am trying to make a query that will return All Invoices that HAVE a commission but not necessarily have a related...
I have the following linq query from o in context.Opportunities join i in context.Interactions on o.OpportunityID equals i.OpportunityID into ints from i in ints.DefaultIfEmpty() orderby i.StatusID descending, o.StatusID descending select o Now i want to then do a...
Hi, In mysql i m selecting from a table shouts having a foreign key to another table named "roleuser" with the matching column as user_id Now the user_id column in the shouts table for some rows is null (not actually null but with no inserts in mysql) How to show all the rows of the shouts table either with user_id null or not I m ...
In sql server 2008, I have the following query: select c.title as categorytitle, s.title as subcategorytitle, i.title as itemtitle from categories c join subcategories s on c.categoryid = s.categoryid left join itemcategories ic on s.subcategoryid = ic.subcategoryid left join items i on ic.itemid = i.itemid and i.site...
I've got a scenario where I want to switch on two different tables in an outer join. It goes something like this:- select mytable.id, yourtable.id from mytable left outer join (case when mytable.id = 2 then table2 yourtable on table1.id = table2.id ...
I've an old table like this: user> id | name | address | comments And now I've to create an "alias" table to allow some users to have an alias name for some reasons. I've created a new table 'user_alias' like this: user_alias> name | user But now I have a problem due my poor SQL level... How to join both tables to generate somethin...
Hi! I'm looking to translate this SQL statement to a well working & performant LINQ command. I've managed to have the first count working using the grouping count and key members, but don't know how to get the second count. select main.title, count(details.id) as details, count(messages.id) as messages from main left outer join detail...
I'm trying to do a left join in subsonic 3 using linq but it doesn't seem to work, I get a big error. var post = from p in Post.All() join q in Quote.All() on p.ID equals q.PostID into pq where p.ID == id.Value from qt in pq.DefaultIfEmpty() select n...