Results 1 to 6 of 6

Thread: Mysql Query get name twise

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jul 2002
    Posts
    115

    Mysql Query get name twise

    Hi
    someone who can help me with this Query

    I need to get List from this Table but the problem is how can I get fiber name from Fiber twise in the list from this ID C_Fiber_INN_ID and C_Fiber_Out_ID


    1 C001 INN A 1 OUT B 1
    1 C001 INN A 2 OUT B 2


    Fiber
    --F_ID
    --F_Name
    --F_Nr

    1 A 1
    2 A 2
    3 B 1
    4 B 2


    Connection
    --C_ID
    --C_Name
    --C_In
    --C_Fiber_INN_ID
    --C_Out
    --C_Fiber_Out_ID

    1 C001 INN 1 OUT 3
    2 C001 INN 2 OUT 4

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: Mysql Query get name twise

    If you're asking us how to write SQL for a MySQL database then why have you posted in the VB.NET forum? There's no connection to VB at all in your question. Even if the query will be executed from a VB app, the SQL would still be the same if the app was written in any other language. I've asked the mods to move this thread to the Database Development forum.

    As for the question, you simply join the same table twice, using an alias for each one, and then use the alias to refer to that instance of the table in the rest of the code. I don't really use MySQL so I'm not exactly sure how it uses aliases but I would assume that it is similar to SQL Server where you simply put the alias immediate after the table name, e.g. "INNER JOIN SomeTable SomeAlias", or use the 'AS' keyword, e.g. "INNER JOIN SomeTable AS SomeAlias". I'm sure that you can find out easily enough with a web search. If I'm reading your question correctly then you would need something like this:
    SQL Code:
    1. SELECT c.C_ID,
    2.        c.C_Name,
    3.        c.C_In,
    4.        fin.F_Naame,
    5.        c.C_Out,
    6.        fout.F_Name
    7. FROM Connection c INNER JOIN Fiber fin
    8. ON c.C_Fiber_INN_ID = fin.F_ID INNER JOIN Fiber fout
    9. ON c.C_Fiber_Out_ID = fout.F_ID
    Notice that I also aliased the Connection table even though I didn't have to. You may or may not choose to do that but I tend to alias every table in a SQL query pretty much every time because it makes referring to them easier in the rest of the code, e.g. I didn't have to put "Connection" over and over in the SELECT clause.

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Mysql Query get name twise

    Thread moved to the 'Database Development' forum - which is where you should always post SQL questions

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Jul 2002
    Posts
    115

    Re: Mysql Query get name twise

    Thanks jmcilhinney

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: Mysql Query get name twise

    Quote Originally Posted by jmcilhinney View Post
    I didn't have to put "Connection" over and over in the SELECT clause.
    I guess I out to also point out that I didn't need to refer to the tables at all in the SELECT clause because the column names weren't ambiguous so, from that perspective, I could have not aliased Connection and still not had to refer to the table repeatedly but I think that the table references in the SELECT clause do add some clarity.

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Mysql Query get name twise

    Quote Originally Posted by jmcilhinney View Post
    I guess I out to also point out that I didn't need to refer to the tables at all in the SELECT clause because the column names weren't ambiguous so, from that perspective, I could have not aliased Connection and still not had to refer to the table repeatedly but I think that the table references in the SELECT clause do add some clarity.
    I find that as soon as I alias one table, I alias them all, and I always use the alias name for the columns even if it's not ambiguous... habits formed on small queries will help on larger queries, and nothing is more frustrating that trying to find out which of the dozen tables that's been joined is the owner of a particular column.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width