Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Creating query that returns id if condition is matched in rows from two tables

Tags:

sql

postgresql

I'm learning SQL/dbms and using Postgres. I want to return rows that both have a certain value in a certain column. For example in tables Carpets and Curtains, I want to get the ids of rows where the colour is 'light yellow'. I think I need JOIN for this, but am not sure what type.

Here's what I've got:

SELECT id
  FROM Carpets
  WHERE colour = 'light yellow'
        INNER JOIN Curtains ON Carpets.colour = Curtains.colour;

Both tables have the id attribute.

About learning JOIN, which one should I learn first? I'm shooting my self in the foot if I try learning all of them at once (as different resources included different 'variants').

IMPORTANT I was looking for an answer where the id would be returned only if both curtains and carpet were 'light yellow'.

like image 260
Celeritas Avatar asked Sep 16 '12 02:09

Celeritas


People also ask

How can I get matched records from two tables in SQL?

(INNER) JOIN : Returns records that have matching values in both tables. LEFT (OUTER) JOIN : Returns all records from the left table, and the matched records from the right table. RIGHT (OUTER) JOIN : Returns all records from the right table, and the matched records from the left table.

How would you return data from 2 tables even if there are no matches?

The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in the right table; the join will still return a row in the result, but with NULL in each column from the right table.

How can I get data from two tables in a single query?

From multiple tables To retrieve information from more than one table, you need to join those tables together. This can be done using JOIN methods, or you can use a second SELECT statement inside your main SELECT query—a subquery.


2 Answers

Have read your question on Meta about this particular question, let me explain why all three answers are indeed correct - as is the way that you worked it out.

I have included examples of all three answers and the schema that they are working on:

Database changed
mysql> create table carpet(id int(3), material varchar(10), color varchar(15));
Query OK, 0 rows affected (0.02 sec)

mysql> create table curtain(id int(3), material varchar(10), color varchar(15));
Query OK, 0 rows affected (0.00 sec)

(bunch of insert statements)

mysql> select * from carpet;
+------+-----------+--------------+
| id   | material  | color        |
+------+-----------+--------------+
|    1 | wool      | Light Yellow |
|    2 | wool      | Beige        |
|    3 | polyester | Light Yellow |
|    4 | polyester | Light Red    |
+------+-----------+--------------+
4 rows in set (0.00 sec)

mysql> select * from curtain;
+------+----------+--------------+
| id   | material | color        |
+------+----------+--------------+
|    1 | Velvet   | Purple       |
|    2 | cotton   | White        |
|    3 | cotton   | Light Yellow |
|    4 | cotton   | Light Blue   |
+------+----------+--------------+
4 rows in set (0.00 sec)

An intersect uses two select statements and brings back matching results. In this case, you are looking for all the rows that have a matching color of 'Light Yellow'.

