inner-join

hibernate many-to-one hql query, when inner join fetch property not associated

I have a many-to-one association set up like this, in the hbm.xml: <many-to-one name="gigVenue" class="blah.blah.xxx" fetch="select" lazy="no-proxy" not-null="true" > <column name="N_VENUE_ID" precision="18" scale="0" not-null="true" /> </many-to-one> And I am using instrumentation to do true lazy loading. BUT when I run a hql query ...

SQL Alternative to performing an INNER JOIN on a single table

I have a large table (TokenFrequency) which has millions of rows in it. The TokenFrequency table that is structured like this: Table - TokenFrequency id - int, primary key source - int, foreign key token - char count - int My goal is to select all of the rows in which two sources have the same token in it. For example if my table ...

translate stored procedure - to Linq2SQL (count, max, group, orderby)

I've two tables (1:N) CREATE TABLE master (idMaster int identity (1,1) not null, TheName varchar( 100) null, constraint pk_master primary key(idMaster) clustered) and - CREATE TABLE lnk (idSlave int not null, idMaster int not null, constraint pk_lnk_master_slave(idSlave) primary key clustered) link between Master.idMaster and ...

join as an alternative to multiple OR's

Hi. I have two tables with the following structure: table name: friends uid (PK)(FK) friend_id (PK)(FK) table name: posts post_id (AI)(PK) post_text uid (FK) //poster id I'd like to select posts that were only made from friends of user x. My inital plan was to use the following code: SELECT posts.post_text INNER JOIN friends ON po...

Outer join or dynamic query, which is the best way to go?

