SQL syntax joining tables

Question

Hello,

I have a SQL syntax question.

Here are my tables:

default_caps:
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| idx   | int(11)    | NO   | PRI | NULL    | auto_increment |
| tab   | int(11)    | NO   |     | NULL    |                |
| cap   | tinyint(1) | NO   |     | 0       |                |
| value | double     | YES  |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+

convertEntries:
+----------------+------------+------+-----+---------+----------------+
| Field          | Type       | Null | Key | Default | Extra          |
+----------------+------------+------+-----+---------+----------------+
| idx            | int(11)    | NO   | PRI | NULL    | auto_increment |
| convertTable   | int(3)     | NO   |     | 0       |                |
| convertEntry   | int(2)     | NO   |     | 0       |                |
| cap            | tinyint(1) | NO   |     | 1       |                |
| capTable       | int(2)     | NO   |     | 1       |                |
+----------------+------------+------+-----+---------+----------------+

I want to select rows from convertEntries, and in each row of the result, I want to include default_caps.value such that default_caps.tab equals convertEntries.capTable, and default_caps.cap equals convertEntries.cap.

So if default_caps looks like this:

+-----+-----+-----+---------+
| idx | tab | cap | value   |
+-----+-----+-----+---------+
|   1 |   1 |   0 | 8650.75 |
|   2 |   1 |   1 |      50 |
|   3 |   1 |   7 |     350 |
|   2 |   2 |   0 |    9000 |
|   2 |   2 |   1 |     100 |
|   3 |   2 |   7 |     200 |
+-----+-----+-----+---------+

And convertEntries looks like this (with some columns omitted):

+-----+----------+-----+
| idx | capTable | cap |
+-----+----------+-----+
|   1 |        1 |   1 |
|   2 |        1 |   1 |
|   3 |        1 |   1 |
|   4 |        1 |   1 |
|   5 |        1 |   1 |
|   6 |        1 |   1 |
|   7 |        1 |   7 |
|   8 |        1 |   7 |
|   9 |        1 |   7 |
|  10 |        1 |   0 |
|  11 |        1 |   0 |
|  12 |        1 |   0 |
+-----+----------+-----+

I want a result like this:

+-----+----------+-----+---------+
| idx | capTable | cap | value   |
+-----+----------+-----+---------+
|   1 |        1 |   1 |      50 |
|   2 |        1 |   1 |      50 |
|   3 |        1 |   1 |      50 |
|   4 |        1 |   1 |      50 |
|   5 |        1 |   1 |      50 |
|   6 |        1 |   1 |      50 |
|   7 |        1 |   7 |     350 |
|   8 |        1 |   7 |     350 |
|   9 |        1 |   7 |     350 |
|  10 |        1 |   0 | 8650.75 |
|  11 |        1 |   0 | 8650.75 |
|  12 |        1 |   0 | 8650.75 |
+-----+----------+-----+---------+

Thanks!

Answer 1

SELECT convertEntries.idx, convertEntries.capTable, convertEntries.cap, 
  default_caps.value
FROM convertEntries
LEFT JOIN default_caps 
  ON convertEntries.cap = default_caps.cap 
  AND convertEntries.capTable = default_caps.tab

Answer 2

select 
  a.idx,
  a.cap, 
  a.capTable, 
  b.Value
from convertEntries a
inner join default_caps b on  a.tab = b.captable
                          and a.cap = b.cap

Answer 3

This is a simple join, the way i see it

From ConvertEntries e
  Left Join default_caps c on e.tab=c.captable and e.cap=c.cap

I did it as a left join, because the schema seems likely to have data problems. If you inner join and the appropriate lookup doesn't exist in default_caps, the record will be ignored.

Answer 4

I would suggest using more meaningful column names, but:

SELECT convertEntries.idx
  , convertEntries.capTable
  , convertEntries.cap
  , default_caps.value
 FROM convertEntries
  INNER JOIN default_caps
    ON default_caps.tab = convertEntries.capTable
      AND default_caps.cap = convertEntries.cap