I can't give you an example in MySQL as it doesn't support it (As you can see below, it's not needed to give the same results).

A union query of two select statements each with a where clause only allowing the color of 'Light Yellow' will return the same data. Although a union can be used to return data that doesn't match, the where clause in each select statement means that it will ondeed only return the rows that you want.

mysql> select id, material, color from carpet
    -> union 
    -> select id, material, color from curtain;
+------+-----------+--------------+
| id   | material  | color        |
+------+-----------+--------------+
|    1 | wool      | Light Yellow |
|    2 | wool      | Beige        |
|    3 | polyester | Light Yellow |
|    4 | polyester | Light Red    |
|    1 | Velvet    | Purple       |
|    2 | cotton    | White        |
|    3 | cotton    | Light Yellow |
|    4 | cotton    | Light Blue   |
+------+-----------+--------------+
8 rows in set (0.00 sec)

Aww, that's bad right? Of course, we didn't specify the where clause:

mysql> select id, material, color from carpet where color='Light Yellow'
    -> union
    -> select id, material, color from curtain where color='Light Yellow';
+------+-----------+--------------+
| id   | material  | color        |
+------+-----------+--------------+
|    1 | wool      | Light Yellow |
|    3 | polyester | Light Yellow |
|    3 | cotton    | Light Yellow |
+------+-----------+--------------+
3 rows in set (0.00 sec)

A join between two tables on the color will allow you to return the rows from both the tables in a single row of data. You can specify the join on the two tables for the item color, and use a where clause to only return the rows that you are looking for.

mysql> select a.id, a.material, a.color, b.id, b.material 
    -> from curtain a join carpet b on a.color=b.color;
+------+----------+--------------+------+-----------+
| id   | material | color        | id   | material  |
+------+----------+--------------+------+-----------+
|    3 | cotton   | Light Yellow |    1 | wool      |
|    3 | cotton   | Light Yellow |    3 | polyester |
+------+----------+--------------+------+-----------+
2 rows in set (0.00 sec)

As you can see, this has returned only the rows with a matching color and allowed you to have columns from both tables in a single row of your resultset.

Now, I clearly didn't plan this very well as I have no other matching results apart from the 'Light Yellow' in both tables, so if I add a few more entries in we get this:

mysql> select * from curtain;
+------+----------+--------------+
| id   | material | color        |
+------+----------+--------------+
|    1 | Velvet   | Purple       |
|    2 | cotton   | White        |
|    3 | cotton   | Light Yellow |
|    4 | cotton   | Light Blue   |
|    5 | Wool     | White        |
|    6 | Fluff    | Beige        |
+------+----------+--------------+
6 rows in set (0.00 sec)

mysql> select * from carpet;
+------+-----------+--------------+
| id   | material  | color        |
+------+-----------+--------------+
|    1 | wool      | Light Yellow |
|    2 | wool      | Beige        |
|    3 | polyester | Light Yellow |
|    4 | polyester | Light Red    |
|    5 | Fluff     | Light Blue   |
+------+-----------+--------------+
5 rows in set (0.00 sec)

Now we can run that again, and this time get:

mysql> select a.id, a.material, a.color, b.id, b.material 
    -> from curtain a join carpet b on a.color=b.color;
+------+----------+--------------+------+-----------+
| id   | material | color        | id   | material  |
+------+----------+--------------+------+-----------+
|    3 | cotton   | Light Yellow |    1 | wool      |
|    3 | cotton   | Light Yellow |    3 | polyester |
|    4 | cotton   | Light Blue   |    5 | Fluff     |
|    6 | Fluff    | Beige        |    2 | wool      |
+------+----------+--------------+------+-----------+
4 rows in set (0.00 sec)

Oh noes!

This is now where we use the join and the where clause together:

mysql> select a.id, a.material, a.color, b.id, b.material 
    -> from curtain a join carpet b on a.color=b.color 
    -> where a.color='Light Yellow';
+------+----------+--------------+------+-----------+
| id   | material | color        | id   | material  |
+------+----------+--------------+------+-----------+
|    3 | cotton   | Light Yellow |    1 | wool      |
|    3 | cotton   | Light Yellow |    3 | polyester |
+------+----------+--------------+------+-----------+
2 rows in set (0.00 sec)

You see, in SQL there are often more ways to get the same result through different means than there are variations of the same data in your tables.

Edit: Okay, so if you only want rows where all the data matches, just include it in the join syntax:

mysql> select a.id, a.material, a.color, b.id, b.material 
    -> from curtain a 
    -> join carpet b on a.color=b.color
    -> and a.id=b.id
    -> where a.color='Light Yellow';
+------+----------+--------------+------+-----------+
| id   | material | color        | id   | material  |
+------+----------+--------------+------+-----------+
|    3 | cotton   | Light Yellow |    3 | polyester |
+------+----------+--------------+------+-----------+
1 row in set (0.00 sec)

As you can see, now we tell the join that both the id and color fields must match between the two tables - and the results speak for themselves. Now, in this case, I technically still didn't match ALL the columns as the material is different. If you wanted to match further, the query would not return any results as I have no matching records where the id, material AND color match, but the syntax would be as follows:

mysql> select a.id, a.material, a.color, b.id, b.material 
    -> from curtain a 
    -> join carpet b on a.color=b.color
    -> and a.id=b.id
    -> and a.material=b.material
    -> where a.color='Light Yellow';
Empty set (0.00 sec)

On that note though, you in most cases, you don't want all the columns to match. Very often tables have an ID that is only used for that table and is an automatically incrementing value. You want to use it to identify a unique row in that table, but not to use it to match unrelated tables. If anything, I would have suggested that you match on material and color - but leave the ID out of it.

like image 189
Fluffeh Avatar answered Sep 29 '22 10:09

Fluffeh


If you want the result for matching in two table, then try this:

select id
  from curtains
      ,carpets
  where curtain.color = carpets.color;

This will return id where curtain.color = carpets.color

like image 33
Shriram Sapate Avatar answered Sep 29 '22 09:09

Shriram Sapate