CODEWITHSUNDEEP
sql-serverdatabasesql-server-2008server-side
user2274204
user2274204

Reputation: 345

Inner join return multiple rows

I have tables like this : 

Cities
id   CityName
1    NewYork
2    Dubai



Categories
id   CatName
1    Gaming
2    Health


BusinessFullData
id   BusName  BusCity  BusCategory
1    NewYork  1        2
2    Dubai    2        2
3    Germany  1        1
4    Egypt    2        1

i have try this : 

Select distinct Bus.Id,Bus.BusName,Bus.BusCity,Bus.BusCategory From BusinessFullData Bus 
INNER JOIN Categories Cat ON Bus.BusCategory = 1
INNER JOIN Cities Cts ON Bus.BusCity= 1
Where Bus.Id=@Id

But i get multiple rows. I don't know why.

What i need is to return the Business Data (with cityName and CategoryName text). by using the id of the Bus.Id

Thanks for explaining and help.

Upvotes: 0

Views: 5730

Answers (2)

Andrey Baulin
Andrey Baulin

Reputation: 679

If I correctly understood your question, this should help you:

Select distinct Bus.Id,Bus.BusName,Bus.BusCity,Bus.BusCategory From BusinessFullData Bus 
INNER JOIN Categories Cat ON Bus.BusCategory = Cat.Id
INNER JOIN Cities Cts ON Bus.BusCity= Cts.Id
Where Bus.Id=@Id
AND Bus.BusCategory = 1
AND Bus.BusCity= 1

Upvotes: 0

pmbAustin
pmbAustin

Reputation: 3970

I think this is what you want... this is just some sample code I cleaned up a little, and it works for me. Is this close?

DECLARE @Towns TABLE (id int, TownName varchar(20));
INSERT INTO @Towns (id, TownName)
VALUES (1, 'NewYork'), (2, 'Dubai')

DECLARE @Categories TABLE (id int, CatName varchar(20));
INSERT INTO @Categories (id, CatName)
VALUES (1, 'Gaming'), (2, 'Health')

DECLARE @BusinessFullData TABLE (id int, BusTown int, BusCat int)
INSERT INTO @BusinessFullData (id, BusTown, BusCat)
VALUES (1, 1, 2), (2, 2, 2)

SELECT bus.Id, cat.CatName, twn.TownName, bus.BusTown 
FROM @BusinessFullData bus 
INNER JOIN @Categories cat ON bus.BusCat = cat.id
INNER JOIN @Towns twn ON bus.BusTown = twn.id
WHERE Bus.Id = 2

Please note I just set up some table variables and inserted test data into them in order to make the final SELECT work without having to do anything special. This is just to try and get the idea across to you.

Note I removed the redundant city name, and instead pull that from the "towns" table. Likewise, I pull the Category name from the Categories table instead of just printing the integer.

Basically, the "BusinessFullData" table references the Towns and Categories tables. Joining them on their common indexes (BusTown to Towns.id, and BusCat to Categories.id) brings all the data together, and the filter selects out just one row basd on the business id.

Upvotes: 1

Related Questions