SQL query to from these two tables-CodePudding

I have these two tables:

CREATE TABLE Customer
(
    Id int PRIMARY KEY IDENTITY(1,1), 
    FirstName varchar(50),
    LastName varchar(50),
    Age int,
    City varchar(50)
)

CREATE TABLE pets
(
    Id int NOT NULL 
        FOREIGN KEY REFERENCES Customer(Id), 
    animal varchar(50),
    color varchar(50),
    Age int
)

I want to get the full names of customers who have an orange cat but it should not give multiple results even for customers who have multiple orange cats.

CodePudding user response：

You can use the exists operator:

SELECT *
FROM   customer c
WHERE  EXISTS (SELECT *
               FROM   pets p
               WHERE  p.animal = 'cat' AND
                      p.color = 'orange' AND
                      p.id = c.id)

CodePudding user response：

You can use Group By or Distinct to get unique results

SELECT 
    FirstName,
    LastName
FROM 
    Customer
LEFT JOIN PETS on CUSTOMER.ID = PETS.ID 
WHERE PETS.COLOR = 'Orange'
and PET.ANIMAL = 'Cat'
GROUP BY FirstName, LastName

SELECT DISTINCT
    FirstName,
    LastName
FROM 
    Customer
LEFT JOIN PETS on CUSTOMER.ID = PETS.ID 
WHERE PETS.COLOR = 'Orange'
and PET.ANIMAL = 'Cat'

CodePudding user response：

We can use DISTINCT Keyword to get unique First and LastNames

SELECT DISTINCT C.FirstName ,C.LastName 
    FROM dbo.Customer C
    INNER JOIN dbo.Pets P ON C.ID = P.ID
    WHERE P.color = 'Orange'
        AND P.animal = 'Cat'

CodePudding user response：

    SELECT 
     FirstName,
     LastName
    FROM 
     Customer
    INNER JOIN PETS on CUSTOMER.ID = PETS.ID 
    WHERE PETS.COLOR = 'Orange' AND PETS.ANIMAL='cat'
    GROUP BY CUSTOMER.ID,FirstName, LastName