join

In SQL, we can use "Union" to merge two tables. What are different ways to do "Intersection"?

In SQL, there is an operator to "Union" two tables. In an interview, I was told that, say one table has just 1 field with 1, 2, 7, 8 in it, and another table also has just 1 field with 2, and 7 in it, how do I get the intersection. I was stunned at first, because I never saw it that way. Later on, I found that it is actually a "Join" ...

For SQL, when did it start to be desirable to always use the words "Inner Join" instead of implicitly joinly?

For SQL, when did it start to be desirable to always use the words "Inner Join" instead of implicitly joining by: select * from t1, t2 where t1.ID = t2.ID; ? Is it just for style or to distinguish between outer join or are there other reasons for it? ...

In SQL, can we always write an inner join statement as a main query and subquery?

In SQL, can we always write an inner join statement as a main query and subquery? For example, select * from gifts g where g.giftID in (select giftID from sentGifts); can do a join and show the gifts sent in the sentGifts table, but it won't be able to show the sentTime because that is inside the subquery? ...

Why does this Grails/HQL query with a JOIN return Lists of pairs of domain classes?

I'm having trouble figuring out how to do a "join" in Groovy/Grails and the return values I get person = User.get(user.id) def latestPhotosForUser = PhotoOwner.findAll("FROM PhotoOwner AS a, PhotoStorage AS b WHERE (a.owner=:person AND a.photo = b)", [person:person], [max:3]) latestPhotosForUser isn't a list of PhotoOwners. It's a lis...

In SQL, can we always write an inner join statement as a main query and subquery if we only want to find the intersection?

In SQL, can we always write an inner join statement as a main query and subquery or vice versa if we only want to find the intersection? For example, select * from gifts g where g.giftID in (select giftID from sentGifts); can do a join and show the gifts sent in the sentGifts table, but it won't be able to show the sentTime because ...

MySQL Join issue

Hi, I have the following tables: --table sportactivity-- sport_activity_id, home_team_fk, away_team_fk, competition_id_fk, date, time (tuple example) -> 1, 33, 41, 5, 2010-04-14, 05:40:00 --table teams-- team_id, team_name (tuple example) -> 1, Algeria Now I have the following SQL statment that I use to extract Team A vs Team B...

MySQL join with a "bounce" off a third table

I have 3 MySQL tables. companies with company_id and company_name products with product_id and company_id names with product_id, product_name and other info about the product I'm trying to output the product_name and the company_name in one query for a given product_id. Basically I need information from the names and companies table...

Fetch last item in a category that fits specific criteria

Let's assume I have a database with two tables: categories and articles. Every article belongs to a category. Now, let's assume I want to fetch the latest article of each category that fits a specific criteria (read: the article does). If it weren't for that extra criteria, I could just add a column called last_article_id or something s...

In SQL or MySQL, can we join a table and a subquery result?

Can we join a table with the result of a subquery, such as: select name from gifts LEFT OUTER JOIN (select giftID from gifts) ... If not, can it be done by some methods, such as creating a temporary table? P.S. Can a subquery only appear using IN or NOT IN, or EXISTS or NOT EXISTS? ...

In SQL / MySQL, what is the difference between "ON" and "WHERE" in a join statement?

The following statements give the same result (one is using "on", and the other using "where"): mysql> select * from gifts INNER JOIN sentGifts ON gifts.giftID = sentGifts.giftID; mysql> select * from gifts INNER JOIN sentGifts WHERE gifts.giftID = sentGifts.giftID; I can only see in a case of a Left Outer Join finding the "unmatched"...

whats the best way to join an array into a readable list

I've got an array listing days of the week: days = ['Monday', 'Tuesday', 'Wednesday'] Whats the easiest / best way to output it in a human readable format: Monday, Tuesday and Wednesday The best I have is a rather ugly: ', '.join(days[:-2]+['']) + ' and '.join(days[-2:]) ...

MySQL query pulling from two tables, display in correct fields

I'm trying to select all fields in two separate tables as long as they're sharing a common ID. //mysql query $result = mysql_query("SELECT * FROM project, links WHERE project.id = links.id and project.id = $clientID") //displaying the link if ($row['url'] != null){ echo "<div class='clientsection' id='links'>Links</div>"; ec...

Appending a prefix when using join in Perl

I have an array of strings that I would like to use the join function on. However, I would like to prefix each string with the same string. Can I do this in one line as opposed to iterating through the array first and changing each value before using join? Actually it's a lil bit trickier. The prefix is not part of the join separator. M...

using a JOIN in an UPDATE in SQL

Hi, I'm having trouble formulating a legal statement to double the statuses of the suppliers (s) who have shipped (sp) more than 500 units. I've been trying: update s set s.status = s.status * 2 from s join sp on (sp.sno = s.sno) group by sno having sum(qty) > 500; however I'm getting this error from Mysql: ERROR 1064 (4200...

Problem using mysql joins

I'm fairly new to mysql and I have no idea if I'm heading in the right direction but I'm having trouble with a mysql query. I basically have a table of users id name ---- -------- 1 user1 2 user2 3 user3 4 user4 as well as a table of user attributes id userid at...

In PHP + MySQL, How do I join many tables with conditions

Hi, I'm trying to get the users full activity throughout the website. I need to Join many tables throughout the database, with that condition that it is one user. What I currently have written is: SELECT * FROM comments AS c JOIN rphotos AS r ON c.userID = r.userID AND c.userID = '$defineUserID'; But What it is returning is ever...

Using MySQL to generate daily sales reports with filled gaps, grouped by currency

I'm trying to create what I think is a relatively basic report for an online store, using MySQL 5.1.45 The store can receive payment in multiple currencies. I have created some sample tables with data and am trying to generate a straightforward tabular result set grouped by date and currency so that I can graph these figures. I want to...

mysql select multiple rows in join

Hi-- I have a simple mySQL problem-- I have two tables, one is a user's table, and one is a photos table (each user can upload multiple photos). I'd like to write a query to join these tables, so I can pull all photos associated with a user (up to a certain limit). However, when I do something obvious like this: SELECT *.a, *.b FRO...

SQL Join query help

Hi I have 2 tables A and B with the following columns Table A - id,bId,aName,aVal Table B - id,bName where A.bId is the same as B.id. I want a result set from a query to get A.id, A.aName, B.bName where A.bId=B.id OR A.id, A.aName, "" when A.bId=0. In both cases, only those records should be considered where A.aVal LIKE "aVal" Can ...

Select Query Joined on Two Fields?

I've got a few tables in an access database: ID | LocationName 1 | Location1 2 | Location2 ID | LocationID | Date | NumProductsDelivered 1 | 1 | 12/10 | 3 2 | 1 | 01/11 | 2 3 | 1 | 02/11 | 2 4 | 2 | 11/10 | 1 5 | 2 | 12/10 | 1 ID | LocationID | Date | NumEm...