join

LINQ to SQL - Left Outer Join with multiple INNER JOINS

I am using multiple joins in a statement and Have tried to make linq-to-SQl query for this but no success. SELECT ur.UserName, ur.LandmarkRef, lt.Date, l.Place FROM tbl_Users ur LEFT OUTER JOIN tbl_LandMarks l ON ur.LandmarkRef = l.LandMarkID INNER JOIN tbl_LandmarkTypes lt ON l.LandmarkTypeRef equals lt.LandmarkTypeID ...

unexpected JPA results

Hi, Im learning JPA and having problems. My main problem is when i join my entitys and dont quite understand why im getting the results i am .. and its realy slowing my progress if someone could help i would be very greatful. Entitys College @Entity @NamedQueries({ @NamedQuery(name=College.allCollegeQuery,query="SELECT col FROM C...

MySql Join with Sum

Hello, I have a table called RESULTS with this structure : resultid,winner,type And a table called TICKETS with this structure : resultid,ticketid,bet,sum_won,status And I want to show each row from table RESULTS and for each result I want to calculate the totalBet and Sum_won using the values from table TICKETS I tried to make some...

mysql left join and sum group by

Hi i using this mysql code to generate this data below: code: SELECT L.LEAVETYPE, LA.IDLEAVETYPE, LA.IDLEAVETABLE, SUM( LA.NOOFDAYS ) FROM LEAVETYPE AS L LEFT JOIN LEAVEAPPLICATION AS LA ON LA.IDLEAVETYPE = L.IDLEAVETYPETABLE GROUP BY L.LEAVETYPE When i add the Where condition it will only show the LeaveType with value. How can i ...

mysql JOIN, how is this interpreted?

If I write a sql query for mysql and I just specify JOIN (no outer, inner, left, etc), what type of join does this default to? For example: SELECT count(*) FROM Students p JOIN Classes c ON p.studentId = c.studentId ...

SQL Statement (JOIN)

I have the following MySql Table: -------------------------------------------- Table 'category' -------------------------------------------- category_id name parent_id 1 animal NULL 2 vegetable NULL 3 mineral NULL 4 dog 1 6 cat 1 7 ...

SQL Query to show 3 user names from userID's in one Bug record.

I would like to show all the usernames who have interacted with a bug in MS Access. I do not understand how to reference the same Users.UserName field 3 times in my select statement? Here are my tables -------------------------------------------- Table 'Bugs' -------------------------------------------- BugID OpendBy ClosedBy ...

Django LEFT OUTER JOIN on TWO columns where one isn't a foreign key

I have two models like so: class ObjectLock(models.Model): partner = models.ForeignKey(Partner) object_id = models.CharField(max_length=100) class Meta: unique_together = (('partner', 'object_id'),) class ObjectImportQueue(models.Model): partner = models.ForeignKey(Partner) object_id = models.CharField(max_...

mysql JOIN ON IF() ??

Hi there, I am trying to use sql such as this: SELECT t.*, t2.* FROM templates t LEFT JOIN IF(t.t_type = 0,'templates_email', IF(t.t_type = 1,'templates_sms','templates_fax')) t2 ON t.t_id = t2.t_id; Is it possible to do something like that? basically I want to join on one of three tables based on the value from the row. Is this re...

MySQL multi table join query

Result needed: The registration number of the cars used by Instructors at the Glasgow, Bearsden office. The 3 relevant tables I have are; ( I have omitted the non-relevant table information, to simplify this. The database is called easydrive. No this is not a School or UNI assignment, it is a question from a text book, that we have bee...

MySql - Select Query - Joins

I have two tables User ID UserName 1 Name1 2 Name2 3 Name3 4 Name4 5 Name5 Item ID ItemName InsertedBy UpdatedBy 1 Item1 1 4 2 Item2 3 3 3 Item3 2 5 4 Item4 5 3 5 Item5 4 5 Resultant Table required ID ItemName InsertedBy UpdatedBy 1 Item1 Name1 Name4...

What's wrong with my sql query using left joins?

I'm using MySQL. I have 3 tables I'm trying to connect in a query and I can't see what I'm doing wrong with the following query: Table 1: books (list of book information) Table 2: bookshelf (list of books a member owns) Table 3: book_reviews (list of book reviews) I want to generate a list of all books a user has in their bookshelf, a...

this SQL appears to be giving weird results with multiple JOINS, how do I correct it?

I have several Rails Active Record Models: ContactEmails, ContactCalls, ContactPostalcards. Each one represent a completed activity: a completed email, call, or postalcard to a specific Contact, and each Contact belongs to a Company. I want a high-level summary by Company of all the Emails, Calls, and Postalcards within a specific dat...

JPQL to query entity connected to another by @OneToMany

Has: class Container { @Id @Column(name="id") protected long id; @OneToMany @JoinColumn(name="container_id", nullable=false) protected Collection<Content> contents = new ArrayList<Content>(); } and class Content { @Id @Column(name="id") protected long id; @Column(name="link_id") protec...

Mysql JOIN of four tables with two key tables

I hate to admit it by my knowledge of MySQL is lacking when it comes to the more complex queries. Essentially I have four tables two of them contain the data I want to return, and two are relational tables linking the data. Table A is present just to provide filler for Table D.aID. +--------+ +--------+ +--------+ +-----------+ +------...

Join two mysql tables

I have two databases - one for articles and the another for the articles' meta information (Like author, date, category and atc.). I have the following columns in meta table: ID, article id, meta type and meta value. I wonder how can I join these two tables to get both - article and meta information - with one mysql query. The article id...

How can I retrieve records from one table and related records from another table in one query?

I have two tables, items and itemmeta. items has item_id which links it to itemmeta which has itemmeta_id, item_id, meta_key and meta_value. I did this so if i needed to add a special flag or value to an item i didn't have to keep changing the items table. Now i am paying the price for flexibility. I want to know if i can combine a quer...

Entity Framework - how to join tables without LINQ and with only string?

Hi all, I have a question about Entity Framework. Please answer if you know answer on this. I have such query : String queryRaw = "SELECT " + "p.ProductName AS ProductName " + "FROM ProductEntities.Products AS p " + "INNER JOIN CategoryEntities.Categories AS c " + "ON p.CategoryID = c.CategoryID "; ObjectQuery<Db...

New to SQL, how do I use results from a SELECT call on TableA to SELECT against TableB?

I'm using phpMyAdmin to query the database. I want to select all the entries in TableA where Date='2010-08-01'. Then I want to use the SubID attributes of the items in the result table to find all the entries in TableB with matching SubIDs. I know I have to use a JOIN somewhere, but I am not sure how or where. I searched StackOverflo...

sql left join where rails

Because I use rails I've become rusty on sql since I rarely use it in rails. I have two related tables: comments 1:m comment_views I want to find all comments where comment_views.viewed is false. Problem is, for some comments there is not a relating record in comment_views yet. So far I have select comments.id from comments ...