Join sintax help - not even know if I really need a join - please advice. :)

oikram · November 22, 2010, 2:13pm

Hello all,

This is a common scenario but I’m struggling here.
I have 3 tables. The middle one relates table 1 and 3 by having a Fk from both tables.

I would like to list some data from table 1 and same data from the table 3.
But that data should be organized by ordering a given column in table 2.

Can I have an example of the above, so that I can study and try to apply it to my code?

If you prefer, I can well provide the create tables here, I was just thinking that, like this, I can properly learn.

K. Regards,
Márcio

r937 · November 22, 2010, 2:46pm

sure

SELECT frabilgimjer.klimperstarber
     , frabilgimjer.confliprig
     , haplistplogget.rebromung
     , haplistplogget.dippflesob
  FROM frabilgimjer
INNER
  JOIN quistipunctous
    ON quistipunctous.scrimflabat = frabilgimjer.horbliston
INNER
  JOIN haplistplogget
    ON haplistplogget.opsilfrummer = quistipunctous.whipfintaggle
ORDER
    BY quistipunctous.sloptizinger

oikram · November 22, 2010, 3:11pm

Ok. I hope I can clear this out.

Please don’t abandon me.

The first think that puzzles me here is:
We are doing a:


SELECT haplistplogget.rebromung
         , haplistplogget.dippflesob
FROM frabilgimjer

However, neither rebromung, or dippflesob, belongs to frabilgimjer table.

I can arrange the sintax for reading proposes only like so:


SELECT frabilgimjer.klimperstarber
     , frabilgimjer.confliprig
     , haplistplogget.rebromung
     , haplistplogget.dippflesob
  FROM frabilgimjer INNER JOIN quistipunctous

Where the from is no longer frabilgimjer BUT frabilgimjer inner joined with quistipunctous.

Correct?

Still however, rebromung and dippflesob don’t have a match.

They only appear here:


SELECT frabilgimjer.klimperstarber
     , frabilgimjer.confliprig
     , haplistplogget.rebromung
     , haplistplogget.dippflesob
  FROM frabilgimjer
INNER
  JOIN quistipunctous
    ON quistipunctous.scrimflabat = frabilgimjer.horbliston
INNER
  JOIN haplistplogget
    ON haplistplogget.opsilfrummer = quistipunctous.whipfintaggle

Now, they do have a match.

From here, I can understand that we are actually joining three tables.

I can also tell that:
frabilgimjer is joined with quistipunctous
And quistipunctous is joined with haplistplogget

Then, forcefully, frabilgimjer will be joined with haplistplogget (is this correct, I miss my basic Aristotelian logic :s);

If all the above are correct deductions:
Why do we need a specific column to join tables?
Why can’t we just say, Join TableA with TableB no matter where they join ?

Thanks a lot in advance,
Márcio

r937 · November 22, 2010, 3:15pm

you cannot compare the contents of the SELECT clause to only the first table in the join

when all the tables are joined, they form a single large “virtual” table, with columns and rows

then the SELECT clause can pick any of those columns

because it wouldn’t make any sense

you ~must~ indicate which columns to join on, otherwise you’ll get garbage

there is an exception, the NATURAL join, but please don’t go there

oikram · November 22, 2010, 3:42pm

Ok.

I supposed that as well. But I still don’t understand.

By garbage you mean, unnecessary data ?

So, that virtual table you are talking about, is composed by, on this case:

frabilgimjer.klimperstarber
frabilgimjer.confliprig
haplistplogget.rebromung
haplistplogget.dippflesob
quistipunctous.scrimflabat
frabilgimjer.horbliston
haplistplogget.opsilfrummer
quistipunctous.whipfintaggle

Is there a way for us to see that “virtual” table ?

I mean will it be something like:

klimperstarber | confliprig | rebromung | dippflesob | scrimflabat | horbliston | opsilfrummer | whipfintaggle

Or, or ON columns will never join this “virtual table” ?

I could help it, I had to have a look. Worry not. I got frightened and I was out right away… but now I know there is some natural thing out there…

What last question, does the ORDER by witch the table names appear on the query, on this case, matters?

I’m not getting the desired result. :s

