join

Mysql Join query using a group by

I'm trying to write a query that will return ... Each distinct user id in the accesslog table. A count of how many distinct ip addresses each userid has accessed the site with, also from the accesslog table. That userid's name, email address, and company name, from the users table. I've got 1 and 2, but I'm not sure how to get 3. I s...

Many to one join help for flat file

I’m not sure if this has already been asked – I looked around for quite a bit but couldn’t find anything. My end result is to get data from several of my SQL database tables into a comma delimited flat file. Normally this would not be a problem but due to their many to one relationship and the format I’m forced to put it in it’s proving...

NHibernate + SqlCE - Forever Lazy?

I'm working on a Winforms app with NHibernate as my ORM layer. Everything is going well, but it doesn't seem that NHibernate is respecting my instructions to not use lazy loading. Problem 1: The app is a task list type of thing, and tasks can have child tasks. I'm representing these in a TreeView, so I need to first add nodes for top-l...

inserting into a view in SQL server

I have a SQL Server as backend and use ms access as frontend. I have two tables (persons and managers), manager is derived from persons (a 1:1 relation), thus i created a view managersFull which is basically a: SELECT * FROM `managers` `m` INNER JOIN `persons` `p` ON `m`.`id` = `p`.`id` id in persons is autoincremen...

How to have multiple join constrains in Zend_Db_Select

Hi, How can I do a join such as : LEFT JOIN table1 t1 ON t1.foo = t2.bar AND tl.baz = t2.bat What I'm looking for is the syntax for adding a second join constraint to the leftJoin() method using Zend_Db_Select in the Zend Framework. Thanks, ...

How to do join with SUM in ActiveRecord

Let's say I have two simple models project t.string :title vote t.references :project t.integer :value When I do loop throuh all projects, I also want to include sum of all votes, because doing projects = Project.all foreach project in projects sum = project.votes.sum(:value) ... isn't really effective. Is there...

How can I join these two queries?

Here's what I want to accomplish: Select all rows from table "posts" while also fetching linked entities, such as the author (from table "users") and the names of the categories the post belongs to (from table "categories"). These are my two queries so far: This one fetches the posts: SELECT posts.* , users.authorName...

Problem with ORs in JOIN statement

I have a problem with the SQL statement detailed below. The query returns the results I need, but takes an insane amount of time to execute. I now have so many records in the db that the page generally won't load. SELECT dscan.guid, dscan.drive, dscan.folder, dscan.filename, source.guid FROM source RIGHT JOIN dscan ON ( (source.gui...

Linq inner join grouping

I'm trying to create an expression tree dynamicly. Let asume that I have two simple classes: class CustomerType { public int Id { get; set; } public string Name { get; set; } public OrderType[] Orders { get; set; } } class OrderType { public int Id { get; set; } public DateTime Date { get; set; } public decimal Pric...

SQL Joining 2 similar tables

Hi, I have two tables which are used to store details of different types of events. The tables are almost identical, with fields such as date, duration etc. I'm trying to perform a join on the two tables and sort them by the mutual field 'date'. I know it would be simpler to simply add a 'type' field to a single table and store it all...

combine join with String.Contains in Linq query

i have the following linq query that create a left join between two tables: var joinResultRows = from leftTable in dataSet.Tables[leftTableName].AsEnumerable() join rightTable in dataSet.Tables[rightTableName].AsEnumerable() on...

Show only most recent date from joined MySQL table

I have 2 tables, a "document" table and a "content" table. They look like this (simplified): document table: docID docTitle content table: contentID docID dateAdded content For every content change, a new record is inserted into the "content" table. This way there is a complete history of all changes. I would like to get a list of al...

Optimize SQL join query for NHibernate

Take a fairly simple domain model Orders, items and shipments where the Order is a root entity and shipment is a root entity. I want to look up all shipments for a given order. The query is pretty straight forward but I'm seeing undesired behavior with NHibernate. The Model public class Order { public Order(){ Items = new List<LineIte...

Order By not working on MySql

Hi Order By not working on MySql the code is as follows, select * from School where School.type = 'HighSchool' order by (select locations.name from locations inner join School_locations on locations.id = School_locations.location_id where School_locations.School_id = School.id and locations.location_country = 'US'...

Difference between two sets of data in SQL Server 2000

I am trying to work out the difference between customers that have been billed in the last 3 months and the whole customer base. I have been able to calculate the customers that have been billed using the following SQL DECLARE @DateFrom AS DATETIME SET @DateFrom = CONVERT(DATETIME, CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '-' + ...

MVC LINQ to SQL JOIN for custom type fails for a strongly typed view

I have a simple data model of two tables, email and recipients, email can be sent to one or more recipients I have setup the database with the two tables, created the Linq to SQL repository, built the controllers and the strongly typed view. This works fine when I want to select all records from the database public IList<AllMailDetai...

MySQL: Joining 3 tables, LIMIT results by first table?

I am selecting everything from prod_drop and joining 2 other tables for additional data. I do not want any of the rows from prod_drop to be repeated, I just want 1 row for each prod_drop row. Here is my query: SELECT prod_drop.* , prod_drop_products.product_id , cart_product.product_image_sm FROM prod_drop LEFT JOI...

What does (+) do in Oracle SQL?

I'm using Oracle SQL Developer to query an Oracle DB (not sure which version it is) and I'm going to use the SQL I make for a Crystal report. Many of the reports the previous developers have written don't use JOIN keywords to make the joins (and I'm not too familiar with JOIN keywords as a result). Many of the joins they make are mad...

Google App Engine / JDO : store computed values

Since I cannot do JOIN queries against the App Engine Datastore, I would like to denormalize my entity objects a little to include computed values, in effect creating something like a function-based index. @Persistent Employee manager; @Persistent // de-normalized stored join value Integer managerDepartmentCode; This works fine,...

Php pdo result of JOIN query

Hello, My problem is I try to do a simple JOIN between two tables, that both have the id field. My result is an stdClass object, as I use PDO. Does anyone know how can I make a difference between the id of the first table and the id of the second table? Code bc. $sql = "SELECT *, FROM products AS p, products_categories as c ...