I have the following tables (I've simplified the data contained in the tables). RateTable - rate_table_id [int] - rate_table_name [nvarchar(50)] RateTableUsed - rate_used_id [int] - rate_table_id [int] (key to RateTable.rate_table_id) - customer_id [int] RateTableExtra - rate_table_extra_id [int] - rate_ extra_id [int] (key to...

How to join data frames in R (inner, outer, left, right)?

Given two data frames df1 = data.frame(CustomerId=c(1:6),Product=c(rep("Toaster",3),rep("Radio",3))) df2 = data.frame(CustomerId=c(2,4,6),State=c(rep("Alabama",2),rep("Ohio",1))) > df1 CustomerId Product 1 Toaster 2 Toaster 3 Toaster 4 Radio 5 Radio 6 Radio > df...

dependent subquery to self join

How do I change the following dependent subquery to self join? SELECT d.name, d.created, (SELECT SUM( q1.payout ) FROM client AS q1 WHERE q1.uid = d.uid) AS payout, (SELECT COUNT( q2.uid ) FROM client AS q2 WHERE q2.uid = d.uid AND q2.winning =1) AS cnt FROM client AS d group by d.name, d.created ORDER BY cnt DESC LIMIT 0 , ...

"Input string was not in a correct format" when using SqlDataReader and Label

I'm joining two tables - Contact and RetailTrainingUserLevelMap in a Select statement. (The common column in both is the RetailTrainingUserLevelID int) SELECT Contact.IntranetUserName, Contact.CompanyName, RetailTrainingUserLevelMap.RetailTrainingUserLevel FROM Contact INNER JOIN RetailTrainingUserLevelMap ON Contact.RetailTrainingU...

Converting from SQL to HQL fail

Trying to go from here this SQL: SELECT DISTINCT iss.ID FROM Issue AS iss INNER JOIN Message ON Message.IssueID = iss.ID INNER JOIN Clinician ON Clinician.UserID = Message.FromUserID INNER JOIN OrgUnit ON OrgUnit.ID = Clinician.OrgUnitID WHERE OrgUnit.ID = [id-number] To here in HQL: select distinc...

Multiple inner joins in SQL 2000

I'm in the unfortunate situation of developing an app using SQL Server 2005 in the development environment but SQL Server 2000 on the production server. I have a fairly complex SELECT query which works fine on the development/test server but falls over in the production environment: SELECT tbl_questions.Question, tbl_questions.Question...

Inner Join with more than a field

Precise to do a select with inner join that has relationship in more than a field among the tables Exemple: DataSet dt = new Select().From(SubConta.Schema) .InnerJoin(PlanoContabilSubConta.EmpSubContaColumn, SubConta.CodEmpColumn) .InnerJoin(PlanoContabilSubConta.FilSubContaColumn, SubConta.CodFilColumn) .In...

Can you do an inner select with Subsonic?

This is my sql query: subsonic version 2.1.0.0 No Linq (.net 2.0) select ( select Title from MenuTranslation mnu2 where mnu2.languageid = 1 and mnu2.menuelementid = menutranslation.menuelementid )as BaseTitle, * from menutranslation inner join menuelement on menuelement.id = menutranslation.menuelementid where menuelementid in (sel...

A complex SQL Query string inner join common denominator

This is a very very important sql query after which my whole website is based.. and its not working.. Its difficult to explain without an example.. There are 2 tables, One is IngredientsTable and other one is ProductsTable. In IngredentsTable i have the following Bread ChickenBreast Noodles Mayonaise Cheese Ketchup Butter And the...

Is there any difference between using innerjoin and writing all the tables directly in the from segment?

Do these two queries differ from each other? Query 1: SELECT * FROM Table1, Table2 WHERE Table1.Id = Table2.RefId Query 2: SELECT * FROM Table1 INNER JOIN Table2 ON Table1.Id = Table2.RefId I analysed both methods and they clearly produced the same actual execution plans. Do you know any cases where using inner joins would work in...

using where and inner join in mysql

I have three tables. locations ID | NAME | TYPE | 1 | add1 | stat | 2 | add2 | coun | 3 | add3 | coun | 4 | add4 | coun | 5 | add5 | stat | schools ID | NAME 1 | sch1 2 | sch2 3 |sch3 school_locations ID |LOCATIONS_ID |SCHOOL_ID 1 | 1 |1 2 | 2 |2 3 | 3 |3 Her...

Can this SQL Statement be made to perform better?

I have a SQL statement for merging edits from one table to another. I.e. UPDATE f SET f.AUDAPLCDE = m.AUDAPLCDE, f.AUDSTF_NO = m.AUDSTF_NO, f.AUDUPD_ID = m.AUDUPD_ID, f.AUDUPDDTE = m.AUDUPDDTE, f.UNTTYP = m.UNTTYP, f.UNTSTM_NO = m.UNTSTM_NO, f.UNTIND = f.UNTIND, f.UNQ = m.UNQ, f.TRNCTL_NO = m.TRNCTL_NO, f.TRN_NO = m.TRN_NO, f...

linq2sql: Explicit construction of entity type '#some type#' in query is not allowed - also using join

Hi there, I am having a problem mapping. I was reading scottGU post of "data shaping features" - http://weblogs.asp.net/scottgu/archive/2007/06/29/linq-to-sql-part-3-querying-our-database.aspx but i have tried this IQueryable<AccessRights> accessRights = from t1 in this.db.AccessRights ...

SQL query look for exact match using inner join

Hi! I have 2 tables in SQL Server 2005 db with structures represented as such: CAR: CarID bigint, CarField bigint, CarFieldValue varchar(50); TEMP: CarField bigint, CarFieldValue varchar(50); Now the TEMP table is actually a table variable containing data collected through a search facility. Based on the data conta...

Rails ActiveRecord :joins with LEFT JOIN instead of INNER JOIN

I have this code User.find(:all, :limit => 10, :joins => :user_points, :select => "users.*, count(user_points.id)", :group => "user_points.user_id") which generates following sql SELECT users.*, count(user_points.id) FROM `users` INNER JOIN `user_points` ON user_points.user_id = users.id GROUP BY u...

SQL double SELECT on the same field (mysql or sqlite3)

Hi, i have a problem that i can't solve ! (sqlite3, but i think it would be the same than MySql) I have those tables (image) : And i would like to find all the bus lines (with type and number) that go to two different streets from which i have the street_id (12 and 14 for exemple). The result shall give to the customer all of the bus...