Query to retried list of names with value and if a value does not exist!

Question

Query to retried list of names with value and if a value does not exist!

jbeclapez

SSC-Addicted

Points: 405
More actions
January 8, 2021 at 12:12 pm

#3829948
Hello,
I have 2 tables : Names and NameProperty.
In the table NameProperty The property Food as by default a value of YES. The problem is that the default value YES is not showing as line in the table. If a user put it to NO and then saves it back to YES, only then it will show YES.
Here is an example i created (See below)
And I would like my query to bring back all names with Food - so Burger, Pizza and Burger2... You probably noted in the code that Pizza is a Food but has not been set up as Food in the property table as by default all Names are Food. So the query should retrieve that :
NAME
Burger
Pizza
Burger2
Thanks for your help
```
CREATE TABLE Names
([productName] varchar(13), [description] varchar(57))
;

INSERT INTO Names
([productName], [description])
VALUES
('Burger', 'This is name 1'),
('Pizza', 'This is name 2'),
('CocaCola', 'This is name 3'),
('Burger2', 'This is name 4')
;


CREATE TABLE NameProperty
([productName] varchar(13), [Property] varchar(57), [Value] varchar(57))
;

INSERT INTO NameProperty
([productName], [Property],[Value])
VALUES
('Burger', 'Food','Yes'),
('CocaCola', 'Beverage','Yes'),
('Burger2', 'Food','No')

;
```

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

jbeclapez SSC-Addicted Points: 405 More actions · Answer 1

By the way, I tried that but it is not working :

SELECT
Names.productName
,NameProperty.Value
,NameProperty.Property
FROM Names
RIGHT OUTER JOIN NameProperty
ON Names.productName = NameProperty.productName
GROUP BY Names.productName
,NameProperty.Property
,NameProperty.Value

jbeclapez SSC-Addicted Points: 405 More actions · Answer 2

OMG! Got it working i think!

I tried the same earlier and it did not work! Do not know what i did wrong!

SELECT
Names.productName
,NameProperty.Value
,NameProperty.Property
FROM Names
LEFT OUTER JOIN NameProperty
ON Names.productName = NameProperty.productName
WHERE
NameProperty.Property = 'Food'
or NameProperty.Property IS NULL

GROUP BY Names.productName
,NameProperty.Property
,NameProperty.Value

ScottPletcher SSC Guru Points: 101097 More actions · Answer 3

It's better to put all conditions related to the LEFT table directly in the LEFT JOIN, i.e.:

SELECT
Names.productName
,NameProperty.Value
,NameProperty.Property
FROM Names

LEFT OUTER JOIN NameProperty
ON Names.productName = NameProperty.productName 
AND NameProperty.Property = 'Food' --<<--

GROUP BY Names.productName
,NameProperty.Property
,NameProperty.Value

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".