join

JPA @NamedQuery with two tables, possible??

Hi all, I'm having a kind of dummy problem, I need to make a @NamedQuery with a join with other table, some simple thing. But in all my @NamedQuery I'm only dealing with my mapped Object/Table. For example in my Object/Table Mapped cars: @NamedQuery(name = Cars.GET_AVAILABLE_CARS, query = "select c.id from Cars c where c.status = (s...

SELECT DISTINCT values after a JOIN

I have 3 tables: Vehicle: vehicle_id, vehicle_type 1, motorcycle 2, car 3, van Owners: person_id, vehicle_id, date_bought 1, 1, 2009 1, 2, 2008 2, 3, 2009 2, 1, 2005 I want to display a list of all vehicle names. If the person_id = 1, date_bought should also be returned. So I thought I would start with this: SELECT * FROM vehicles ...

multiple innerjoins with linq

I've got the following sql statement and need to know how to write it in linq, but i can't figure out how to write the multiple inner joins SELECT Zugehörigkeiten.PK_Dept_ID FROM mitarbeiter INNER JOIN (abteilungen INNER JOIN Zugehoerigkeiten ON abteilungen.PK_Dept_ID = Zugehoerigkeiten.PK_Dept_ID) ON mitarbeiter...

SQL Join Question

I have the following tables: Table: user_groups (many-to-many) user_id (int) group_id (varchar) Table: profile_groups (many-to-many) profile_id (int) group_id (varchar) So basically, I want to write a sql script to find out what profile is assigned to each user. So in the end there should be only 2 columns: user_id and profile_...

Joining two SQL tables with GROUP BY not achieving desired results

For example, I have a shop order database, and two tables in it - ORDERS and ORDERSTATUS. Table : orders -------------------------------------------- OrderID | OrderItems | AddedTimeStamp | -------------------------------------------- 1 | Apples | 2009-12-22 13:15:18 | -------------------------------------------- 2 ...

boost thread and join

Hi, I've got this test case here that compiles with g++ theFile.cc -lboost_thread. When running the program it seems to be hanging at the join command. I'm not exactly sure why. It's like the interrupt_point() function isn't getting the join request from the main thread. #include <iostream> #include <stdio.h> #include <signal.h> #inclu...

Friendship System Sql Structure & Query

Hello. I am coding a friendship system and it has two tables. members id username password friends id user_id friend_id status Let's say that i want a query that can select the friends IDs of the member $userId how possible to make this in one query? I found a solution which is to make 2 queries. The fist selects the friends W...

MySQL Multiple Join/Subquery Questions

Hi All, For about a year now, we’ve been allowing our users to login with usernames and/or email addresses that are not unique (though each user does have a unique id). Although the system handles duplicate usernames/emails elegantly, we’ve decided to finally enforce unique usernames and email addresses. I’ve been tasked with generati...

How to join SQL tables while selecting a COUNT() function?

I have two database tables, 'Lists' and 'Notes'. Lists has columns _id, listname Notes has columns _id, checked, list_id (which is a foreign key of Lists._id), and more columns that aren't relevant to this question. I would like to create a query that returns four columns: Lists._id, Lists.listname, the count of all checked Notes in thi...

I am confused with join.

I have two tables, omc_categories and omc_products. I want to pull out categories name where omc_proudcts.category_id is equal to omc_categories.id. I created the following sql but I am not sure which is right one. SELECT C.Name AS CatName FROM omc_categories AS C LEFT JOIN omc_products AS P ON C.id = P.category_id WHERE...

How can I sort data before grouping in MYSQL

I have a big mysql query which needs to trawl through 4 tables to get all the 'items' for my application. Each item can have many categories and each user can have up to one of each item. The items and categories are easy: SELECT Items.itemId, Items.name, Items.type, Categories.name AS category FROM Items LEFT JOIN ItemCategories ON I...

Select row above and row(s) between next matching row or last row

...

Queries that implicit SQL joins can't do?

I've never learned how joins work but just using select and the where clause has been sufficient for all the queries I've done. Are there cases where I can't get the right results using the WHERE clause and I have to use a JOIN? If so, could someone please provide examples? Thanks. ...

Why won't this LINQ join statement work?

I have this LINQ-query: // types... LinkedList<WeightedItem> itemScores = new LinkedList<WeightedItem>(); var result = from i in _ctx.Items join s in itemScores on i.Id equals s._id orderby s._score descending select new ItemSearchResult(i, s._score); // this fails: ...

How do I make DBX know that fields from joins shouldn't be updated during ApplyUpdates?

I've got some code that builds a map (graph) of rooms on a grid with links between them. It's stored in a Firebird database with rooms in one table and links in another. The data is managed through DB Express TSimpleDataset datasets. The query for the Exits (links) table looks like this: select EXITS.*, r1.x as x, r1.y as y, ...

(mySQL) Unable to query 2 tables properly for data

I have 2 tables. One is 'page_links' and the other is 'rpp'. Table page_links is the superset of table rpp. The following is the schema of my tables: -- Table structure for table `page_links` -- CREATE TABLE IF NOT EXISTS `page_links` ( `page` varchar(255) NOT NULL, `page_link` varchar(100) NOT NULL, `heading_id` tinyint(3) uns...

Join operation with NOSQL

Hi, I have gone through some articles regarding Bigtable and NOSQL. It is very interesting that they avoid JOIN operations. As a basic example, let's take Employee and Department table and assume the data is spread across multiple tables / servers. Just want to know, if data is spread across multiple servers, how do we do JOIN or UNI...

Combine two tables in SQLite

I have two tables, ta and tb: ta: key col1 -------- k1 a k2 c tb: key col2 ------- k2 cc k3 ee They connected by "key". I want to know how can I get a table, tc, like: key col1 col2 ------------- k1 a k2 c cc k3 ee Is there a easy method instead of inserting every record? They are one million records of tables so ...

Join two tables based on relationship defined in third table

Hello, I have two tables Activity and Action. One or more actions can be performed for an activity. And the relationships between Activity and Action is given in a third table called Activity Action. How do I retrieve a result set that tells me what action is applicable for each activity using an sql statement? Here's the table structu...

Listing mutual friends in Core Data

I'm working with an intermediate join table in Core Data similar to the friends example from Apple's Core Data Programming Guide. Sorry, can't post pictures yet due to no reputation points. Its a friends entity with relationships to FriendInfo intermediate join, with relationships of friends and friend info. The following link describes ...