SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Guru
    Join Date
    Feb 2009
    Posts
    994
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by oikram View Post
    Can I have an example of the above, so that I can study and try to apply it to my code?
    sure
    Code:
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Feb 2009
    Posts
    994
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code MySQL:
    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:
    Code MySQL:
    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:
    Code MySQL:
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by oikram View Post
    However, neither rebromung, or dippflesob, belongs to frabilgimjer table.
    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

    Quote Originally Posted by oikram View Post
    Why can't we just say, Join TableA with TableB no matter where they join ?
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Feb 2009
    Posts
    994
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    Ok.

    Quote Originally Posted by r937 View Post
    because it wouldn't make any sense
    I supposed that as well. But I still don't understand.

    Quote Originally Posted by r937 View Post
    you ~must~ indicate which columns to join on, otherwise you'll get garbage
    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:
    Code MySQL:
    klimperstarber | confliprig | rebromung | dippflesob | scrimflabat | horbliston | opsilfrummer | whipfintaggle

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

    Quote Originally Posted by r937 View Post
    there is an exception, the NATURAL join, but please don't go there
    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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by oikram View Post
    Is there a way for us to see that "virtual" table ?
    sure, just use the dreaded evil "select star"
    Code:
    SELECT * FROM ... -- joined_tables
    Quote Originally Posted by oikram View Post
    I'm not getting the desired result. :s
    what desired result?

    maybe we should switch to using your table/column names, and not these fictitious ones...
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Feb 2009
    Posts
    994
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok... true.

    Here:

    Code MySQL:
    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

  8. #8
    SitePoint Guru
    Join Date
    Feb 2009
    Posts
    994
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...


  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by oikram View Post
    I want to display the pair desafios.titulo and equipas.nome when, desafiosporequipas.estado = 1
    order by data_concluido
    Code:
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Guru
    Join Date
    Feb 2009
    Posts
    994
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code MySQL:
    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:

    Code MySQL:
    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?

    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?




  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by oikram View Post
    Ok. And I also notice that the order by witch we do the joins IS relevant.
    yes, all you have to do is remember not to join to a table that you haven't mentioned yet

    this --
    Code:
      FROM equipas e
    INNER
      JOIN desafios d
        ON d.cod_desafio = de.cod_desafioFk
    doesn't make sense

    Quote Originally Posted by oikram View Post
    What was the garbage on your previous answer?
    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...
    Code:
    SELECT desafios.titulo
         , equipas.nome
      FROM desafios
    INNER
      JOIN desafiosporequipa
        ON 1=1
    INNER
      JOIN equipas
        ON 2=2
    ORDER
        BY desafiosporequipa.data_concluido
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Guru
    Join Date
    Feb 2009
    Posts
    994
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    try this if you really want to see the garbage...
    Code:
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •