join

MySQL Insert & Joins

i saw this In MySQL, joins work for INSERT, UPDATE, and DELETE statements. It's possible to change data in more than one table when joining tables in an UPDATE or DELETE statement. in an answer for a question from the mysql certification guide. is it true? inserts with joins? an example of it? ...

how to use a like with a join in sql?

I have 2 tables, say table A and table B and I want to perform a join, but the matching condition has to be where a column from A 'is like' a column from B meaning that anything can come before or after the column in B: for example: if the column in A is 'foo'. Then the join would match if column in B is either: 'fooblah', 'somethingfo...

How to write a query showing records from table A without a matching record in table B?

Say I have two tables called A (fields: id, phase, name) and B(fields: id, AID, APHASE, void). I need to show all records from A except for records where A.id = B.AID and A.phase = B.APHASE and void = 0. Environment is MySQL. ...

Can layered subqueries spanning multiple tables reference a parent query?

I have three tables setup: POSTS, DISCUSSIONS, and COMMENTS. The layout is as follows: POSTS id | user_id | title | body | created DISCUSSIONS id | post_id | user_id | title | body | created COMMENTS id | post_id | discussion_id | user_id | title | body | created A post hasMany DISCUSSIONS, which then hasMany COMMENTS. What I need ...

Why does 'HASH JOIN' or 'LOOP JOIN' improve this stored proc?

I have a basic query that goes from 6 seconds to 1 second just by changing one join from LEFT JOIN to LEFT HASH JOIN or 'LEFT LOOP JOIN'. Can anyone explain why this would cause such a large increase in performance and why SQL's optimizer isn't figuring it out on it's own? Here is roughly what the SQL looks like: SELECT a.[ID] FROM...

Count() using Where clause and Left Join

I have a query similar to the following: SELECT users.id FROM users LEFT JOIN sales ON installations.customer = users.id What I would like to say is something like "WHERE count(sales.id) > 4" - meaning that if the user has more than 4 sales assoc with them. I am not sure if I am going about this the wrong way or not though ...

SQL Filter criteria in join criteria or where clause which is more efficient

I have a relatively simple query joining two tables. The "Where" criteria can be expressed either in the join criteria or as a where clause. I'm wondering which is more efficient. Query is to find max sales for a salesman from the beginning of time until they were promoted. Case 1 select salesman.salesmanid, max(sales.quantity) from...

How to sort by count with postgresql?

I have two tables: Companies: (id, name, city) Workers: (id, name) I would like to get all companies and sort them by numbers of employes. The result should give: count | company id | company name | city ------------------------------------------ 90 6 foo corp NY 45 9 bar corp LA 0 ...

Joining to a limited subquery?

I have this releases table in a SQLite3 database, listing each released version of an application: |release_id|release_date|app_id| |==========|============|======| | 1001| 2009-01-01 | 1| | 1003| 2009-01-01 | 1| | 1004| 2009-02-02 | 2| | 1005| 2009-01-15 | 1| So for each app_id, there will be multi...

How to sort by custom count with postgresql?

I have two tables: Companies: (id, name) Workers: (id, name, country) I would like to get all companies and sort them by numbers of employees from a given country. For a query looking for companies that have more workers from the USA, the result should give: #workers from USA | company id | company name ----------------------------...

mysql join and limit

Hi, i have a master table page, and a relative table page_lang.In page_lang i hold the page data for each language. Using the the above code SELECT * FROM pages as p RIGHT JOIN pages_lang as l ON l.pageID=p.pageID i get the rows containing common data form pages and language data from page_lang.All OK The problem is when rty to lim...

Problems counting joined rows with conditional

I am having some problems with MySQL and selecting column count from a joined table. I have a feeling this is going to require a sub-select statement to fetch the rows I would like to join instead of doing it in my primary where condiutional. I am trying to select a list of project which are linked to a certain user. I would also like t...

4 table join so that only 1 response is given for each company instead of a result for each campaign

Thanks for your help! I'd like to output all companyName entries that have uploads across any of their serverFiles as: companies.companyName - count(files.fileID) - sum(serverFiles.uniqueUploads) Initech Ltd. - 11 - 24931 Epiphyte Inc. - 23 - 938821 Here are the relavent parts of my table structure: Table: companies c...

Good object structure for results of my Hibernate left outer join

I need to make an outer join query that retrieves all of the definitions and any properties they have associated with them that are associated with a certain company. I have two Hibernate models objects: class PropertyDefinition { @Id private Long id; @Column private String name; @OneToMany(mappedBy = "propert...

Join DataTable with List<SomeObject>

I have a DataTable and a List of objects. I need to return all rows in the DataTable where a property in the List is a certain value. The List is only used for filtering the datatable (but the filter column isn't containined in the datatable. I'm sure this must be possible with link The DataTable contains: MembershipID Username Pas...

Tricky SQL statement over 3 tables

I have 3 different transaction tables, which look very similar, but have slight differences. This comes from the fact that there are 3 different transaction types; depending on the transaction types the columns change, so to get them in 3NF I need to have them in separate tables (right?). As an example: t1: date,user,amount t2: date,us...

SQL SELECT from multiple tables

How can I get all products from customers1 and customers2 include their customer names? customer1 table cid name1 1 john 2 joe customer2 table cid name2 p1 sandy p2 linda product table pid cid pname 1 1 phone 2 2 pencil 3 p1 pen 4 p2 paper Result should be like this pid cid pname name1 name2 1 1 phone ...

Join query from table with multiple foreign keys to same table primary key

I have a workorder system using SQL Express 2008. I have a table called Workorders that has several personnel that are linked to it via UserID. In the Workorder table I have TechID for the Technician, CustomerID for the Customer, QAID for quality assurance. These are linked back to the User Table via UserID (User Table PK). I want to joi...

SQL: cascade UNION and JOIN

Hi I have a union opertaion between two tables SELECT ID_1, name_1, surname_1, FROM T_ONE UNION SELECT ID_2, name_2, surname_2 FROM TABLE_2 now I want to join the result of this JOIN operation with another one table or even with all TABLE_1. How can I handle this new table result of the UNION. for ex after the previous UN...

Combine two tables from two different SQL Server instances

Hi Everyone, I wanna learn how to join two tables which are located two different SQL Server instances. Thanks in advance. ...