SQL Server Performance Forum – Threads Archive

help with sql joins .. please!!!

I need to populate a drop down list of countries from a sql database. I have the following tables tblCountry tblSeason tblCountrySeason
Sample data
tblCountry tblSeason
1 USA ……………1 SPRING
2 MEXICO ……….2 SUMMER
3 ENGLAND ……..3 FALL tblCountrySeason
columns ID SeasonID CountryID
1…..1……1…….aka SPRING USA
2…..2……1…….aka SUMMER USA
3…..2……2…….aka SUMMER MEXICO
4…..1……3…….aka SPRING ENGLAND I want to first have the user select the Season from a drop down list then have a drop down only showing countries that are listed for that season. So if they select Summer it should only show USA and Mexico in the drop down list I know i need to do a join somehow but hopefully someone can help me

Something along these lines should do:
SELECT countryID, country
FROM tblCountry
JOIN tblCountrySeason
ON tblCountry.countryID = tblCountrySeason.CountryID
WHERE tblCountrySeason = @seasonID Assuming this is in a stored procedure and @seasonID is submitted as input paramter from the dropdown box —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

Little typo there…[<img src=’/community/emoticons/emotion-1.gif’ alt=’

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by DilliGrg Little typo there…[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] <pre id="code"> WHERE tblCountrySeason.SeasonID = @seasonID </pre id="code"> <pre id="code"> Name ——— Dilli Grg (1 row(s) affected) </pre id="code"> <hr height="1" noshade id="quote"></blockquote id="quote"> Al mejor cazador se le escapa la presa[<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />]. Best hunter sometimes loose prey (goolge translation) Luis Martin Moderator SQL-Server-Performance.com All in Love is Fair Stevie Wonder All postings are provided â€œAS ISâ€ with no warranties for accuracy.

LOL Luis [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] a well matched saying… Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center>

of Lately I have became a Fan of Exists Queries.. so here there is another way.. for the same.. SELECT t1.Country,t1.CountryID
FROM tblCountry t1
Where
Exists
(
Select 1 From tblCountrySeason t2 Where t1.CountryID = t2.CountryID And
t1.SeasonID = @@seasonID
) Chirag http://chirikworld.blogspot.com/

Thanks for catching the typo. (<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] EXISTS queries are fine. Unfortunately they do not seem very optimized in SQL Server. They often produce plans with a Lazy Spool Operator. Not the "best" operator. – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Anyway OP has to learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Madhivanan Failing to plan is Planning to fail

thank you for all the help .. SELECT tblCountry.CountryName
FROM tblSeason INNER JOIN
tblCountrySeason ON tblSeason.SeasonID = tblCountrySeason.SeasonID INNER JOIN
tblCountry ON tblCountrySeason.CountryID = tblCountry.CountryID
WHERE (tblSeason.SeasonID = ‘<%Session("Season")%>’) this works in query works but Im trying to set the session("season") from the season drop down and I get the following error Conversion failed when converting the varchar value ‘<%Session("Season")%>’ to data type int. I hoep someone can please help me out thank you

i meant to say this works in query analyzer .. if i put a value "1" but its not working when executed from the code

That’s because of data type precedence. INT has a higher precedence than any string. Try a
WHERE … = CAST( <expression> AS VARCHAR(5)) Sorry, don’t know how to concatenate strings in ASP. In case you’re putting this together dynamically. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Luis Martin <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by DilliGrg Little typo there…[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]. Best hunter sometimes loose prey (goolge translation) Luis Martin Moderator SQL-Server-Performance.com All in Love is Fair Stevie Wonder All postings are provided â€œAS ISâ€ with no warranties for accuracy. <hr height="1" noshade id="quote"></blockquote id="quote"> LOL Luis. That was good one. [<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />] <pre id="code"> Name ——— Dilli Grg (1 row(s) affected) </pre id="code">

]]>