October 10, 2014 at 9:08 am
Hello,
I know that I can join, left outer join, inner join etc. to get results like I can with a vlookup in Excel.
But in Excel, I can perform a vlookup based on criteria in Excel. Can I do that in SQL?
Example:
Dog Name
Boxer MAX
Poodle TINA
Lab JACK
Greyhound NULL
Schnauzer NULL
What I want is Where Name is "NULL" look up the dog in the Name table and give me the name.
Different Question:
Also, for the names that are given, I'd like to know if those Names are in the Name table as a validation check. In Excel I'd run a match function.
Thanks in Advance!
October 10, 2014 at 10:40 am
In SQL you use a WHERE clause to limit results. So in your example your query would like this:
Select * from dbo.Dogs Where Name IS NULL
NULL represents unknown in SQL so the default behavior is that any comparison (=, <>) always returns Unknown, not true or false. So SQL has special handling using IS NULL or IS NOT NULL to evaluate columns that may contain NULL's.
to search for a specific name you would use:
Select * from dbo.Dogs Where Name = 'Max'
To search for a list of names you would use IN:
Select * from dbo.Dogs Where Name IN ('Max', 'Rover', 'Jack')
You need to check your collation to determine if you have case sensitive collation. In the US the SQL Server install defaults to a non-case sensitive collation for the default collation when you create a database or a string database column.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 14, 2014 at 7:12 pm
Hi Jack,
Thanks for your response.
I'm not sure this answers my question.
I would want something like this..
if "my result' is null then look up the name in another table.
October 15, 2014 at 6:24 am
Without knowing what tables are involved it is hard to give you a complete answer, if you were to post your question using the guidelines in the top link in my signature it would be easier to provide an answer that will work. I currently have to guess. I think you are on track with a JOIN to get the name, I just have no idea what you would use as your join column. Here's something that I hope will help and gives an example of how you can post a question so we can provide an answer that is more likely to be accurate and helpful:
/* Tables to store test data that are similar to, if not exactly the same as,
the real schemas */
DECLARE @dogs TABLE
(
dogID INT PRIMARY KEY,
dogType VARCHAR(20),
dogName VARCHAR(20)
);
DECLARE @dogNames TABLE
(
dogID INT,
dogName VARCHAR(20)
);
DECLARE @names TABLE (name VARCHAR(20));
/* Create some test data */
INSERT INTO @dogs
(dogID, dogType, dogName)
VALUES
(1, 'Boxer', 'MAX'),
(2, 'Poodle', 'TINA'),
(3, 'Lab', 'JACK'),
(4, 'Greyhound', NULL),
(5, 'Schnauzer', NULL);
INSERT INTO @dogNames
(dogID, dogName)
VALUES
(1, 'MAX'),
(2, 'TINA'),
(3, 'JACK'),
(4, 'ROVER'),
(5, 'SPOT');
INSERT INTO @names
(name)
VALUES
('MAX'),
('TINA'),
('JACK'),
('ROVER');
/* Here's a query that returns the results.
The join between @dogs and @dogNames assumes that
there is an entry in @dogNames for every dog and then
in the SELECT the ISNULL maens if the first value IS NULL
then use the second value. Then I LEFT OUTER JOIN to
the @names table using the ISNULL(D.dogName, DN.dogName)
function to be sure I have a dogName to match with and
join on name. The CASE statement in the select retunrs 1 when
there is a match so you know the name is in the names table */
SELECT
D.dogID,
D.dogType,
ISNULL(D.dogName, DN.dogName) AS dogName,
CASE WHEN N.name IS NULL THEN 0
ELSE 1
END AS nameInNameTable
FROM
@dogs AS D
JOIN @dogNames AS DN
ON D.dogID = DN.dogID
LEFT JOIN @names AS N
ON ISNULL(D.dogName, DN.dogName) = N.name;
I know this won't match your schema, but hopefully it gives you an idea on how to do what you need to do and shows how you can post a question with some examples to help those helping you provide accurate answers.
I should note that using the ISNULL() function in the JOIN is not a best practice because it will hinder performance as the tables will need to be scanned instead of being able to use an index.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy