Multiple Joins to the same Table in a Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Multiple Joins to the same Table in a Query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-30 : 08:58:31

Jordan writes "I want to write an SQL query that lets the user specify at least 3 ingredients and returns drink recipes that contain only those ingredients.

Drinks
------
Drink_id (pk)
Drink_name
Drink_description
Drink_type

Ingredients
-----------
Ingredient_id (pk)
Ingredient_name

Amounts
-------
Amount_id (pk)
Amount_name

Ingredients_track
-----------------
Id (pk)
Drink_id
Ingredient_id
Amount_id"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-30 : 09:13:22


CREATE    FUNCTION ufn_ParseArray
(
    @array     varchar(8000),
    @separator varchar(10)
) 
RETURNS @Parsed TABLE
(
    ArrayID    INT     IDENTITY(1,1),
    Value      VARCHAR(1000)
)
AS
BEGIN

DECLARE @separator_position INT
DECLARE @array_value        VARCHAR(1000) -- this holds each array value as it is returned


--Special case
IF DATALENGTH(ISNULL(@array,'')) = 0
BEGIN
    INSERT INTO @Parsed (Value) VALUES (NULL)
    RETURN
END


SET @array = @array + @separator

-- Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @separator + '%' , @array) <> 0 
BEGIN

    SELECT @separator_position =  PATINDEX('%' + @separator + '%' , @array)
    SELECT @array_value = LEFT(@array, @separator_position - 1)
    
    INSERT INTO @Parsed (Value) VALUES (@array_value)

    SELECT @array = STUFF(@array, 1, @separator_position  + datalength(@separator)-1, '')
END

RETURN
END
GO

create proc usp_getdrinks
    @inglist varchar(8000) -- comma delim list of ingrediants
as
create table #inglist (
    ingid  int,
    ing    varchar(1000)
)

insert #inglist
select * from dbo.ufn_parsearray(@inglist,',')

--insert error handling for < 3 ing

select
    d.drink_name
from
    drinks d
    inner join ingredients_track it
        on d.drink_id = it.drink_id
    inner join ingredients i
        on it.ingredient_id = i.ingredient_id
    inner join #inglist il
        on i.ingredient_name = il.ing
go


exec usp_getdrinks 'grey goose vodka, vermoth, olive'
go

<O>

MakeYourDaddyProud

184 Posts

Posted - 2002-05-30 : 09:47:21

Assuming...

create table #Drinks
(Drink_id int primary key, Drink_name varchar(20) not null,
Drink_description varchar(100) not null, Drink_type int null
)
go

create table #Ingredients
(Ingredient_id int primary key, Ingredient_name varchar(20) not null
)
go

create table #Amounts
(Amount_id int primary key, Amount_name varchar(20)
)
go

create table #Ingredients_track -- Or RECIPES
([Id] int primary key, Drink_id int not null,
Ingredient_id int not null, Amount_id int not null
) -- and hence set up the foreign key relationships if required
go

-- here is the solution

create procedure #p_list_recipes

@ingredient_name1 varchar(20),
@ingredient_name2 varchar(20),
@ingredient_name3 varchar(20)

as

select D.*
from
#Drinks D
join
(select IT.drink_id, IT.[id], count(*) AS TOTAL_INGREDIENTS
from #Ingredients_track IT
join #Ingredients I ON I.Ingredient_ID = IT.Ingredient_ID
and I.Ingredient_name IN (@ingredient_name1,@ingredient_name2,@ingredient_name3)
group by IT.[id], IT.Drink_id
having count(*) = 3) ONLY3
on ONLY3.Drink_id = D.Drink_ID

-- you will need to remove the # symbols from the table names and sp name
-- other than that should be OK

Daniel Small CEO
www.danielsmall.com

MakeYourDaddyProud

184 Posts

Posted - 2002-05-30 : 09:47:56

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources