Trying to use table aliases in SQL...

Question

I'm a graphic designer trying my best to understand table aliases, but it's not working. Here's what I have so far:

SELECT colours.colourid                  AS colourid1,
       combinations.manufacturercolourid AS colourmanid1,
       colours.colourname                AS colourname1,
       colours.colourhex                 AS colourhex1,
       combinations.qecolourid2          AS colouridqe2,
       colours.colourid                  AS colourid2,
       colours.colourname                AS colourname2,
       colours.colourhex                 AS colourhex2,
       colours.colourid                  AS colourid3,
       combinations.qecolourid3          AS colouridqe3,
       colours.colourname                AS colourname3,
       colours.colourhex                 AS colourhex3,
       colours.colourid                  AS colourid4,
       combinations.qecolourid4          AS colouridqe4,
       colours.colourname                AS colourname4,
       colours.colourhex                 AS colourhex4,
       combinations.coloursupplierid
FROM   combinations
       INNER JOIN colours
         ON colours.colourid = combinations.manufacturercolourid; 

Now, the idea is that in the colours lookup table, the id will pull the colour code, hex and name from the lookup table so that I can pull the colour code, hex and name for the 4 colours that I'm looking for. I can get this to work, but it only pulls up the first name, code and hex and I'm just not seeing what I'm doing wrong.

Answer 1

You can use table aliases to reduce the amount of typing needed - by adding something like this:

SELECT 
   cl.colourid                  AS colourid1,
   cb.manufacturercolourid AS colourmanid1,
   cl.colourname                AS colourname1,
    ... and so on.....
FROM   
   combinations AS cb
INNER JOIN 
   colours AS cl ON cl.colourid = cb.manufacturercolourid; 

By defining a table alias cb for your table combinations, you can use that shorter alias in your SELECT and other parts of your statement, instead of having to always spell out the entire table name.

But your problem really is in the JOIN - you're only joining once, yet you expect to get four results back....

What you need to do is something like this:

SELECT 
   col1.colourid            AS colourid1,
   cb.manufacturercolourid  AS colourmanid1,
   col1.colourname          AS colourname1,
   col1.colourhex           AS colourhex1,

   cb.qecolourid2           AS colouridqe2,
   col2.colourid            AS colourid2,
   col2.colourname          AS colourname2,
   col2.colourhex           AS colourhex2,
   col2.colourid            AS colourid3,

   cb.qecolourid3           AS colouridqe3,
   col3.colourname          AS colourname3,
   col3.colourhex           AS colourhex3,
   col3.colourid            AS colourid4,

   cb.qecolourid4           AS colouridqe4,
   col4.colourname          AS colourname4,
   col4.colourhex           AS colourhex4,
   cb.coloursupplierid
FROM   
    combinations cb
INNER JOIN colours AS col1 ON col1.colourid = cb.manufacturercolourid
INNER JOIN colours AS col2 ON col2.colourid = cb.qecolourid2
INNER JOIN colours AS col3 ON col3.colourid = cb.qecolourid3
INNER JOIN colours AS col4 ON col4.colourid = cb.qecolourid4

Answer 2

This is not an exhaustive answer, but your problem has to do with your how you are using the JOINs. Table and column aliases do not affect the output result set.

You are selecting the same field names four times, and that is why you are getting strange results.

Answer 3

Your problem is that you are linking in only a single record from the colours table because you only have a single JOIN in your SQL. That record will match the color specified by manufacturer_colour_id.

You may also have a further problem in that your combinations table does not appear to be in proper normal form (although I could be wrong, not knowing the actual nature of the data you're trying to represent).

If I understand your problem correctly, the solution (using your current table structures) will be something more like:

SELECT C1.colourid              AS colourid1,
   CMB.manufacturercolourid     AS colourmanid1,
   C1.colourname                AS colourname1,
   C1.colourhex                 AS colourhex1,
   CMB.qecolourid2              AS colouridqe2,
   C2.colourid                  AS colourid2,
   C2.colourname                AS colourname2,
   C2.colourhex                 AS colourhex2,
   C3.colourid                  AS colourid3,
   CMB.qecolourid3              AS colouridqe3,
   C3.colourname                AS colourname3,
   C3.colourhex                 AS colourhex3,
   C4.colourid                  AS colourid4,
   CMB.qecolourid4              AS colouridqe4,
   C4.colourname                AS colourname4,
   C4.colourhex                 AS colourhex4,
   CMB.coloursupplierid
FROM   combinations CMB
   LEFT OUTER JOIN colours C1
     ON C1.colourid = CMB.manufacturercolourid
   LEFT OUTER JOIN colours C2
     ON C2.colourid = CMB.qecolourid2
   LEFT OUTER JOIN colours C3
     ON C3.colourid = CMB.qecolourid3
   LEFT OUTER JOIN colours C4
     ON C4.colourid = CMB.qecolourid4

What's happening here is that I'm linking the colours table four times, once for each of the colour_id fields in the combinations table. To do so, I need to alias the table name each time so that I know which of the four possible instances of colours to use in the list of returned columns. Also, I'm using OUTER JOINs in the event that one or more colour_id columns might be empty. If that happened with INNER JOINs, the entire row would drop out of the result set.

Answer 4

These are all great, but for some reason when I try to use them, I get an error in the page:

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression

I think I understand how to use the table aliases now, but for some reason, even though I'm sure it should work, the page doesn't like it.