SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist barbara1712's Avatar
    Join Date
    Apr 2007
    Location
    India
    Posts
    509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    help me with the query

    I am having problem for writing the query.

    The tables are
    table1 =
    id, title, mode, discipline tid(id from table 3)
    1 'title 1' 39 999 1
    2 'title2' 41 335 2
    3 'title3' 41 999 2

    table2 =
    id, type, name
    38 mode web
    41 mode desk
    999 discipline AS
    335 discipline APPT
    12 status Dropped
    15 status enrolled

    table3
    id name status
    1 aaa 12
    2 bbb 15

    I want to display the records from table1

    id title mode discipline co_status
    1 title1 web AS dropped
    2 title2 desk APPT enrolled
    3 title3 desk APPT enrolled


    Help me with the query
    Barbara

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT ...
      FROM table1
    INNER
      JOIN table2 AS m
        ON m.id = table1.mode
    INNER
      JOIN table2 AS d
        ON d.id = table1.discipline
    INNER
      JOIN table3
        ON table3.id = table1.tid
    INNER
      JOIN table2 AS s
        ON s.id = table3.status
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    You have to join the 3 tables.
    I guess all codes in table 1 are present in table 2 and 3, so you can use INNER JOIN.
    Code:
    SELECT
        table1.id
      , table1.title
      , m.name as mode
      , d.name as discipline
      , s.name as co_status
    FROM table1
    INNER JOIN table2 AS m
    ON table1.mode = m.id
    INNER JOIN table2 as d
    ON table1.discipline = d.id
    INNER JOIN table3
    ON table1.tid = table3.id
    INNER JOIN table2 AS s
    ON table3.status = s.id
    I also presumed that the unique key in table2 is 'id'. If it's 'id' + 'type', then you'll have to add the corresponding type in the ON of each join to table2.


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
  •