CrazyEngineers
CrazyEngineers

  • Problem in sql query

    Manish Goyal

    Manish Goyal

    @manish-r2Hoep
    Updated: Oct 22, 2024
    Views: 1.1K
    I am facing some problem in a sql query

    here is the scenario

    I have 6 tables, where one of them is parent table and other are child table

    Primary table has 62 attributes

    Child table have 5 attribute entries

    Parent table contain unique entry for each row,but child table may different multiple entries for each parent table entry,now i want to retrieve data from all the tables through single query

    can anyone help me in this?
    0
    Replies
Howdy guest!
Dear guest, you must be logged-in to participate on CrazyEngineers. We would love to have you as a member of our community. Consider creating an account or login.
Replies

  • slashfear

    Member • Mar 7, 2011

    Hi Goyal,

    Really surprising none of them answered this question its easy buddy we have to use joins simple ok for you to understand this I have created 4 tables named demo1, demo2, demo3 and demo4. here the demo1 table has is the primary key (parent table) and the rest 3 are child which holds a reference to the parent table. following is the query to create a table for your reference:

    NOTE: I have done this in SYBASE database, the querys will be same in all the databases since it is SQL part 😉

    create table demo1(id int primary key, name varchar(20))
    create table demo2 (id int references demo1(id), prod varchar(20))
    create table demo3 (id int references demo1(id), prod3 varchar(20))
    create table demo4 (id int references demo1(id), prod4 varchar(20))



    And I populated the table as shown below:

    select * from demo1


    id name
    ----------- ----------------------------------------
    1 arvind
    2 slashfear


    select * from demo2


    id prod
    ----------- ----------------------------------------
    1 IPOD
    2 ACER LAPTOP
    1 ITOUCH


    select * from demo3

    id prod3
    ----------- ----------------------------------------
    1 BOOK
    1 PEN
    2 PENCIL


    select * from demo4

    id prod4
    ----------- ----------------------------------------
    2 something
    1 something again
    2 another prod


    Now to the join part which is shown below:


    select * from demo1 a inner join demo2 b on a.id = b.id join demo3 c on a.id = c.id join demo4 d on a.id = d.id

    id name id prod id prod3 id prod4
    ----------- ---------------------------------------- ----------- ---------------------------------------- ----------- ---------------------------------------- ----------- ----------------------------------------
    1 arvind 1 IPOD 1 BOOK 1 something again
    1 arvind 1 IPOD 1 PEN 1 something again
    1 arvind 1 ITOUCH 1 BOOK 1 something again
    1 arvind 1 ITOUCH 1 PEN 1 something again
    2 slashfear 2 ACER LAPTOP 2 PENCIL 2 something
    2 slashfear 2 ACER LAPTOP 2 PENCIL 2 another prod


    Simple right!! and sorry for the shabby output I dint have time to format the output!!

    If you have any doubts feel free to ask buddy...! 😉

    -Arvind
    Are you sure? This action cannot be undone.
    Cancel
Home Channels Search Login Register