left-join

mysql joins are driving me mad!

So I've asked a couple of questions about performing joins and have had great answers, but there's still something I'm completely stumped by. I have 3 tables. Let us call them table-b, table-d and table-e. Table-b and table-d share a column called p-id. Table-e and table-b share a column called ev-id. Table-e also has a column called da...

How do you left join in Linq if there is more than one field in the join?

I asked a question earlier about why left joins in Linq can't use defined relationships; to date I haven't got a satisfactory response. Now, on a parallel track, I've accepted that I need to use the join keyword as if there were no relationship defined between my objects, and I'm trying to work out how to express my query in Linq. Trou...

How can I turn these LINQ joins into LEFT OUTER joins?

var auditAgencyRecords = (from ag in db.Agencies join ara in db.AuditRuleAccounts on ag.Agency_Id equals ara.AgencyID join arr in db.AuditRuleResults on ara.AuditRuleAccountID equals arr.AuditRuleAccountID join are in db.Audi...

Difference between Condition in Where and Condition in Join

Hi All Can anyone please explain to me why the following two queries yield different results? SELECT o.* FROM Customer c LEFT JOIN [Order] o ON o.CustomerID = c.CustomerID AND o.OrderType = 'Cash' WHERE c.Country = 'USA' SELECT o.* FROM Customer c LEFT JOIN [Order] o ON o.CustomerID = c.CustomerID WHERE ...

TSQL Left Join with multiple right hand rows

When you perform a left join in TSQL (MSSQL SERVER) is there any guarantee which row will return with your query if there are multiple rows on the right? I'm trying to use this to exploit an ordering on the right table. so Select ColA, ColB, ColC from T Left Outer Join (Select ColA, ColB, ColC from T--CLARIFIED, this is a sel...

Access Query Memo field truncation due to "distinct"

I am having problems running a query without either truncating the note field in NotesTbl or returning repeated entries. UID is not unique for AccessTbl. When I leave out "distinct" notes will return multiple times because I am joining with AccessTbl on a non-distinct condition. When I use distict, the note field is trunctated because...

Retrieve a list containing NULLS with Linq

Is it possible to use LINQ to retrieve a list that may contain nulls. For example if I have a left outer join like so: var query= from c in db.Customers join o in db.Orders on c.CustomerID equals o.CustomerID into sr from x in sr.DefaultIfEmpty() select x.OrderId; Ho...

SQL join multiple values from column into one cell

There are a ton of sql join q's already but I didn't see my answer so here goes . . . I am working with WPDB (Wordpress database)/EZSql/MySQL 5.0. Trying to achieve the 'simple' desired output below has not proven to be easy. Current output MemberID MemberName FruitName -------------- --------------------- --...

Create a matrix/table from a LEFT JOIN in SQL

I'd like to know a good solution for converting a LEFT JOIN into a table/matrix. For example, given this schema: objects id (integer) name (string) attributes id (integer) object_id (integer) name (string) value (string) And these values: 1,ball 2,box 1,1,colour,red 2,1,shape,sphere 3,1,material,rubber 4,2,colo...

sql: why does query repeat values when using 'GROUP CONCAT' + 'GROUP BY'?

The Query: SELECT MemberId, a.MemberName, GROUP_CONCAT(FruitName) FROM a LEFT JOIN b ON a.MemberName = b.MemberName GROUP BY a.MemberName Table a MemberID MemberName -------------- ---------- 1 Al 1 Al 3 A2 Table b MemberName...

django one-to-one left join is null?

I've got models like those in django: class User(models.Model): name = models.CharField(max_length = 128) class Message(models.Model): sender = models.ForeignKey(User, related_name = 'messages_sent') recipient = models.ForeignKey(User, related_name = 'messages_recieved') subject = models.CharField(max_length = 128) body = mod...

Nhibernate: left outer join on subquery

Update take 2 here is the two queries i'm working with (paging is omitted in both queries) i'd like to get the following query SELECT * FROM product LEFT OUTER JOIN ( SELECT * FROM Cart LEFT OUTER JOIN cartproducts ON Cart.Id = cartproducts.Cart_id WHERE Cart.username = 'user' ) AS CartFiltered ON produ...

Left Outer Join in Linq-To-Entities

Is there a way to do a left outer join in linq-to-entities WITHOUT having tables mapped with foreign keys? Also, if our company decides to stick with using linq-to-entities despite all of its current flaws, do you think it's important to get Visual Studio 2010? In other words, what is in Visual Studio 2010 that would help developing wit...

left join in rails/mysql

hi all, i am newbie in rails and try to perform left join in mysql. there are two objects - user and message. user has_and_belongs_to_many messages, message has_and_belongs_to_many users currently, by simply writing user.messages i get following query in console SELECT * FROM `messages` INNER JOIN `messages_users` ON `messages`.id ...

Filtering LEFT JOIN results

I have two tables: authorizations and settlements. 'Settlements' contains a forign key reference to authorizations. A settlement can also have a status (ERROR, ACCEPTED, etc). Given this data: Authorizations Settlements id id | auth_id | status ----- --------------------------- 1 ...

Linq to Entities Left Outer Join/ Subquery?

Hi All, I have a query (developing in linqpad): DateTime currentDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1); DateTime previousMonth = currentDate.AddMonths(-1); DateTime previousMonthForAveragePrices = currentDate.AddMonths(-2); var help = from cd in CostDrivers.OfType<Commodity>() where cd.isActive =...

How can I optimize this MySQL query that involves two left joins?

I cannot figure out why my query slows down. What it boils down to are four tables: team, player, equipment, and metadata. Records in player and equipment have a FK to team, making team a parent of player and equipment. And all three of those tables' rows each have a record in metadata which stores things like creation date, creator ...

MySQL complex JOIN problem - Unable to achieve proper results

I have two table: Vehicles(Id, VIN) and Images(Id, VehicleId, Name, Default). I need to select the vehicles VIN and its default picture to display in a table. The problem I am having is that if a default picture is not set I still would like to select an image to display if it exists. If no images exist the vehicle information obvious...

SQL Query: joining two fields from two separate rows

I have the following two tables: USER FID UID VALUE 4 3 John 3 3 Doe 4 4 Jack 3 4 Russel Should be fairly clear that FID 3 = Surname, and FID 4 = Name. DATEDATA UID DATE 3 1234 4 4321 I want to join these two tables, so that I end up with something like this: UID DATE NAME SUR...

MySQL complex query not yielding proper results

I have two table: Vehicles(Id, VIN) and Images(Id, VehicleId, Name, Default). I need to select the vehicles VIN and its default picture to display in a table. The problem I am having is that if a default picture is not set I still would like to select an image to display if it exists. If no images exist the vehicle information obvious...