Thanks a again. (:
Márcio

r937 · November 22, 2010, 4:06pm

sure, just use the dreaded evil “select star”

SELECT * FROM ... -- [I]joined_tables[/I]

what desired result?

maybe we should switch to using your table/column names, and not these fictitious ones…

oikram · November 22, 2010, 4:17pm

Ok… true.

Here:


CREATE TABLE `desafios` (
 `cod_desafio` int(11) NOT NULL AUTO_INCREMENT,
 `titulo` varchar(255) NOT NULL,
 `descricao` text NOT NULL,
 `pontos` int(11) NOT NULL,
 `cod_categoriaFk` int(11) DEFAULT NULL,
 PRIMARY KEY (`cod_desafio`),
 KEY `cod_categoriaFk` (`cod_categoriaFk`),
 KEY `pontos` (`pontos`),
 CONSTRAINT `desafios_ibfk_1` FOREIGN KEY (`cod_categoriaFk`) REFERENCES `categorias` (`cod_categoria`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8


CREATE TABLE `desafiosporequipa` (
 `cod_equipaFk` int(11) DEFAULT NULL,
 `cod_desafioFk` int(11) DEFAULT NULL,
 `estado` tinyint(1) NOT NULL COMMENT '0| Pendente 1|concluido 2|recusado',
 `prova` varchar(255) NOT NULL,
 `data_concluido` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'data da conclusao do desafio',
 KEY `cod_equipaFk` (`cod_equipaFk`),
 KEY `cod_desafioFk` (`cod_desafioFk`),
 KEY `estado` (`estado`),
 CONSTRAINT `desafiosporequipa_ibfk_1` FOREIGN KEY (`cod_equipaFk`) REFERENCES `equipas` (`cod_equipa`),
 CONSTRAINT `desafiosporequipa_ibfk_2` FOREIGN KEY (`cod_desafioFk`) REFERENCES `desafios` (`cod_desafio`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


CREATE TABLE `equipas` (
 `cod_equipa` int(11) NOT NULL AUTO_INCREMENT,
 `nome` varchar(100) NOT NULL,
 PRIMARY KEY (`cod_equipa`),
 UNIQUE KEY `nome` (`nome`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8

Table desafiosequipas relates equipas id with desafios id.

I want to display the pair desafios.titulo and equipas.nome when, desafiosporequipas.estado = 1
order by data_concluido

I can provide more details if the above is not clear. I realize my struggle to explain it and understand it.

Thanks a lot, really.

Márcio

oikram · November 22, 2010, 4:36pm

The hard is getting the pair… but I believe I know how to get the par.

ON cod_desafios = cod_desafiosFk

and later

ON cod_equipas = cod_equipasFk

I will give it a try…

r937 · November 22, 2010, 4:40pm

SELECT desafios.titulo
     , equipas.nome
  FROM desafios
INNER
  JOIN desafiosporequipa
    ON desafiosporequipa.cod_desafioFk = desafios.cod_desafio
   AND desafiosporequipa.estado = 1
INNER
  JOIN equipas
    ON equipas.cod_equipa = desafiosporequipa.cod_equipaFk
ORDER
    BY desafiosporequipa.data_concluido

oikram · November 22, 2010, 5:16pm

Thanks a lot for your time and guidance. yes. That was exactly what I have after half an hour ehehe

Ok. And I also notice that the order by witch we do the joins IS relevant.

If I do:


SELECT e.nome
     , d.titulo
  FROM equipas e
INNER
  JOIN desafios d
    ON d.cod_desafio = de.cod_desafioFk
INNER
  JOIN desafiosporequipa de
    ON de.cod_equipaFk = e.cod_equipa

I get “de.cod_desafioFk unknown”.

If I switch like so:


SELECT e.nome
     , d.titulo
  FROM equipas e
INNER
  JOIN desafiosporequipa de
    ON de.cod_equipaFk = e.cod_equipa
INNER
  JOIN desafios d
    ON d.cod_desafio = de.cod_desafioFk

We get it ok.

This was due to the fact that, on the first case, desafiosporequipa was not yet joined right? :D:D

Resumé:
And with this exercise I’ve learned as well the the ON clause, is important, at least on this case. It is with this ON clause that I was able to relate the tableA with tableB and tableC with tableB so that I can get the pairs of tableB.

The * as help me out figure that, (and OMG will I always be this newbie?) we get on the result what we put after the select ONLY. So the fields on the ON clause, will not appear. (hopefully I’m not wrongly generalizing).

BUT this leads me to a prior question.
What was the garbage on your previous answer?

(:

r937 · November 22, 2010, 5:24pm

yes, all you have to do is remember not to join to a table that you haven’t mentioned yet

this –

  FROM equipas e
INNER
  JOIN desafios d
    ON d.cod_desafio = de.cod_desafioFk

doesn’t make sense

if you do not specify ON conditions which match rows based on column values, then your joins effectively become cross joins

try this if you really want to see the garbage…

SELECT desafios.titulo
     , equipas.nome
  FROM desafios
INNER
  JOIN desafiosporequipa
    ON 1=1
INNER
  JOIN equipas
    ON 2=2
ORDER
    BY desafiosporequipa.data_concluido

oikram · November 22, 2010, 7:58pm

r937:

try this if you really want to see the garbage…

SELECT desafios.titulo
     , equipas.nome
  FROM desafios
INNER
  JOIN desafiosporequipa
    ON 1=1
INNER
  JOIN equipas
    ON 2=2
ORDER
    BY desafiosporequipa.data_concluido

Ok… now I see the garbage, and I understand that That garbage is indeed extra data that we don’t need.

I still don’t understand why do we get so much data with that query.
But I don’t understand that because I don’t know the meaning of
1=1 and 2=2.

What do they mean?

I must also add, however, that I’ve also tried the same query without any ON CLAUSE at all, and I got a even LONGER list of usefulness data.

What is he doing there?

Thanks a lot again,
Márcio