SQL Server Performance

help with sql joins .. please!!!

Discussion in 'Getting Started' started by jmitch421, May 11, 2007.

  1. jmitch421 New Member

    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
  2. FrankKalis Moderator

    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
  3. DilliGrg Member

    Little typo there...[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />WHERE tblCountrySeason.SeasonID = @seasonID<br /></font id="code"></pre id="code"><br /><br /><pre id="code"><font face="courier" size="2" id="code"><br /><br />Name<br />--------- <br />Dilli Grg <br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code">
  4. Luis Martin Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by DilliGrg</i><br /><br />Little typo there...[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />WHERE tblCountrySeason.SeasonID = @seasonID<br /></font id="code"></pre id="code"><br /><br /><pre id="code"><font face="courier" size="2" id="code"><br /><br />Name<br />--------- <br />Dilli Grg <br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Al mejor cazador se le escapa la presa[<img src='/community/emoticons/emotion-4.gif' alt=':p' />].<br />Best hunter sometimes loose prey (goolge translation)<br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">All in Love is Fair <br />Stevie Wonder<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />
  5. satya Moderator

    LOL Luis [<img src='/community/emoticons/emotion-2.gif' alt=':D' />] a well matched saying...<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>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.</i></font id="size1"></font id="teal"></center>
  6. chiragkhabaria New Member

    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/
  7. FrankKalis Moderator

    Thanks for catching the typo. (<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />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.<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  8. Madhivanan Moderator

  9. jmitch421 New Member

    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
  10. jmitch421 New Member

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

    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
  12. DilliGrg Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Luis Martin</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by DilliGrg</i><br /><br />Little typo there...[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />WHERE tblCountrySeason.SeasonID = @seasonID<br /></font id="code"></pre id="code"><br /><br /><pre id="code"><font face="courier" size="2" id="code"><br /><br />Name<br />--------- <br />Dilli Grg <br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Al mejor cazador se le escapa la presa[<img src='/community/emoticons/emotion-4.gif' alt=':p' />].<br />Best hunter sometimes loose prey (goolge translation)<br /><br />Luis Martin<br />Moderator<br /><br /><br />SQL-Server-Performance.com<br /><br /><font size="1">All in Love is Fair <br />Stevie Wonder<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><br />LOL Luis. That was good one. [<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br /><br />Name<br />--------- <br />Dilli Grg <br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code">

Share This Page