join

group_concat on an empty join in MySQL

Hello, I've got the following problem: I have two tables: (simplified) +--------+ +-----------+ | User | | Role | +--------+ +-----------+ | ID<PK> | | ID <PK> | +--------+ | Name | +-----------+ and M:N relationship between them +-------------+ | User_Role | +-------------+ | User<...

LINQ to DataSet - group by variable field, or join on a variable condition (with sum)

The following query works as long as I can add DataRelations. However, the relation "Table2Table3" may no longer be clearly defined, so I think I need to remove it. The old relation strictly joins on 2 string fields. The new relation exists if Table2.code = Table3.code and Table2.class = Table3.class OR Table2.code = Table3.code and Tabl...

SQL is this equivalent to a LEFT JoIn?

Is this equivalent to a LEFT JOIN? SELECT DISTINCT a.name, b.name FROM tableA a, (SELECT DISTINCT name FROM tableB) as b It seems as though there is no link between the two tables. Is there an easier / more efficient way to write this? ...

When calling CRUD check if "parent" exists with read or join?

All my entities can not be deleted - only deactivated, so they don't appear in any read methods (SELECT ... WHERE active=TRUE). Now I have some 1:M tables on this entities on which all CRUD operations can be executed. What is more efficient or has better performance? My first solution: To add to all CRUD operations: UPDATE ... JOIN ...

mysql multiple queries

Query: SELECT c.Name FROM [Catagories] c SELECT c.Name, bc.CategoryName FROM [Catagories] c INNER JOIN [Business_Categories] bc ON c.BusinessCategoryID = bc.BusinessCategoryID Question: Why don't I have to put this in a WHERE clause? ...

MySQL Search (Sort by Relevance)

Hi guys, Can any one help me how to sort rows by relevance for the following criterion ? `tbluser` - - - - - - - First Name Last Name `tbleduc` - - - - - - - School College University On the search form the user has following fields Name School College University Where School College and University are Optional.. And Name is spl...

SQL join problem

I want to retrieve all records from one table when there are no matches in the second table. So it is kind of the opposite of an inner join. ...

SQL Server 2008, join or no join?

Just a small question regarding joins. I have a table with around 30 fields and i was thinking about making a second table to store 10 of those fields. Then i would just join them in with the main data. The 10 fields that i was planning to store in a second table does not get queried directly, it's just some settings for the data in the ...

Help with SQL statement (JOIN)

Hi everyone, I'm having some trouble with an SQL statement that have to find the number of students attending a course. My Database design look likes this: Table Course: id | course_name Table Student: id | name And to connect the two many-to-many relationship I've an table: Table course_student: id | course_id | student_id What I ...

How do I select from multiple tables in one query with Django?

I have two tables, one "Company" and one "Employee": class Company(models.Model): name = models.CharField(max_length=60) class Employee(models.Model): name = models.CharField(max_length=60) company = models.ForeignField(Company) And I want to list every Employee in a table, with the Company next to it. Which is simple eno...

Simple join gives unnecessary rows,How to get join properly with only matches in both tables

Table 1 Field1 Field2 AA 20 AA 20 AB 12 AC 13 Table2 field3 field4 AA 20 AA 20 AC 13 AD 23 AW 21 output required: newfield field2 field4 AA 20 20 AA 20 20 AC 13 13 I used: select field1 as newfield, t1.field2,t2.field4 from table1 t1 join table2 t2 on t1.field1=t2.field3 This does not give the required output,Ple...

Does clustered index on foreign key column increase join performance vs non-clustered ?

In many places it's recommended that clustered indexes are better utilized when used to select range of rows using BETWEEN statement. When I select joining by foreign key field in such a way that this clustered index is used, I guess, that clusterization should help too because range of rows is being selected even though they all have sa...

sort mysql query by filtered query

I have two mysql queries: $sql = "SELECT * FROM content WHERE threadName LIKE '%$filter%' ORDER BY lastUpdated desc"; and $sql = "SELECT * FROM content ORDER BY lastUpdated desc"; The end result is to have all rows returned from a particular table 'content' but have those that match the variable $filter at the top. Is there either ...

MySQL Rating system (calculating average from two tables).

I have two tables, videos and videos_ratings. The videos table has an int videoid field (and many others but those fields are not important I think) and many records. The videos_ratings table has 3 int fields: videoid, rating, rated_by which has many records (multiple records for each fields from the videos table) but not for all records...

SQL join different tables depending on row information

Suppose I have table A with a field that can be either 1 or 2... How do I select such that for each row in table A, if the field is 1, join the select with table B and if the field is 2, join the select with table C? ...

Joining two tables (through a link), one which may yield multiple rows, together into one result.

Lets say I've got a table listing car brands or models: Cars: Id | Brand ----------- 1 | BMW 2 | Audi 3 | Volvo And I've also got another table which links features. Link: Id | carid | featureid ----------------------- 1 | 1 | 1 2 | 1 | 2 3 | 2 | 2 4 | 3 | 1 5 | 3 | 2 6 | 3 | 3 And I've got the tab...

SQL SERVER 2008 JOIN hints

Hi all, Recently, I was trying to optimise this query UPDATE Analytics SET UserID = x.UserID FROM Analytics z INNER JOIN UserDetail x ON x.UserGUID = z.UserGUID Estimated execution plan show 57% on the Table Update and 40% on a Hash Match (Aggregate). I did some snooping around and came across the topic of JOIN hints. So I added a L...

How to add condition on multiple-join table

Hi, I have those two tables: client: id (int) #PK name (varchar) client_category: id (int) #PK client_id (int) category (int) Let's say I have those datas: client: {(1, "JP"), (2, "Simon")} client_category: {(1, 1, 1), (2, 1, 2), (3, 1, 3), (4,2,2)} tl;dr client #1 has category 1, 2, 3 and client #2 has only category 2 ...

In Perl, how can I wait for threads to end in parallel?

I have a Perl script that launches 2 threads,one for each processor. I need it to wait for a thread to end, if one thread ends a new one is spawned. It seems that the join method blocks the rest of the program, therefore the second thread can't end until everything the first thread does is done which sort of defeats its purpose. I tried...

MySQL - optimising selection across two linked tables

I have two MySQL tables, states and trans: states (200,000 entries) looks like: id (INT) - also the primary key energy (DOUBLE) [other stuff] trans (14,000,000 entries) looks like: i (INT) - a foreign key referencing states.id j (INT) - a foreign key referencing states.id A (DOUBLE) I'd like to search for all entries in trans with...