joins

PHP & mySQL: Repeating entries problem - Need each entry to show up only once but they repeat

Hi all, MY PLATFORM: PHP & mySQL WHAT I HAVE HERE: I have 4 tables, namely, 'books', 'book_type', 'book_categories', 'all_categories'. WHAT I AM TRYING TO DO: In simple words, I want to display all the books that are in stock i.e. in_stock = 'y', with all the book related information from all the tables, only once without repeati...

Need help with a SQL join in Rails

I have a table Blog belongs to User through user_id. I'm using thinking sphinx to index the blog, but I only want it to index blogs where the user is currently active (user.status = User::ACTIVE). I have the code below for creating the index, but I know the 'where' clause is wrong. What should it be? define_index do indexes title ...

Nested named scopes with joins (explosive error)

So I have an ActiveRecord class with a couple different named scopes that include join parameters. While running a report, I happen to have a situation where one gets called inside of the other: 1 Model.scope_with_some_joins.find_in_batches do |models| 2 models.each do |mdl| 3 other_comparisons = Model.scope_with_other_joins 4 ...

SQL: Is a query like this OK or is there a more efficient way of doing it, like using a join?

I often find myself wanting to write an SQL query like the following: SELECT body FROM node_revisions where vid = (SELECT vid FROM node WHERE nid = 4); I know that there are joins and stuff you could do, but they seem to make things more complicated. Are joins a better way to do it? Is it more eff...

TSQL INTERSECT Keyword

I have known about the SQL Server 2005/2008 keywords EXCEPT and INTERSECT for some time but have never found a compelling reason to use INTERSECT over the good old UNION keyword. Can anyone explain when or why you would use INTERSECT instead of UNION? ...

Joins with multiple fields on GroupBy table data in LINQ query/method

Urgent: I have to work out how to write the following SQL query usingLINQ query or method syntax. (Edit: This is to return a list of latest AgentActivities for all Agents). Any help will be much appreciated. SELECT a.[AgentActivityId], a.[AgentId], a.[ActivityId], a.[StartedAt], a.[EndedAt], a.[Version] FROM [dbo].[AgentActivity...

MYSQL like a join but only need the newest row?

I want to do the following but in one query: $query = mysql_query("SELECT name FROM tbl_users WHERE category = '1'"); while($row = mysql_fetch_assoc($query)) { $query2 = mysql_query("SELECT datecreated FROM tbl_comments ORDER BY datecreated DESC LIMIT 1"); $row2 = mysql_fetch_assoc($query2...

Using a Right Outer Join to Match Records from Two Different Databases

SQL 2005: I am trying to create an outer join that will pull records from two different databases. My objective is to determine which records in database B don't have matching records in database A. When I tried running the query, it returned the error below. I am not sure how to get around this error: 'Tables or functions 'Asset...

Conditional join in Mysql select statement

I have two tables tbl_studentapplicationdetails and tbl_studentqualification... My second table contains studentId and qualificationId... Now i want to join two tables to get list of students with their qualification... NOTE: Some students may have two or three qualification.... And my query is select distinct t1.dStudent_id,t1.dStuF...

Left Joins in Grails

Hi, How to call joins in grails.Here are my Domain classes : package com.org.domain class CoverageList { Integer id Integer pub_cat_id Integer brand_id Integer brand_set_no String cov_list_desc Date edit_date Date part_edit_date Integer category_id static constraints = { } static mapping ...

IN clause on multiple columns using AND , JOIN

Which of the following queries are correct SELECT ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME WHERE STATUS IN('OPEN','UPDATED') AND ITEM_TYPE IN ('ITEM1','ITEM2') AND CREATED_TIME BETWEEN 'XX' AND 'YY' AND UPDATED_TIME BETWEEN 'XX' AND 'ZZ' SELECT ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME WHERE STA...

Rails - Find results from two join tables

I have have 3 Tables of data and 2 Join Tables connecting everything. I'm trying to figure out a way to query the results based on the condition that the join table data is the same. To explain, I have User, Interest, and Event Tables. These tables are linked through an HABTM relationship (which is fine for my needs since I dont need ...

Linq - Loading record with many joins (One to One)

Hello, My app has quite a lot of entities with relationships. I have a scenario in which the entity should join all the contacts records (One to One). another scenario where I need only limited record without all the joins. For example, a "service call". This entity contains a number of one-to-one relationships: supplier, customer .. I ...

Java Spring-Hibernate mapping question?

Say I have domain objects corresponding to Posts and Users. Nevertheless, I have corresponding database tables containing information relevant to "posts" and "users". Currently I have properly set up the mapping in Hibernate so that I can pull the info from the "posts" table and save it as a Post object. However, I then added to my ...

Eliminate the Join Table in HasMany Relationships in Rails

I am thinking about ways to create a Role Based Access Control system in Rails. I have seen these great projects too (among others): RoleRequirement ActsAsPermissible RailsAuthorization BoxRoom (rails file management) My question is, is it really necessary to have a join table for everything? If I one of the tables in the relations...

Using multiple JOINS. SUM() producing wrong value.

Hey folks, I am getting some basic invoice information in a SQL query and figuring the Order Total and Payment Totals in the same query. Here is what I have thus far: SELECT orders.billerID, orders.invoiceDate, orders.txnID, orders.bName, orders.bStreet1, orders.bStreet2, orders.bCity, orders.bSta...

In linq to entity, how to retrieve objects matching a list of conditions without looping ???

Dear all, I play with a mysql database using ADO entity framework (through devart dot.connect). I get lists of entity objects (a) from listboxs and I would like to make a linq query retrieving all object from a given entity table (b) where there is a b foreign key: //THIS WORKS : IQueryable<Nb_student> nbstud = MainForm.context.Nb_stu...

php & mySQL: Query does not use index in table join

Hi, I am trying to list all the book_sales information for a particular book author. So I have a query and it's not using Index to lookup records. The following is my tables structure: -- Table structure for table `books` CREATE TABLE IF NOT EXISTS `books` ( `book_id` int(11) NOT NULL auto_increment, `author_id` int(11) unsigned...

MYSQL: How to find player_id from surname?

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

Zend Framework: How to combine three tables in one query using Joins?

I have three tables like this: Person table: person_id | name | dob -------------------------------- 1 | Naveed | 1988 2 | Ali | 1985 3 | Khan | 1987 4 | Rizwan | 1984 Address table: address_id | street | city | state | country -----------------------------...