join

sql join - only select top row from 2nd table...

Bit of an sql noob, have a list in table a of customercodes/phone numbers, and table b has all the call records. I want to select the most recent call from table b for each of the customercodes/phone numbers in table a. So far I have: SELECT A.CustomerCode, A.PhoneNumber, B.StartTime FROM tableA A INNER JOIN t...

Iphone NSPredicate how to do an INNER JOIN?

Hello, I have been looking thru the documentation and some other posts in this site, but I simply cannot understand how to work this out, which is actually very simple in SQL. Basically I have 2 entities Instruments and Deals. And the "Deals" use an "Instrument" to perform a desired operation. Now I want to list Deals attributes and t...

Correct MySQL JOIN format to avoid nested SELECT

I have two separate SELECT statements: SELECT VCe.VId FROM `VCe` WHERE `YId` = 9007 AND `MaId` =76 AND `MoId` = 2851 SELECT r_pts.p_id FROM r_pts WHERE r_pts.v_id IN (57202, 57203, 69597, 82261, 82260, 69596, 69595, 82259) When they are run separately they both complete in under .05sec however when I nest the first one within the sec...

How to get data from a joined table

I have two table joined with c.id = p.category_id. I want to get categories.name but it gives an error. Could anyone tell me how to get data from a joined table please? function getGalleryone(){ $data = array(); $query = 'SELECT * FROM products AS p JOIN categories AS c ON c.id = p.category_id WHERE c.name ...

SQL Server 2008 extremely slow using FTS on joined tables

I have two tables Product (id int, brandid int,Name nvarchar(1000)) 2+ million rows Brand (id int,name nvarchar(1000)) 20k rows FullText index is enabled on both table's name field. If I do a search such as SELECT count(*) FROM Product p join Brand b on p.BrandID = b.ID WHERE contains(b.name,'calvin') Runs super fast...

Hadoop: intervals and JOIN

Hi all, I'm very new to Hadoop and I'm currently trying to join two sources of data where the key is an interval (say [date-begin/date-end]). For example: input1: 20091001-20091002 A 20091011-20091104 B 20080111-20091103 C (...) input2: 20090902-20091003 D 20081015-20091204 E 20040011-20050101 F (...) I'd like to...

A good example for boost::algorithm::join

I recently wanted to use boost::algorithm::join but I couldn't find any usage examples and I didn't want to invest a lot of time learning the Boost Range library just to use this one function. Can anyone provide a good example of how to use join on a container of strings? Thanks. ...

MySQL join with LIMIT 1 from two tables

I have two tables. One with information about properties. The other stores 3 images for each property. Of these three images - one is marked as being the "main" image. So I have: Properties: p_id name 1 villa a 2 villa b 3 villa c 4 villa d and Images i_id p_id main 1 1 0 2 1 0 3 1 1 4 2 0 ...

how to implement a double join in n-to-n relation in MySQL

In my database I have cars and persons. Between the cars and the persons is an n-to-n 'drives'-'is driven by' relationship. I would like to query the database to get a list of all the cars that are driven by a specific person (say 'john') and that are also driven by other persons. I would like the query to show per car how many (other) ...

How handles JPA JOINs with available foreign key entry but not available foreign entry?

I wonder how JPA defines to handle following scenario: Table A: | Table B: ID FK_B | ID 1 10 | 10 2 null | 12 3 11 | I want all Table A entries with FK_B NULL or referencing not available Table B entry. public class A implements Serializable { @Id private Long id; @JoinColumn(name = "FK_B", nullable = true) ...

Self Join on variable data

I have a table with the following data: Fiscal Year | Fiscal Quarter | Fiscal Week | Data 2009 | 2 | 22 | 9.5 2009 | 2 | 24 | 8.8 2009 | 2 | 26 | 8.8 2009 | 3 | 28 | 8.8 2009 | 3 | 31 | 9.1 2...

Most efficient way of getting record counts from a related table?

I have a stored procedure that has this select statement in it: select (select Count(FaqId) from faq_read_stats where faqid = Faq.Id) as ReadCount, Faq.* from Faq where Faq.Show = 1 and Faq.Active = 1 This gives me the results I want but it seems like having the ad-hoc select count in there might not be optimized for SQL Ser...

C# Display Join Query in DataGridView at designTime using TableAdapter, etc.

I have a DataGridView I also have some tableAdapters (groupTableAdapter, userTableAdapter) generated from sqlserver database. I have created a JOIN query in userTableAdapter that shows users with their correspoding groupname. Of course, I've got the classic warning Visual Studio throws regarding it is not the original bla bla bla.....

SQL joins and logic

I am working with a SharePoint web part that I am designing in C#. I need to query a SQL Server 2005 database to return the names of machines that are associated with a particular customer, selected by the user. The problem is that there are different types of machines, and the names themselves are stored in several other tables. Here i...

get the first row of a join

Hi guys! I've a query like this: select t1.id, t1.sample, t2.id from table t1 join table t2 on t1.sample = t2.sample and t2.id > t1.id I need to get the first row that satisfy the second condition. Any idea? ...

Select statement using WHERE x IN (SELECT ...)

I have two tables: [ product_to_category --------------------- product_id category_id [ category --------------------- category_id parent_id I need to get all product_id's with a category.parent_id of '39'. Here's what I'm trying, but it's returning empty when there are at least a few hundred: SELECT product_id FROM product_to_cate...

Powershell "join"

Call me stupid, but I'm losing my hair with this one. I have two results from a Get-WmiObject: $cpu = Get-WmiObject -Class Win32_Processor $mb = Get-WmiObject -Class Win32_BaseBoard Now, I can filter and output a CSV file from each one: $cpu | Select-Object Name, Description | ConvertTo-Csv -NoTypeInformation and $mb | Select-Ob...

How to convert Sql query with inner join statement to sql query with Where statement(no inner join in statement)

I have generated the following sql query code in access using the Qbe and converting it to sql.However, i want proof that i did without help so i intend to take out all inner join statements and replace them with the WHERE statement. How do i work it out. Please explain and provide answer. thank you. SQL Query: SELECT Entertainer.Ente...

please help me in this query

I have three tables (user, friends, posts) and two users (user1 and user2). When user1 adds user2 as friend then user1 can see the posts of user2 just like on Facebook. But only the posts after the date when user1 added user2 as friend. My query is like this: $sql = mysql_query("SELECT * FROM posts p JOIN friends f ON p.curren...

MySQL: get records from database and add a COUNT() column to the rows

I'm trying to retrieve books from one table and left join the chapters table. What I need from the second table is just the COUNT() of chapters available for those books and add that value as an extra column called chapters (or something else). My current try looks like this: SELECT b.*, count(c.chapter_nr) as chapters FROM books as b l...