0

So i have those two tables:

DROP TABLE Ticket CASCADE CONSTRAINTS PURGE;
DROP TABLE Store CASCADE CONSTRAINTS PURGE;
DROP TABLE Employee CASCADE CONSTRAINTS PURGE;

CREATE TABLE Employee
(
    emp_ID               VARCHAR(30) NOT NULL ,
    emp_fname            VARCHAR(20) ,
  emp_lname            VARCHAR(20) ,
    emp_hiredt           DATE,
    emp_cell             CHAR(10) ,
    emp_homenum          CHAR(10) ,
    emp_title            VARCHAR(20),
  emp_street           VARCHAR(20) ,
    emp_city             VARCHAR(20) ,
    emp_state            CHAR(2)  CHECK (emp_state IN ('AZ', 'AK', 'AS', 'AL', 'NE')),
    emp_zip              CHAR(5)  CHECK (emp_zip > 0),
 PRIMARY KEY (emp_ID)
);

CREATE TABLE Store
(
    store_ID             VARCHAR(30) NOT NULL ,
    store_name           VARCHAR(20) NULL ,
    store_street         VARCHAR(20) NULL ,
    store_city           VARCHAR(20) NULL ,
    store_state          CHAR(2) NULL  CHECK (store_state IN ('AZ', 'AK', 'AS', 'AL', 'NE')),
    store_zip            CHAR(5) NULL  CHECK (store_zip > 0),
 PRIMARY KEY (store_ID)
);

CREATE TABLE Ticket
(
    Ticket_Num           VARCHAR(30) NOT NULL ,
    date_recived         DATE NULL ,
    date_ready           DATE NULL ,
    total                FLOAT NULL ,
  ServiceType          VARCHAR(20) NULL ,
    emp_ID               VARCHAR(30) NOT NULL ,
    store_ID             VARCHAR(30) NOT NULL ,
  PRIMARY KEY (Ticket_Num),
  FOREIGN KEY (emp_ID) REFERENCES Employee (emp_ID) ,
  FOREIGN KEY (store_ID) REFERENCES Store (store_ID)
);

and this data:

DELETE FROM Ticket; 
DELETE FROM Store;
DELETE FROM Employee;

-- INSERTING Employee TABLE
INSERT INTO Employee (emp_ID, emp_fname, emp_lname, emp_hiredt,
    emp_cell, emp_homenum, emp_title, emp_street, emp_city, emp_state, emp_zip)
VALUES (78395, 'Mark', 'King', '17-Sep-81', 4022248679, 4026678539, 'President', 'Dodge St', 'Omaha', 'NE', 68114);
INSERT INTO Employee (emp_ID, emp_fname, emp_lname, emp_hiredt,
    emp_cell, emp_homenum, emp_title, emp_street, emp_city, emp_state, emp_zip)
VALUES (76988, 'Jefferson', 'Blake', '01-MAY-91', 4024848679, 4029878539, 'Manager', 'Pacific St', 'Omaha', 'AK', 68784);
INSERT INTO Employee (emp_ID, emp_fname, emp_lname, emp_hiredt,
    emp_cell, emp_homenum, emp_title, emp_street, emp_city, emp_state, emp_zip)
VALUES (77821, 'James', 'Clark', '14-Sep-94', 8702248679, 8706678539, 'Front Desk', 'Pacific St', 'Omaha', 'AK', 68784);
INSERT INTO Employee (emp_ID, emp_fname, emp_lname, emp_hiredt,
    emp_cell, emp_homenum, emp_title, emp_street, emp_city, emp_state, emp_zip)
VALUES (75667, 'Mary', 'Jones', '04-Apr-20', 6067486790, 6858078539, 'Delivery', 'Pacific St', 'Omaha', 'AK', 68784);
INSERT INTO Employee (emp_ID, emp_fname, emp_lname, emp_hiredt,
    emp_cell, emp_homenum, emp_title, emp_street, emp_city, emp_state, emp_zip)
VALUES (73690, 'John', 'Smith', '15-Dec-14', 7809586324, 7802369852, 'Accountant', 'Dodge St', 'Omaha', 'NE', 68114);
INSERT INTO Employee (emp_ID, emp_fname, emp_lname, emp_hiredt,
    emp_cell, emp_homenum, emp_title, emp_street, emp_city, emp_state, emp_zip)
VALUES (85367, 'John', 'Doe', '20-Mar-91', 5872347901, 5875747901, 'Delivery', 'Pacific St', 'Omaha', 'AS', 68784);
INSERT INTO Employee (emp_ID, emp_fname, emp_lname, emp_hiredt,
    emp_cell, emp_homenum, emp_title, emp_street, emp_city, emp_state, emp_zip)
VALUES (24057, 'Viola', 'Mims', '03-Dec-04', 3172391399, 3172390599, 'Front Desk', 'Dodge St', 'Omaha', 'NE', 68784);
INSERT INTO Employee (emp_ID, emp_fname, emp_lname, emp_hiredt,
    emp_cell, emp_homenum, emp_title, emp_street, emp_city, emp_state, emp_zip)
VALUES (63407, 'Frank', 'Bixler', '04-Oct-07', 8452529572, 8452525772, 'Manager', 'Dodge St', 'Omaha', 'NE', 68784);
commit;

-- INSERTING Store TABLE
INSERT INTO Store (store_ID, store_name, store_street, store_city, store_state, store_zip)
VALUES (44444, 'Mighty Clean', 'Lost Ave', 'Never Land', 'AL', 35006);
INSERT INTO Store (store_ID, store_name, store_street, store_city, store_state, store_zip)
VALUES (12345,  'Uniform Land', 'Pancake circle',   'Springfield', 'AZ', 35008);
INSERT INTO Store (store_ID, store_name, store_street, store_city, store_state, store_zip)
VALUES (68745,  'Chefs' ,'Nowhere Ave'  , 'Never Land', 'AS', 35006);
INSERT INTO Store (store_ID, store_name, store_street, store_city, store_state, store_zip)
VALUES (52146   ,'School',  'School St', 'Never Land', 'NE', 68004);
commit;

-- INSERTING Ticket TABLE
INSERT INTO Ticket (Ticket_Num, date_recived, date_ready, total, ServiceType, emp_ID, store_ID)
VALUES (11111, '28-Nov-14', '01-Dec-14', 8.50, 'Pants', 77821, 52146);
INSERT INTO Ticket (Ticket_Num, date_recived, date_ready, total, ServiceType, emp_ID, store_ID)
VALUES (22222, '20-Nov-14', '07-Dec-14', 7, 'Shirts', 77821, 68745);
INSERT INTO Ticket (Ticket_Num, date_recived, date_ready, total, ServiceType, emp_ID, store_ID)
VALUES (33333, '01-Dec-14', '10-Dec-14', 10.7, 'Sweater', 75667, 52146);
INSERT INTO Ticket (Ticket_Num, date_recived, date_ready, total, ServiceType, emp_ID, store_ID)
VALUES (44444, '27-Nov-14', '02-Dec-14', 12, 'Jeans', 75667, 52146);
INSERT INTO Ticket (Ticket_Num, date_recived, date_ready, total, ServiceType, emp_ID, store_ID)
VALUES (55555, '30-Nov-14', '01-Dec-14', 8.50, 'Jacket', 77821, 44444);
INSERT INTO Ticket (Ticket_Num, date_recived, date_ready, total, ServiceType, emp_ID, store_ID)
VALUES (66666, '30-Nov-14', '05-Dec-14', 14.50, 'Shirts', 77821, 52146);
INSERT INTO Ticket (Ticket_Num, date_recived, date_ready, total, ServiceType, emp_ID, store_ID)
VALUES (77777, '30-Nov-14', '05-Dec-14', 8.50, 'Jacket', 77821, 52146);
INSERT INTO Ticket (Ticket_Num, date_recived, date_ready, total, ServiceType, emp_ID, store_ID)
VALUES (88888, '30-Nov-14', '05-Dec-14', 8.50, 'Jacket', 77821, 52146);
commit;

and I have this query:

select store.store_name, store.store_ID, ticket.Ticket_Num, Ticket.date_recived, 
ticket.date_ready
from store left join ticket on store.store_ID = ticket.store_ID where store.store_ID = 44444;

and I get this result:

   STORE_NAME           STORE_ID                       TICKET_NUM                     DATE_RECIVED DATE_READY
-------------------- ------------------------------ ------------------------------ ------------ ----------
Mighty Clean         44444                                                                                 

I should be getting data for Ticket number and other ticket related information from my query, but it's not working. and ideas?

closed as off-topic by Michael - sqlbot, Paul White, ypercubeᵀᴹ, RLF, RolandoMySQLDBA Dec 15 '14 at 16:01

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Michael - sqlbot, Paul White, ypercubeᵀᴹ, RLF, RolandoMySQLDBA
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Check the data you are inserting into ticket. It appears you have swapped store_id and ticket_id values – MickyT Dec 14 '14 at 17:41
  • um... insert statement looks alright to me. I know I'm using in one of the rows Ticket_Num = 44444 and I'm also using store_ID = 44444 but 44444 in the Ticket table is different than 44444 in the Store table. – Amture101 Dec 14 '14 at 17:47
  • Apologies, I missed that. Your query looks OK to me. Which Database are you actually using? You have 3 tagged. – MickyT Dec 14 '14 at 18:20
  • Here's a SQLFiddle with your data (changed dates to varchar because it didn't like your format) and query. Seems to work, so double check your data. (sqlfiddle.com/#!2/e2a43/1/0) – MickyT Dec 14 '14 at 18:25
  • @MickyT Beat me to it by a minute, but I'll post it here just to show that you can also just input the date in a correct format for correct results (the solution is not changing the date to varchar) – Reaces Dec 14 '14 at 18:29
1

Well. it seems it was Oracle SQL Developer fault because when I deleted it and downloaded the newer edition it worked.

Not the answer you're looking for? Browse other questions tagged or ask your own question.