join

simple Group By

Sorry for this simple question, but i'm missing the forest through the trees. These are my tables (i left the sparepart-table because i'm only looking for two special fk's): I need the distinct rows from tabData which are referenced in the child-table tabDataDetail with fiSparePart=8837 and 8969. The following gives me only that rows ...

Need help using sql join to convert rows to columns (Oracle 9i)

Hi, I have a table, with abt 22 columns and 6-7 thousand rows in the following format Seq_num unique_id name ... ------------------------------------ 1 1 abc 1 1 cde 2 1 lmn 2 1 opq 3 1 pqr ...

JPA Criteria API - How to add JOIN clause (as general sentence as possible)

I am trying to construct queries dynamically, and my next target is add JOIN clauses (I don't know how can I use the API). By now, for example, this code work for me : ... Class baseClass; ... CriteriaBuilder cb = JpaHandle.get().getCriteriaBuilder(); CriteriaQuery cq = cb.createQuery(this.baseClass); Root entity_ = cq.from(this.bas...

Best way to build a delimited string from a list in java

I have a list of objects, and each object has a string property. For example, I have a List<Person> and each Person has a firstName property. I want to build a comma-delimited, quoted string that looks like this: 'James', 'Lily', 'Michael' Considering that java doesn't seem to have a join method (and besides, this is a bit more compli...

SQL Server joins, groups, and etc. to do simple number crunching

Here's what I'm working with (SQL Server): departments: deptID, deptName students: studID, studName, deptID assignment: studID, courseID, status Students are assigned to a department and the student record holds the department's ID number. The "assignment" is a link between a student and a course (course not shown) that holds a statu...

NHibernate Linq - why does it use join when querying foreign key field?

Hi all I have a object model a bit like this: public class Foo { public int Id {get;set;} public string FooName {get;set;} } public class Bar { public int Id {get;set;} public Foo Foo {get;set;} } These correspond to tables in the database in the typical one-to-many kind of way. Using Linq to NHibernate to query Bars accord...

SQL Count where clause

I have the the following SQL statement: SELECT [l.LeagueId] AS LeagueId, [l.LeagueName] AS NAME, [lp.PositionId] FROM (Leagues l INNER JOIN Lineups lp ON l.LeagueId = lp.LeagueId) WHERE (lp.PositionId = 1) OR (lp.PositionId = 3) OR (lp.PositionId ...

Return distinct and null records from a mysql join query

Is there any way to return distinct values with blank/null data from a table join. Best to explain with my example below. Table "orders" order_id | order_total 1 | 10 2 | 20 3 | 50 Table "order_items" item_id | order_id | name | qty_ordered | base_price | row_total 1 | 1 | Product | 1 ...

mysql query issue

Let's assume I have the below table mysql> desc countrylist; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | country | varchar(32) | YES | | NULL | | +---------+-------------+------+-----+-...

Using For loop to retrieve multiple rows in Oracle procedure

Hi all, Im working on stored procedure where I need to retrieve a set of results and process each element individually and then return the entire result.(using 3 different tables) Im not too familiar with databases, but heres what I was able to come up with.. create or replace procedure GET_EMP_RSLT IS CURSOR ecursor IS select emp...

Ways to join and update tables in SQL.

Hi, So I am a first time user here, and still relatively new to SQL. I am attempting to take 2 tables, and join them in a sense. In Table1 I have the data: House_Key ---Other Fields--- Customer_ID House_Key is not unique, and as such I can have multiple Customer_IDs per household. House_Key is a numerical code, and Customer_ID i...

How can I SELECT from multiple tables in CodeIgniter

This is really boggling my mind on how to do this in ActiveRecord Queries in CodeIgniter. Maybe I'm over complicating it. I have three tables: (to keep it simple I'll only show the relevant fields) Articles (id, title, text, author) Comments (id, article_id, text, author) Users (user_id, user_type, first_name, last_name, email, passwo...

SQL Union/Join Query

I have a user profile table with columns User Name, manager and many other fields, for example amount.. Example Records:- User Manager Amount A B 100 x y 200 B C 300 M N 800 C D 500 P Q 1000 D E 1000 I am trying to get the result as below:- User...

Updating a table with multiple values from a select statement where the date matches

I'm having a problem updating a table and im sure its pretty straight forward but im going round and round in circles here. Table 'table1' data I want to update is formatted as follows: [Month] Figure ---------------------------------- 2010-05-01 00:00:00.000 1.0000 2010-06-01 00:00:00.000 1.0000 2010-07-01 00:00:00....

SQL Server Update with Inner Join

I have 3 tables (simplified): tblOrder(OrderId INT) tblVariety(VarietyId INT,Stock INT) tblOrderItem(OrderId,VarietyId,Quantity INT) If I place an order, I drop the stock level using this: UPDATE tblVariety SET tblVariety.Stock = tblVariety.Stock - tblOrderItem.Quantity FROM tblVariety INNER JOIN tblOrderItem ON tblVar...

How to change the sequence of 'joins' in CakePHP?

I have the problem with the sequence of joins. The similar problem was in another question http://stackoverflow.com/questions/3343348/manipulating-order-of-joins-in-cakephp. The answer was to use Containable behavior. In my case that is unacceptable because I have deeper associations and containable generates too many queries. Containabl...

In MySQL, need to join two tables with one table having multiple references to the second

I have two tables, one is signups and contained in it are two fields, firstchoice, and secondchoice. Another is a schedule table, and it has things like a begin and end date, and a semesterid. firstchoice and secondchoice from the signups table both reference the semesterid from the schedule table. I am trying to create a page which d...

Multiple Joins on Temporary Table OR Subquery in SQL 2008

Hi, I have the following SQL which gets a season for each day in a range of dates, then groups each season by start and end date with number of nights. What it does is not important but my question is which is better, the way I've done it below or use the first select statement as a subquery each time @dateSeasons is used in the second q...

How to retrive the name of the attribute dynamically without specifying the name of the attribute ?

I am developing asp.net mobile application. I am using LINQ to XML to query XML file. I am using the following query to retrieve the name & value of the query dynamically as follows var TotalManifolds = from MF in FieldRoot.Element("FIELD-DEFINITION").Element("MANIFOLDS").Elements("MANIFOLD") join SLT in FieldRoot.E...

How Do I Join Records In LINQ?

Hi. I am new to LINQ and am trying to sort this one out. I am trying to grab records for multiple accounts (multiple account ids) and sort the results according to the date. What I have currently is putting the results in a list such that multiple rows / account are contained in each list item. This is close but not exactly what I...