May 15, 2018 at 12:56 pm
Hello Everyone,
I have a slight problem so I hope you can help.
I have a table with orders joined to a table with roles. An order can have multiple roles
| Order Number
| RoleType
|
| 123
| 20
|
| 123
| 21
|
| 123
| 22
|
| 123
| 23
|
| 124
| 20
|
| 124
| 21
|
| 124
| 23
|
As you can see, order # 124 does not have a role type 22 but order # 123 does. is there any way in sql to bring back order 124 because it doesn't have RoleType 22? I'd like to write a query to bring back any orders that don't have a RoleType of 22.
Thanks for your help in advance!
May 15, 2018 at 1:02 pm
Something like this?select o.OrderNumber from Orders o
where not exists (select 1 from Roles r where r.OrderNumber = o.OrderNumber and o.RoleType = 22)
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 15, 2018 at 1:09 pm
Phil Parkin - Tuesday, May 15, 2018 1:02 PMSomething like this?select o.OrderNumber from Orders o
where not exists (select 1 from Roles r where r.OrderNumber = o.OrderNumber and o.RoleType = 22)
OMG, Thank you so much! Worked like a charm!!!!!
May 15, 2018 at 1:18 pm
meichmann - Tuesday, May 15, 2018 1:09 PMPhil Parkin - Tuesday, May 15, 2018 1:02 PMSomething like this?select o.OrderNumber from Orders o
where not exists (select 1 from Roles r where r.OrderNumber = o.OrderNumber and o.RoleType = 22)OMG, Thank you so much! Worked like a charm!!!!!
My pleasure, thanks for posting back.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 16, 2018 at 2:05 pm
Where is the DDL for all of this? Why do you want us to design your schema for you? Why did you post a picture instead of actual SQL code? If you're working in a shop where they treat you like this, it's time to quit; their contempt for you is simply not worth it.
CREATE TABLE Orders
(order_nbr CHAR(3) NOT NULL,
role_type CHAR(2) NOT NULL
CHECK (role_type IN ('20,', '21', '22', '23'),
PRIMARY KEY (order_nbr, role_type));
INSERT INTO Orders
VALUES
('123', '20'),
('123', ' 21'),
('123', '22'),
('123', '23'),
('124', '20'),
('124', '21'),
('124', '23');
>> I'd like to write a query to bring back any orders that don't have a role_type of '22'. <<
SELECT O1.order_nbr
FROM Orders AS O1
WHERE
NOT EXISTS (SELECT *
FROM Orders AS O2
WHERE O1.order_nbr = O2.order_nbr
AND O2.role_type = '22');
>> I'd like to write a query to bring back any orders that don't have a role_type of '22'. <<
SELECT O1.order_nbr FROM Orders AS O1 WHERE NOT EXISTS (SELECT * FROM Orders AS O2 WHERE O1.order_nbr = O2.order_nbr AND O2.role_type = '22');
>> I have a slight problem so I hope you can help.
I have a table with orders joined to a table with roles. An order can have multiple roles <<
Where is the DDL for all of this? Why do you want us to design your schema for you? Why did you post a picture instead of actual SQL code? If you're working in a shop where they treat you like this, it's time to quit; their contempt for you is simply not worth it.CREATE TABLE Orders
(order_nbr CHAR(3) NOT NULL,
role_type CHAR(2) NOT NULL
CHECK (role_type IN ('20,', '21', '22', '23'),
PRIMARY KEY (order_nbr, role_type));
INSERT INTO Orders
VALUES
('123', '20'),
('123', ' 21'),
('123', '22'),
('123', '23'),
('124', '20'),
('124', '21'),
('124', '23');
/* I'd like to write a query to bring back any orders that don't have a role_type of '22'. */
SELECT O1.order_nbr
FROM Orders AS O1
WHERE
NOT EXISTS (SELECT *
FROM Orders AS O2
WHERE O1.order_nbr = O2.order_nbr
AND O2.role_type = '22');
Please post DDL and follow ANSI/ISO standards when asking for help.
May 16, 2018 at 5:14 pm
meichmann - Tuesday, May 15, 2018 12:56 PM>> I have a slight problem so I hope you can help.
I have a table with orders joined to a table with roles. An order can have multiple roles <<Where is the DDL for all of this? Why do you want us to design your schema for you? Why did you post a picture instead of actual SQL code? If you're working in a shop where they treat you like this, it's time to quit; their contempt for you is simply not worth it.
CREATE TABLE Orders
(order_nbr CHAR(3) NOT NULL,
role_type CHAR(2) NOT NULL
CHECK (role_type IN ('20,', '21', '22', '23'),
PRIMARY KEY (order_nbr, role_type));INSERT INTO Orders
VALUES
('123', '20'),
('123', ' 21'),
('123', '22'),
('123', '23'),('124', '20'),
('124', '21'),
('124', '23');>> I'd like to write a query to bring back any orders that don't have a role_type of '22'. <<
SELECT O1.order_nbr
FROM Orders AS O1
WHERE
NOT EXISTS (SELECT *
FROM Orders AS O2
WHERE O1.order_nbr = O2.order_nbr
AND O2.role_type = '22');>> I'd like to write a query to bring back any orders that don't have a role_type of '22'. <<
SELECT O1.order_nbr FROM Orders AS O1 WHERE NOT EXISTS (SELECT * FROM Orders AS O2 WHERE O1.order_nbr = O2.order_nbr AND O2.role_type = '22');>> I have a slight problem so I hope you can help.
I have a table with orders joined to a table with roles. An order can have multiple roles <<Where is the DDL for all of this? Why do you want us to design your schema for you? Why did you post a picture instead of actual SQL code? If you're working in a shop where they treat you like this, it's time to quit; their contempt for you is simply not worth it.
CREATE TABLE Orders
(order_nbr CHAR(3) NOT NULL,
role_type CHAR(2) NOT NULL
CHECK (role_type IN ('20,', '21', '22', '23'),
PRIMARY KEY (order_nbr, role_type));
INSERT INTO Orders
VALUES
('123', '20'),
('123', ' 21'),
('123', '22'),
('123', '23'),
('124', '20'),
('124', '21'),
('124', '23');/* I'd like to write a query to bring back any orders that don't have a role_type of '22'. */
SELECT O1.order_nbr
FROM Orders AS O1
WHERE
NOT EXISTS (SELECT *
FROM Orders AS O2
WHERE O1.order_nbr = O2.order_nbr
AND O2.role_type = '22');
First off Joe, the schema has already been built. second, its not an image, its a table copied from word. thirdly, the answer was already given so why do you feel the need to post after the fact?
I don't like your elitist attitude. you belittle everyone you reply to, so do me a favor and stay away from my posts.
May 17, 2018 at 4:54 pm
>> First off Joe, the schema has already been built. <<
It was already built, then why didn't you post the DDL for it? We can't read your mind. This also violates basic netiquette on SQL forms for the last 30+ years.
>> Second, its not an image, its a table copied from word. <<
A word table is an image that is just as useless as any screens snapshot. We have to copy the data into the DDL that you fail to post by hand.
>>Thirdly, the answer was already given so why do you feel the need to post after the fact? <<
Phil assumed, since he also had no DDL or other information, that you can incorrectly modeled the roles as an entity. Instead of an attribute. If you think it's an entity, then you put it in a separate table, and then have to join it back to the entity that has that attribute. This is a very common beginners mistake.
Just ask yourself, "Can a role exist without an order?" And then "can a role be a property or attribute of more than one kind of entity?" If the attribute remodeling was color, then many different kinds of entities and have color. We might want to put our colors in the table and decide which color system we want to use across all of our entities (Pantone, Land, RBG, etc.)
In your case, the role attribute seems to belong only to orders. I'm also going to guess that the range of possible encodings is static and limited. Therefore, we use a CHECK() constraint. If this attribute were shared, dynamic or relatively large in its range, then we put it in a separate table and use a REFERENCES clause to assure that you have data integrity in your schema.
>> I don't like your elitist attitude. you belittle everyone you reply to, so do me a favor and stay away from my posts. <<
No, just the ones who failed to follow basic netiquette. I don't like the attitude of privileged snowflakes who think that everybody should do their work for them. All you have to do is read the rules at the front of each forum as to what's required for posting.
Please post DDL and follow ANSI/ISO standards when asking for help.
May 17, 2018 at 5:00 pm
jcelko212 32090 - Thursday, May 17, 2018 4:53 PM>> First off Joe, the schema has already been built. <<It was already built, then why didn't you post the DDL for it? We can't read your mind. This also violates basic netiquette on SQL forms for the last 30+ years.
>> Second, its not an image, its a table copied from word. <<
A word table is an image that is just as useless as any screens snapshot. We have to copy the data into the DDL that you fail to post by hand.
>>Thirdly, the answer was already given so why do you feel the need to post after the fact? <<
Phil assumed, since he also had no DDL or other information, that you can incorrectly modeled the roles as an entity. Instead of an attribute. If you think it's an entity, then you put it in a separate table, and then have to join it back to the entity that has that attribute. This is a very common beginners mistake.
Just ask yourself, "Can a role exist without an order?" And then "can a role be a property or attribute of more than one kind of entity?" If the attribute remodeling was color, then many different kinds of entities and have color. We might want to put our colors in the table and decide which color system we want to use across all of our entities (Pantone, Land, RBG, etc.)
In your case, the role attribute seems to belong only to orders. I'm also going to guess that the range of possible encodings is static and limited. Therefore, we use a CHECK() constraint. If this attribute were shared, dynamic or relatively large in its range, then we put it in a separate table and use a REFERENCES clause to assure that you have data integrity in your schema.
>> I don't like your elitist attitude. you belittle everyone you reply to, so do me a favor and stay away from my posts. <<
No, just the ones who failed to follow basic netiquette. I don't like the attitude of privileged snowflakes who think that everybody should do their work for them. All you have to do is read the rules at the front of each forum as to what's required for posting.
Actually, Mr. Celko, he is correct. That is exactly what you do in 99.9% (Dove pure) of your posts.
May 17, 2018 at 5:04 pm
jcelko212 32090 - Thursday, May 17, 2018 4:53 PM>> First off Joe, the schema has already been built. <<It was already built, then why didn't you post the DDL for it? We can't read your mind. This also violates basic netiquette on SQL forms for the last 30+ years.
>> Second, its not an image, its a table copied from word. <<
A word table is an image that is just as useless as any screens snapshot. We have to copy the data into the DDL that you fail to post by hand.
>>Thirdly, the answer was already given so why do you feel the need to post after the fact? <<
Phil assumed, since he also had no DDL or other information, that you can incorrectly modeled the roles as an entity. Instead of an attribute. If you think it's an entity, then you put it in a separate table, and then have to join it back to the entity that has that attribute. This is a very common beginners mistake.
Just ask yourself, "Can a role exist without an order?" And then "can a role be a property or attribute of more than one kind of entity?" If the attribute remodeling was color, then many different kinds of entities and have color. We might want to put our colors in the table and decide which color system we want to use across all of our entities (Pantone, Land, RBG, etc.)
In your case, the role attribute seems to belong only to orders. I'm also going to guess that the range of possible encodings is static and limited. Therefore, we use a CHECK() constraint. If this attribute were shared, dynamic or relatively large in its range, then we put it in a separate table and use a REFERENCES clause to assure that you have data integrity in your schema.
>> I don't like your elitist attitude. you belittle everyone you reply to, so do me a favor and stay away from my posts. <<
No, just the ones who failed to follow basic netiquette. I don't like the attitude of privileged snowflakes who think that everybody should do their work for them. All you have to do is read the rules at the front of each forum as to what's required for posting.
>>It was already built, then why didn't you post the DDL for it? We can't read your mind. This also violates basic netiquette on SQL forms for the last 30+ years.
Some people don't need the DDL as proven by the solution and it violates nothing. You're not limited to a set of rules. people post information for help. If people can't figure out the solution, they move on, not drabble about rules and fill posts with garbage like you do.
>>A word table is an image that is just as useless as any screens snapshot. We have to copy the data into the DDL that you fail to post by hand.
A table and an image are two different things. Maybe we don't have the DDL to post, so a table is the best way to display the issue. If you can't help with the issue, move on.
>>Phil assumed, since he also had no DDL or other information, that you can incorrectly modeled the roles as an entity. Instead of an attribute. If you think it's an entity, then you put it in a separate table, and then have to join it back to the entity that has that attribute. This is a very common beginners mistake
Well, apparently he had no issue providing a solution, which I thanked him for.
>>No, just the ones who failed to follow basic netiquette. I don't like the attitude of privileged snowflakes who think that everybody should do their work for them. All you have to do is read the rules at the front of each forum as to what's required for posting
I'm not asking for people to do my work for me. Just help with a issue. And if you have a problem with people asking for help, then leave the forum and do us all a favor. You are a belligerent, abrasive person who feels everyone is below you that deviates from a set of rules that are not set in stone, even if displayed. People have their own way of displaying data for an issue in hopes that someone can help them. That's why we come to this forum, and it's people like you that turns people away. So please, keep your "etiquette" to yourself. If you don't or can't provide a solution or at least help, then shut up.
May 18, 2018 at 7:17 am
meichmann - Thursday, May 17, 2018 5:04 PMjcelko212 32090 - Thursday, May 17, 2018 4:53 PM>> First off Joe, the schema has already been built. <<It was already built, then why didn't you post the DDL for it? We can't read your mind. This also violates basic netiquette on SQL forms for the last 30+ years.
>> Second, its not an image, its a table copied from word. <<
A word table is an image that is just as useless as any screens snapshot. We have to copy the data into the DDL that you fail to post by hand.
>>Thirdly, the answer was already given so why do you feel the need to post after the fact? <<
Phil assumed, since he also had no DDL or other information, that you can incorrectly modeled the roles as an entity. Instead of an attribute. If you think it's an entity, then you put it in a separate table, and then have to join it back to the entity that has that attribute. This is a very common beginners mistake.
Just ask yourself, "Can a role exist without an order?" And then "can a role be a property or attribute of more than one kind of entity?" If the attribute remodeling was color, then many different kinds of entities and have color. We might want to put our colors in the table and decide which color system we want to use across all of our entities (Pantone, Land, RBG, etc.)
In your case, the role attribute seems to belong only to orders. I'm also going to guess that the range of possible encodings is static and limited. Therefore, we use a CHECK() constraint. If this attribute were shared, dynamic or relatively large in its range, then we put it in a separate table and use a REFERENCES clause to assure that you have data integrity in your schema.
>> I don't like your elitist attitude. you belittle everyone you reply to, so do me a favor and stay away from my posts. <<
No, just the ones who failed to follow basic netiquette. I don't like the attitude of privileged snowflakes who think that everybody should do their work for them. All you have to do is read the rules at the front of each forum as to what's required for posting.
>>It was already built, then why didn't you post the DDL for it? We can't read your mind. This also violates basic netiquette on SQL forms for the last 30+ years.
Some people don't need the DDL as proven by the solution and it violates nothing. You're not limited to a set of rules. people post information for help. If people can't figure out the solution, they move on, not drabble about rules and fill posts with garbage like you do.>>A word table is an image that is just as useless as any screens snapshot. We have to copy the data into the DDL that you fail to post by hand.
A table and an image are two different things. Maybe we don't have the DDL to post, so a table is the best way to display the issue. If you can't help with the issue, move on.>>Phil assumed, since he also had no DDL or other information, that you can incorrectly modeled the roles as an entity. Instead of an attribute. If you think it's an entity, then you put it in a separate table, and then have to join it back to the entity that has that attribute. This is a very common beginners mistake
Well, apparently he had no issue providing a solution, which I thanked him for.
>>No, just the ones who failed to follow basic netiquette. I don't like the attitude of privileged snowflakes who think that everybody should do their work for them. All you have to do is read the rules at the front of each forum as to what's required for posting
I'm not asking for people to do my work for me. Just help with a issue. And if you have a problem with people asking for help, then leave the forum and do us all a favor. You are a belligerent, abrasive person who feels everyone is below you that deviates from a set of rules that are not set in stone, even if displayed. People have their own way of displaying data for an issue in hopes that someone can help them. That's why we come to this forum, and it's people like you that turns people away. So please, keep your "etiquette" to yourself. If you don't or can't provide a solution or at least help, then shut up.
I will pass on this little bit of advice. Sometimes we can figure out what an OP, such as you, are looking for based on the question and questionable info (images, word description of a table, no sample data). The other side of the coin to this is that if you take the time to provide the DDL (CREATE TABLE Statement) for the table(s) involved, sample data (INSERT statements) for the table(s) involved, and the expected results, especially if provided in a SQL table (CREATE TABLE and INSERT statements), actually helps those of us that are more visual in our problem solving. We have also seen some people actually solve their own problem by going through this exercise. What is even better, when that happens, is when those same people still post their question with everything including their solution so that others can learn from their problem.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply