locked
Issues with setting up relationships RRS feed

  • Question

  • I am having a problem with relationships and the data from my order table showing up in my members table.

     

    Member Table has fields such as

    MemberID (PK)

    Fname

    Lname

    Address

    etc..

    OrderID *This is the problem--- I only getting nulls here. I've tried to do a FK relationship but nothing changes it's always null....

    I want the orderid's from the Order table to display here so that I can query users that do not have orders.

     

    Order Table has fields such as (this is working fine I can see members in the table)

    OrderID (PK)

    Date

    Product

    MemberID (I have a FK relationship to Member Table) This is a one (Member) to Many (orders) at this time. This is working fine.

     


    ---------------------------------------------- Help the community and mark posts as Answered if it helps you out ----------------------------------------------
    Saturday, July 30, 2011 1:28 PM

Answers

  • I want the orderid's from the Order table to display here so that I can query users that do not have orders.

    Hi Rollo,

    I assume you mean you want know which members hasn't placed any orders yet. Is this correct? Does the following example help you out?

     

    CREATE TABLE Membership
    (
    	member_id int NOT NULL PRIMARY KEY,
    	first_name varchar(15) NOT NULL,
    	last_name varchar(15) NOT NULL
    );
    
    CREATE TABLE Orders
    (
    	order_nbr INT NOT NULL PRIMARY KEY,
    	order_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    	upc varchar(13) NOT NULL,
    	member_id INT NOT NULL REFERENCES Membership(member_id) ON DELETE CASCADE ON UPDATE CASCADE
    );
    
    INSERT INTO Membership VALUES(1, 'Bob', 'Wiliamson');
    INSERT INTO Membership VALUES(2, 'Steve', 'Anderson');
    
    INSERT INTO Orders VALUES (1, GETDATE(), 'foo', 1); -- Bob orders foo
    -- Steve doesn't order anything
    
    SELECT m.first_name, m.last_name 
    FROM Membership m
    LEFT JOIN Orders o
    	ON m.member_id = o.member_id
    WHERE o.order_nbr IS NULL;
    
    


    • Marked as answer by Rollo1002 Tuesday, August 2, 2011 3:57 AM
    Saturday, July 30, 2011 3:12 PM

All replies

  • Hi, try to use :

    select * from Member_table M
    where Not Exists (select 1 from Order_table O where M.OrderID=O.OrderID )
    

     

     

    Best regards
    Saturday, July 30, 2011 1:37 PM
  • >> I am having a problem with relationships and the data from my order [sic” only one order?] table showing up in my members table.<<

    Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

    Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL.

    Please learn why fields are not columns, rows are not records and tables are not files. If you had been polite, would you have posted this skeleton?

    CREATE TABLE Membership
    (member_id INTEGER NOT NULL PRIMARY KEY,
     first_name VARCHAR(15) NOT NULL,
     last_name VARCHAR(15) NOT NULL,
    ..);

    CREATE TABLE Orders
    (order_nbr INTEGER NOT NULL PRIMARY KEY,
     order_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
     upc CHAR(13) NOT NULL,
     member_id  member_id INTEGER NOT NULL
       REFERENCES Membership(member_id)
     ON DELETE CASCADE
     ON UPDATE CASCADE,
     ..);

    >> order_nbr , *This is the problem--- I only getting NULLs here. I've tried to do a FK relationship but nothing changes it's always NULL.. <<

    See why we have to see your DDL? The DRI and constraints make this situation impossible. This is why I stress that columns are not fields. I is a symptom of more bad SQL in other places and ignorance of basics.

    >> I want the order_nbr's from the Orders table to display here so that I can query users that do not have orders. <<

    That makes no sense. How do I show the order_nbr of an Order that does not exist? Read what you wrote! I think you want the remembers who have no orders.

    SELECT member_id AS inactive_member_id
      FROM (SELECT member_id FROM Membership
            EXCEPT
            SELECT member_id FROM Orders)
           X (member_id       
    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Saturday, July 30, 2011 2:18 PM
  • I want the orderid's from the Order table to display here so that I can query users that do not have orders.

    Hi Rollo,

    I assume you mean you want know which members hasn't placed any orders yet. Is this correct? Does the following example help you out?

     

    CREATE TABLE Membership
    (
    	member_id int NOT NULL PRIMARY KEY,
    	first_name varchar(15) NOT NULL,
    	last_name varchar(15) NOT NULL
    );
    
    CREATE TABLE Orders
    (
    	order_nbr INT NOT NULL PRIMARY KEY,
    	order_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    	upc varchar(13) NOT NULL,
    	member_id INT NOT NULL REFERENCES Membership(member_id) ON DELETE CASCADE ON UPDATE CASCADE
    );
    
    INSERT INTO Membership VALUES(1, 'Bob', 'Wiliamson');
    INSERT INTO Membership VALUES(2, 'Steve', 'Anderson');
    
    INSERT INTO Orders VALUES (1, GETDATE(), 'foo', 1); -- Bob orders foo
    -- Steve doesn't order anything
    
    SELECT m.first_name, m.last_name 
    FROM Membership m
    LEFT JOIN Orders o
    	ON m.member_id = o.member_id
    WHERE o.order_nbr IS NULL;
    
    


    • Marked as answer by Rollo1002 Tuesday, August 2, 2011 3:57 AM
    Saturday, July 30, 2011 3:12 PM