>> * ID_ORDER NUM(9) * -- * CUSTOMER_NAME VCHR(50) * * CUSTOMER_NAME VCHR(50) * -- *----------------------------* * ID_ITEM VCHR(15) * -- * DATE_PLACED DATE * *----------------------------* -- ****************************** * QTY_ORDERED NUM(9,2) * -- * ITEM_PRICE NUM(17,6) * -- ****************************** What I'm seeking is to reach a Select that will return the following resultset: -- TABLE_FROM Table_From_Column TABLE_TO Table_TO_Columns Type ---******************************************************************************** -- Orders ID_ORDER Order_Items ID_Order NUM -- Orders CUSTOMER_NAME Order_Items CUSTOMER_NAME VCHR -- Null Null Order_Items ID_ITEM VCHR --********************************************************************************* U've searched some of the system tables (like SYSOBJECTS, SYSINDEXES, SYSREFERENCES and so on), but I realized I would not be able to learn the relationship between them and yet build my query to return the above data in only one day. I would be very pleased if someone could help me. I don't intend to use a Stored Procedure or stuff like that to perform the operation. Actually, I really intend to learn how to do it so I can refine this select within time. Thank you very much Daniel [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => Daniel [ip] => danielj@afc.com [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 1 [islastshown] => [isfirstshown] => 1 [attachments] => [allattachments] => ) -->>> * ID_ORDER NUM(9) * -- * CUSTOMER_NAME VCHR(50) * * CUSTOMER_NAME VCHR(50) * -- *----------------------------* * ID_ITEM VCHR(15) * -- * DATE_PLACED DATE * *----------------------------* -- ****************************** * QTY_ORDERED NUM(9,2) * -- * ITEM_PRICE NUM(17,6) * -- ****************************** What I'm seeking is to reach a Select that will return the following resultset: -- TABLE_FROM Table_From_Column TABLE_TO Table_TO_Columns Type ---******************************************************************************** -- Orders ID_ORDER Order_Items ID_Order NUM -- Orders CUSTOMER_NAME Order_Items CUSTOMER_NAME VCHR -- Null Null Order_Items ID_ITEM VCHR --********************************************************************************* U've searched some of the system tables (like SYSOBJECTS, SYSINDEXES, SYSREFERENCES and so on), but I realized I would not be able to learn the relationship between them and yet build my query to return the above data in only one day. I would be very pleased if someone could help me. I don't intend to use a Stored Procedure or stuff like that to perform the operation. Actually, I really intend to learn how to do it so I can refine this select within time. Thank you very much Daniel [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => <#qxQgisWDHA.1632@TK2MSFTNGP11.phx.gbl> [ref] => [htmlstate] => on_nl2br [postusername] => oj [ip] => nospam_ojngo@ho [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 2 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) -->>> * ID_ORDER NUM(9) * -- * CUSTOMER_NAME VCHR(50) * * CUSTOMER_NAME VCHR(50) * -- *----------------------------* * ID_ITEM VCHR(15) * -- * DATE_PLACED DATE * *----------------------------* -- ****************************** * QTY_ORDERED NUM(9,2) * -- * ITEM_PRICE NUM(17,6) * -- ****************************** What I'm seeking is to reach a Select that will return the following resultset: -- TABLE_FROM Table_From_Column TABLE_TO Table_TO_Columns Type ---******************************************************************************** -- Orders ID_ORDER Order_Items ID_Order NUM -- Orders CUSTOMER_NAME Order_Items CUSTOMER_NAME VCHR -- Null Null Order_Items ID_ITEM VCHR --********************************************************************************* U've searched some of the system tables (like SYSOBJECTS, SYSINDEXES, SYSREFERENCES and so on), but I realized I would not be able to learn the relationship between them and yet build my query to return the above data in only one day. I would be very pleased if someone could help me. I don't intend to use a Stored Procedure or stuff like that to perform the operation. Actually, I really intend to learn how to do it so I can refine this select within time. Thank you very much Daniel [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => <#qxQgisWDHA.1632@TK2MSFTNGP11.phx.gbl> [htmlstate] => on_nl2br [postusername] => Daniel [ip] => danielj@afc.com [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 3 [islastshown] => 1 [isfirstshown] => [attachments] => [allattachments] => ) --> Have all the relationships of a Two tables in a resultset - Microsoft SQL / MS SQL Server

Have all the relationships of a Two tables in a resultset - Microsoft SQL / MS SQL Server

Hi there, Well, what I want should be Quite simple, but I'm having quite of a Hard Time to complete this task. Let's Say I have the following tables.. ID_Order and Customer_Name are Primary Keys in Both Tables, but they're also FK in the Order_Items table -- ****************************** ****************************** -- * Orders * * Order_Items * -- ****************************** ****************************** -- * ID_ORDER NUM(9) * <--------->>> * ID_ORDER NUM(9) * -- * CUSTOMER_NAME VCHR(50) * * CUSTOMER_NAME VCHR(50) * -- *----------------------------* * ID_ITEM VCHR(15) * -- * DATE_PLACED DATE * *----------------------------* -- ****************************** * QTY_ORDERED NUM(9,2) * -- * ITEM_PRICE NUM(17,6) ...

  1. #1

    Default Have all the relationships of a Two tables in a resultset

    Hi there,

    Well, what I want should be Quite simple, but I'm having quite of a Hard Time to complete this task.
    Let's Say I have the following tables..
    ID_Order and Customer_Name are Primary Keys in Both Tables, but they're also FK in the Order_Items table

    -- ****************************** ******************************
    -- * Orders * * Order_Items *
    -- ****************************** ******************************
    -- * ID_ORDER NUM(9) * <--------->>> * ID_ORDER NUM(9) *
    -- * CUSTOMER_NAME VCHR(50) * * CUSTOMER_NAME VCHR(50) *
    -- *----------------------------* * ID_ITEM VCHR(15) *
    -- * DATE_PLACED DATE * *----------------------------*
    -- ****************************** * QTY_ORDERED NUM(9,2) *
    -- * ITEM_PRICE NUM(17,6) *
    -- ******************************

    What I'm seeking is to reach a Select that will return the following resultset:

    -- TABLE_FROM Table_From_Column TABLE_TO Table_TO_Columns Type
    ---************************************************** ******************************
    -- Orders ID_ORDER Order_Items ID_Order NUM
    -- Orders CUSTOMER_NAME Order_Items CUSTOMER_NAME VCHR
    -- Null Null Order_Items ID_ITEM VCHR
    --************************************************** *******************************

    U've searched some of the system tables (like SYSOBJECTS, SYSINDEXES, SYSREFERENCES and so on), but I realized I would not be able to learn the relationship between them and yet build my query to return the above data in only one day.
    I would be very pleased if someone could help me. I don't intend to use a Stored Procedure or stuff like that to perform the operation. Actually, I really intend to learn how to do it so I can refine this select within time.

    Thank you very much

    Daniel
    Daniel Guest

  2. #2

    Default Re: Have all the relationships of a Two tables in a resultset

    create procedure usp_findreferences
    /************************************************** *************************
    */
    /* Purpose: A quick & dirty way to find ref. objects for a[ll] table[s] */
    /* Author: OJ Ngo */
    /* Date: 02/28/2002 */
    /************************************************** *************************
    */
    tbname sysname=null
    as
    set nocount on

    Print 'Referenced:'
    select c1.table_name,
    c1.column_name,
    fkey=r.constraint_name,
    referenced_parent_table=c2.table_name,
    c2.column_name
    from information_schema.constraint_column_usage c1 join
    information_schema.referential_constraints r on
    c1.constraint_name=r.constraint_name
    join information_schema.constraint_column_usage c2 on
    r.unique_constraint_name=c2.constraint_name
    where c1.table_name=coalesce(tbname,c1.table_name)
    order by case when tbname is null then c1.table_name else c2.table_name end

    print ''
    print 'Referencing:'
    select c1.table_name,
    c1.column_name,
    fkey=r.constraint_name,
    referencing_child_table=c2.table_name,
    c2.column_name
    from information_schema.constraint_column_usage c1 join
    information_schema.referential_constraints r on
    c1.constraint_name=r.unique_constraint_name
    join information_schema.constraint_column_usage c2 on
    r.constraint_name=c2.constraint_name
    where c1.table_name=coalesce(tbname,c1.table_name)
    order by case when tbname is null then c1.table_name else c2.table_name end


    GO


    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Daniel Jorge" <com.br> wrote in message news:phx.gbl...
    Hi there,

    Well, what I want should be Quite simple, but I'm having quite of a Hard Time to complete this task.
    Let's Say I have the following tables..
    ID_Order and Customer_Name are Primary Keys in Both Tables, but they're also FK in the Order_Items table

    -- ****************************** ******************************
    -- * Orders * * Order_Items *
    -- ****************************** ******************************
    -- * ID_ORDER NUM(9) * <--------->>> * ID_ORDER NUM(9) *
    -- * CUSTOMER_NAME VCHR(50) * * CUSTOMER_NAME VCHR(50) *
    -- *----------------------------* * ID_ITEM VCHR(15) *
    -- * DATE_PLACED DATE * *----------------------------*
    -- ****************************** * QTY_ORDERED NUM(9,2) *
    -- * ITEM_PRICE NUM(17,6) *
    -- ******************************

    What I'm seeking is to reach a Select that will return the following resultset:

    -- TABLE_FROM Table_From_Column TABLE_TO Table_TO_Columns Type
    ---************************************************** ******************************
    -- Orders ID_ORDER Order_Items ID_Order NUM
    -- Orders CUSTOMER_NAME Order_Items CUSTOMER_NAME VCHR
    -- Null Null Order_Items ID_ITEM VCHR
    --************************************************** *******************************

    U've searched some of the system tables (like SYSOBJECTS, SYSINDEXES, SYSREFERENCES and so on), but I realized I would not be able to learn the relationship between them and yet build my query to return the above data in only one day.
    I would be very pleased if someone could help me. I don't intend to use a Stored Procedure or stuff like that to perform the operation. Actually, I really intend to learn how to do it so I can refine this select within time.

    Thank you very much

    Daniel

    oj Guest

  3. #3

    Default Re: Have all the relationships of a Two tables in a resultset

    Hi OJ

    Man.. I don't care how dirty this can be, it totally fullfill my requirements (with the additional advantage of the second select)...

    Owe you a big one

    Thank you very much

    Daniel
    "oj" <com> escreveu na mensagem news:%phx.gbl...
    create procedure usp_findreferences
    /************************************************** *************************
    */
    /* Purpose: A quick & dirty way to find ref. objects for a[ll] table[s] */
    /* Author: OJ Ngo */
    /* Date: 02/28/2002 */
    /************************************************** *************************
    */
    tbname sysname=null
    as
    set nocount on

    Print 'Referenced:'
    select c1.table_name,
    c1.column_name,
    fkey=r.constraint_name,
    referenced_parent_table=c2.table_name,
    c2.column_name
    from information_schema.constraint_column_usage c1 join
    information_schema.referential_constraints r on
    c1.constraint_name=r.constraint_name
    join information_schema.constraint_column_usage c2 on
    r.unique_constraint_name=c2.constraint_name
    where c1.table_name=coalesce(tbname,c1.table_name)
    order by case when tbname is null then c1.table_name else c2.table_name end

    print ''
    print 'Referencing:'
    select c1.table_name,
    c1.column_name,
    fkey=r.constraint_name,
    referencing_child_table=c2.table_name,
    c2.column_name
    from information_schema.constraint_column_usage c1 join
    information_schema.referential_constraints r on
    c1.constraint_name=r.unique_constraint_name
    join information_schema.constraint_column_usage c2 on
    r.constraint_name=c2.constraint_name
    where c1.table_name=coalesce(tbname,c1.table_name)
    order by case when tbname is null then c1.table_name else c2.table_name end


    GO


    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Daniel Jorge" <com.br> wrote in message news:phx.gbl...
    Hi there,

    Well, what I want should be Quite simple, but I'm having quite of a Hard Time to complete this task.
    Let's Say I have the following tables..
    ID_Order and Customer_Name are Primary Keys in Both Tables, but they're also FK in the Order_Items table

    -- ****************************** ******************************
    -- * Orders * * Order_Items *
    -- ****************************** ******************************
    -- * ID_ORDER NUM(9) * <--------->>> * ID_ORDER NUM(9) *
    -- * CUSTOMER_NAME VCHR(50) * * CUSTOMER_NAME VCHR(50) *
    -- *----------------------------* * ID_ITEM VCHR(15) *
    -- * DATE_PLACED DATE * *----------------------------*
    -- ****************************** * QTY_ORDERED NUM(9,2) *
    -- * ITEM_PRICE NUM(17,6) *
    -- ******************************

    What I'm seeking is to reach a Select that will return the following resultset:

    -- TABLE_FROM Table_From_Column TABLE_TO Table_TO_Columns Type
    ---************************************************** ******************************
    -- Orders ID_ORDER Order_Items ID_Order NUM
    -- Orders CUSTOMER_NAME Order_Items CUSTOMER_NAME VCHR
    -- Null Null Order_Items ID_ITEM VCHR
    --************************************************** *******************************

    U've searched some of the system tables (like SYSOBJECTS, SYSINDEXES, SYSREFERENCES and so on), but I realized I would not be able to learn the relationship between them and yet build my query to return the above data in only one day.
    I would be very pleased if someone could help me. I don't intend to use a Stored Procedure or stuff like that to perform the operation. Actually, I really intend to learn how to do it so I can refine this select within time.

    Thank you very much

    Daniel
    Daniel Guest

Similar Threads

  1. MS SQL Relationships
    By ceaseanddesist in forum Dreamweaver AppDev
    Replies: 4
    Last Post: May 10th, 04:33 PM
  2. Help with relationships.
    By tester in forum FileMaker
    Replies: 6
    Last Post: January 8th, 12:44 PM
  3. many-to-many relationships
    By df in forum FileMaker
    Replies: 2
    Last Post: December 3rd, 03:01 PM
  4. Relationships in FMP5.5
    By Ben Bradley in forum FileMaker
    Replies: 0
    Last Post: October 2nd, 02:23 PM

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
  •