left-join

MYSQL Select query to return difference in row data

Hi, I have an example table as below that I'm trying to do analyse upon, to learn using PHP and MYSQL for producing statistical results; TableID | RNGResult | sessionseq | sessionid | Date | Scriptname 1 | 1 | 1 | 1 2 | 7 | 2 | 1 3 | 2 | 3 | 1 4 | 12 | 4...

Improving performance on a view with a LOT of joins...

I have a view that uses 11 outer joins and two inner joins to create the data. This results in over 8 million rows. When I do a count (*) on the table it takes about 5 minutes to run. I'm at a loss as to how to improve the performance of this table. Does anyone have any suggestions on where to begin? There appear to be indexes on al...

MySQL: Removing duplicate columns on Left Join, 3 tables

I have a table that uses 3 foreign keys into other tables. When I perform a left join, I get duplicate columns. MySQL says that the USING syntax will reduce the duplicate columns, but there aren't examples for multiple keys. Given: mysql> describe recipes; +------------------+------------------+------+-----+---------+-------+ | Fiel...

Subsonic 3 LeftOuterJoin

I have a problem. I want to create sql question select * from Firma left outer join FirmaAdres on FirmaAdres.Typ = 1 and FirmaAdres.FirmaID = Firma.ID in subsonic I have written SelectColumns("Firma.*").From().LeftOuterJoin(FirmaTable.IDColumn, FirmaAdresTable.FirmaIDColumn).And(FirmaAdresTable.AdresTypIDColumn).IsEqualTo(1) When I...

DataBinding to a related boolean column, many-to-many relationship

This is a WinForms application. There is a many-to-many relationship between the rows in TABLEA and TABLEB. There is a record in TABLELINK for every row in TABLEA that is related to a row in TABLEB. Think "users" and "roles" or something similar, with each TABLELINK record indicating "user X has role Y." I'm browsing through TABLEA rec...

LEFT JOINing on additional criteria in MS Access

I have the following T-SQL query (a simple test case) running fine in MS SQL but cannot get the equivalent query in MS Access (JET-SQL). The problem is the additional criteria in the LEFT JOIN. How can I do this in MS Access? T-SQL: SELECT * FROM A LEFT OUTER JOIN B ON A.ID = B.A_ID AND B.F_ID = 3 JET-SQL (what I h...

Mysql scenario - Get all tasks even if there is no entry?

Hi Gurus, I have three tables Tasks with columns Taskid, Taskname TaskAllocations with columns Taskid, EmpNum TaskEntries with columns TaskId, EmpNum, WorkedDate, Hoursspent Now I want to get all the task entries along a particular week. Here my problem is even if there is no Taskentry for a particular task I should get atleast a r...

Joining tables in MySQL question (php)

Hi, Situation: In my database I have a table called 'artists' and 'tags'. Each artist has a set of tags, saved in a linking table 'artisttags'. Each unique tag is saved in a table called 'tags'. Problem I would like to show all artists that have one (or more) tags in common with a given artist. function getSimilarArtists($artist_...

Linq, Left Join and Dates...

So my situation is that I have a linq-to-sql model that does not allow dates to be null in one of my tables. This is intended, because the database does not allow nulls in that field. My problem, is that when I try to write a Linq query with this model, I cannot do a left join with that table anymore because the date is not a 'nullable...

why does DefaultIfEmpy throw NotSupportedException?

var defaultRole=ZAssociateRoles.Single(r=>r.RoleName=="Associate"); var allAssociates=(from a in ZAssociates join arm in Associate_role_maps on a.UserName equals arm.UserName into leftArm from la in leftArm.DefaultIfEmpty() join r in ZAssociateRoles on la.AssociateRoleId equals r.AssociateRoleId into leftOuterRole ...

MYSQL count child table with other modifiers - help please!

I have a ticket purchasing database and want to collect all of the orders that meet certain criteria, and then also grab the total number of tickets each order has by counting the rows in the 'orders_tickets' table that match the orderID. If I make the SQL a simple call, this SQL works: SQL A: SELECT o . * , COUNT( ot.OrderTicketID )...

Selecting data from two mysql tables in one query

Hi everyone, I have two tables, landlords and properties. My properties table has; ID, Address, Postcode, lease and landlordID in it. The problem I face is: If I want to search for all the properties that have Mr.Spina as their landlord I need search the landlords database with the name "spina" to get his ID which is saved in the prope...

SQL: Get list of non-coupled items using MySQL 4

I'm not that good in SQL and I've come across a problem I don't know how to solve. I've read and re-read parts of a book about SQL (O'Reilly's Learning SQL) which I hoped would contain the information I needed but I haven't found it. My problem is the following. I'll use a simplified example to make it easier to discuss. I've got three...

PHP/MySQL How do I remove duplicate information from a LEFT JOIN?

I am trying to generate a simple list from a series of tables in a MySQL database. The SQL I am using is: $resource_sql = "SELECT prod_spec_sheets.spec_uri, prod_spec_sheets.spec_title, prod_photos.photo_uri, prod_photos.photo_title, prod_diagrams.diag_uri, prod_diagrams.diag_title, prod_ies.ies_uri, prod_ies.ies_title, prod_instruc...

LINQ to EF left join with multiple condition

I am trying to replicate the following SQL using LINQ to EF but with no luck. select * from Role left join QueueAccess on Role.RoleId = QueueAccess.RoleId and queueId = 361 Here's what I've tried. var myAccess = (from role in entity.Role.Include(p => p.QueueAccess) join qa in entity.QueueAccess on new { rID = role.RoleId, qID = queue...

linq to entites left outer join

I am struggling linq to entities left outer join. I have two entities (tables): Listings { ListingID, MakeID (nullable) } Makes { MakeID, Description } I want to write something like this in LINQ: select listings.listingID ,listings.makeid , IsNull(makes.Description, 'NA') from listings left outer join makes on list...

Join, Group By and Sum in MySQL

Hello, I have the following three tables in a MySQL database in order to give ratings to comments of users: Users: id name ----------- 1 Smith 2 Brown Comments: id user_id post_id comment ----------------------------------- 1 2 1 Test 1 2 1 1 Test 2 3 1 1 ...

Aggregate functions with a left outer join in LINQ to Entities

I've been looking through related LINQ questions here trying to figure this one out, but I'm having some trouble converting a SQL query of mine to the equivalent LINQ to Entities version. select companies.CommpanyName, job.Position, count(offers.jobID) As Offered, job.Openings, job.Filled from jobs left outer...

Django ForeignKey with null=True, inner join, and left outer join

Let's say I have two Django models Person and Company as follows: - class Company(models.Model): name = models.CharField() class Person(models.Model): last_name = models.CharField(blank=True) first_name = models.CharField() company = models.ForeignKey(Company, null=True, blank=True) A Person may or may not belong to ...

TSQL left join and only last row from right

Hi, I'm writing sql query to get post and only last comment of this post(if exists). But I can't find a way to limit only 1 row for right column in left join. Here is sample of this query. SELECT post.id, post.title,comment.id,comment.message from post left outer join comment on post.id=comment.post_id If post has 3 comments I get 3...