join

SQL: Select from many-many through, joined on conditions in through table

Can't get my head around this... I have 3 tables like this: Computers --------- Id Name ComputerLogins -------------- Computer_Id User_Id NumberOfLogins Users ----- Id Name Computers "have and belong to many" Users "through" ComputerLogins. Sample data: Computers: Id Name 1 "Al...

Oracle number and varchar join

I have a query that joins two tables. One table has a column that is of type varchar, and the other table has type of number. I have executed my query on 3 oracle databases, and am seeing some strange results I hope can be explained. On two of the databases something like the following works. select a.col1, b.somecol from tableA a i...

HQL nullable property should not be ignored, help?

Hey All, I have: Class Foo { String name; String value; Foo parent; //Foo.parent is OneToOne and nullable } I have the following HQL: FROM Foo f WHERE (lower(f.name) like concat( lower('string') , '%' )) or (lower(f.value) like concat( lower('string') , '%' )) or (lower(f.parent.name) like concat( lower('string') , '%' )) The que...

SQL natural join POSTGRES

I'm not sure what kind of join I need as I'm not familiar with trying to overlap data in such a way or if it's even feasible. I have two tables which both share a similar set of data and are both related to a 3rd parent table thru Room_id. I have a table called Room_rates which stores average prices for each room (room_id) +-------+--...

How do I get every permutation in two totally unrelated tables with t-sql

I have two tables that are indirectly related by another table TableA - ID, SomeFieldA TableB - ID, SomeFieldB TableAB - IDA, IDB, SomeFieldAB I have to generate data from the ground up. So I've put some data in TableA, and I've put some data in TableB. The problem is, I need to insert data into TableAB now, and I don't...

How to limit the return of a join to just one table?

Today at work we got into a discussion about which is the best way to do a query like this : For instance lets assume a users table : tblUsers ID = Autoint Name = String and a login table : tblLogin ID = AUtoint UserID = Int IP = String Browser = String OS = String timestamp = DateTime What would...

Problem joining tables where joined table needs to be ordered before grouping

I have a scenario, which is seemingly simple on paper, but I'm having trouble getting to work as desired in practice. I have two tables (only relevant columns presented): | Thread +---------- | ThreadID | Post +---------- | PostID | ThreadID | Posted (Datetime) Now, what I want to do, is to join Thread and Post, grouping by ThreadI...

How do I perform the SQL Join equivalent in MongoDB?

How do I perform the SQL Join equivalent in MongoDB? For example say you have two collections (users and comments) and I want to pull all the comments with pid=444 along with the user info for each. comments { uid:12345, cid:444, comment="blah" } { uid:12345, cid:888, comment="asdf" } { uid:99999, cid:444, comment="qwer" } user...

mysql problem: populate table from another table but referencing ID instead of name

Hi, I'm now trying to populate my 'testMatch' table (below) with data from my 'summary' table: TESTMATCH TABLE +------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------+-------+ | match_id | int...

UPDATE Multiple Rows Using CASE in SQL 2008

Hi, I have the following sql statement where i'm trying to update multiple rows matching a select statement. UPDATE Cars SET engineSize = CASE specCode WHEN 1 THEN value ELSE engineSize END FROM Cars INNER JOIN CarSpecs ON CarsSpecs.carID = Cars.carID My tables are as follows: Cars carID engineSize ... 1 0 2 0 CarSp...

Latest Actions in PHP?

If you go to http://www.foxnews.com, and scroll down a little you would see the box "LATEST NEWS". Now I want to do something like that, but with modification. I have 3 queries, that get out information from 3 tables. But this doesn't give me the "latest news", because it's 3 separate queries. Now I want to make a box, (I can do this ...

Getting random and last value from group in SQL

I have a SQL table containing train schedules. The table looks something like this: Schedule TrainNumber LegID DepartureTime DepartureStation ArrivalTime ArrivalStation My real database contain several tables, but for this question only the one above is relevant. Different trainNumber can have different amount of legs. Based on a dep...

Inner Joining two tables based on all "Key/Value" Pairs matching exactly

Lets say I have two tables - Person and Clothes and both of these tables have associated Key/Value tables which store attributes about a Person and an item of Clothing. A joined version of Person to Attributes might look like: PersonID | AttributeKey | AttributeValue 1 'Age' '20' 1 'Size' 'La...

Linq expression

I have 2 lists. The first is a CountryId-list that can look like this 036 208 999 The seccond list is a coundryId to Name list that can look like this: 036, AUSTRALIA 208, DENMARK 380, ITALY 578, NORWAY The result shall be like this: AUSTRALIA DENMARK UNKNOWN ID (999) How can I make a linq query that solves this? ...

Create combined DataTable from two DataTables joined with LINQ. C#

I have the following code that fills dataTable1 and dataTable2 with two simple SQL queries, dataTableSqlJoined is filled from the same tables but joined together. I'm trying to write a LINQ query that can create the dataTableLinqJoined as if it had been created using SQL. In my example below, it only returns the values from dataTable1. ...

Ruby-on-Rails: How to pull out most recent entries from a limited subset of a database table

Imagine something like a model User who has many Friends, each of who has many Comments, where I'm trying to display to the user the latest 100 comments by his friends. Is it possible to draw out the latest 100 in a single SQL query, or am I going to have to use Ruby application logic to parse a bigger list or make multiple queries? I ...

MySQL Full Outer Join Syntax Error

SELECT airline, airports.icao_code, continent, country, province, city, website FROM airlines FULL OUTER JOIN airports ON airlines.iaco_code = airports.iaco_code FULL OUTER JOIN cities ON airports.city_id = cities.city_id FULL OUTER JOIN provinces ON cities.province_id = provinces.province_id FULL OUTER JOIN countries ON cities.countr...

MySQLi not returning results from a JOIN prepared statement

function fullJoinTest() { $con = ModelBase::getConnection(); $sql = "SELECT airline, airport FROM airlines LEFT JOIN airports on airlines.icao_code = airports.icao_code"; $query = $con->prepare($sql) or die("Error preparing sql in Search (test) "); $query->execute() or die("Error executing query i...

C# LINQ Joining And where statement not working

I am trying to have the collection of order IDs be used in my where statement how come i can't get this to work? List<int> orders = new List<int>(){1,2,3,4,5}; DataTable dtTable1 = getOrders(); DataTable dtTable2 = getOrderDetails(); var results = from a in dtTable1.AsEnumerable() join b in dtTable2.AsEnumerable() on a....

Rails - Add Record to join table from controller

I'm trying to create a record within a join table from the action of a button. To explain, I would have an events model and would like to track selected events from each user. I used the HABTM relationship since I dont really need any extra fields. User.rb => has_to_and_belongs_to_many :events Event.rb => has_to_and_belongs_to_many :us...