locked
combine columns from two tables RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    User-2060576634 posted

    hi everyone..

    I used to have an exam-schedule Table including the following columns:

    [ExamId][Title][Date][Item1][Item2][Item3]..[Item6]

    which I could easily generate a dynamic html table from it.. but then I realized that it needs normalization ..  so I moved the item columns to a new table containing these columns:
    [ItemId][Item]

    Now, I wonder how I should combine the two tables to dynamically generate the former html table once again?

    Monday, January 19, 2015 8:37 PM

Answers

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    User379720387 posted

    You would need a column in exam-schedule that links to ItemId in the second table

    ExamId, Title, Date, ItemId

    ItemId, Item1.... Item6

    select ExamId, Title, Date, Item1, Item2,.... Item6

    from exame-schedule es

    join items i on es.ItemId = i.ItemId

    Monday, January 19, 2015 8:49 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User-735851359 posted

    Hello,

    To combine two tables, you need to ave a COMMON FIELD in both tables. (ItemID)

    Then you can combine and get the necessary fields from both tables using the SELECT command as wavemaster showed..

    Tuesday, January 20, 2015 3:29 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User-2060576634 posted

    I actually used this query using ExamId common field (sorry for the naming changes):

    SELECT Exams.Title,Contents.Text
FROM Exams
JOIN Contents
ON Exams.ExamId=Contents.ExamId ;

    and i got sth like this result:

       [Title]                     [Text]

    first exam              some text

    first exam              some text

    first exam              some text

    first exam              some text

    first exam              some text

    first exam              some text

    second exam         some text

    second exam         some text

    second exam         some text

    second exam         some text

    second exam         some text

    second exam         some text

    second exam         some text

    third exam             some text

    third exam             some text

    third exam             some text

    third exam             some text

    third exam             some text

    third exam             some text

    . . .

    now I need to divide the columns to create a horizontal result like this
       [Title]                           [Text]            [Text]        [Text]          . . .

    first exam                     some text     some text   some text      . . .

    second exam                some text     some text   some text      . . .

    third exam                   some text      some text   some text     . . .

    . . .

    should I use multiple joins for doing this?

    Tuesday, January 20, 2015 7:10 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User379720387 posted

    No need for multiple joins.

    The relationships is not defined by what you have.

    ExamId is the primary key for your Exams table, your Items table needs a primary key as well. We suggested ItemId.

    Then you need an ItemId column in Exams

    Tuesday, January 20, 2015 10:05 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User-735851359 posted

    Show your loops please..

    Tuesday, January 20, 2015 10:31 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User-2060576634 posted

    sorry for not being accurate.. I have already added an ItemId column to the Exams Table as a FK. I just cant understand the right query..

    Tuesday, January 20, 2015 11:01 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User-2060576634 posted

    does the loops matter? I thought I should  find the right query first then use a foreach loop to display the table

    Tuesday, January 20, 2015 11:03 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User379720387 posted

    Forget loops.

    Fix your tables and relationship first.

    What is the latest query?

    Tuesday, January 20, 2015 12:51 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User-2060576634 posted

    haven't changed anything from the last descriptions..

    Tuesday, January 20, 2015 12:53 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User-2060576634 posted
    SELECT Exams.Title,Contents.Text
FROM Exams
JOIN Contents
ON Exams.ExamId=Contents.ExamId ;
    Tuesday, January 20, 2015 2:05 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User379720387 posted

    SELECT Title, Text

    FROM Exams e

    JOIN Contents c ON e.ItemId = c.ItemId

    Tuesday, January 20, 2015 2:36 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User-2060576634 posted

    Sorry I think I made a mistake. i apologize .. I don't have an ItemId in the Exams table.. but why should I have one? every exam has several items .. can't give an itemId to an exam.. doesnt make sense.. Should I start a new thread and explain more accurate?

    Tuesday, January 20, 2015 2:48 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User-2060576634 posted

    i started a modified thread in sql forums

    Tuesday, January 20, 2015 4:34 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User1080785583 posted

    lazy loading vs eager loading vs deferred loading

    http://blogs.msdn.com/b/spike/archive/2008/09/25/deferred-vs-immediate-loading-or-lazy-vs-eager-loading-simplyfied-i-hope.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 21, 2015 11:27 AM