Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Aug 2008
    Posts
    2

    Unanswered: One to Many Query....

    Short background: I'm an Air Force Sys Admin, just started learning SQL about 5 days ago on my own. Am using MS Visual Web Development Express to create an asp page to query my database. Is a test db just to learn SQL.

    So, I have three tables I am concerned with right now to make a query. The query runs when a user would select a "Genre" from an asp dropdown list. So far so easy. The problems occurs when I try to write the query to select a TV Show that has more than one Genre and want that show to be included in each Genre. Here is my table structure (sorry if the format is not standard, newbie here!:

    [tblTVMain (
    TVTitle PK
    GenreID FK (reference MoviesTVGenre.GenreID)
    GenreID2 FK (reference MoviesTVGenre.GenreID)
    GenreID3 FK (reference MoviesTVGenre.GenreID)
    )]

    [tblMoviesTVGenre (
    GenreID PK
    GenreDescrip
    )]

    [tblTVSeasons1 (
    TVTitle FK (reference TVMain.TVTtitle)
    Season
    Ep1
    Ep2
    EP3
    )]

    Here is my Select statement with just using one Genre that works fine:

    "SELECT TVSeasons1.TVTitle, MoviesTVGenre.GenreDescrip, TVSeasons1.Season, TVSeasons1.Ep1, TVSeasons1.Ep2, TVSeasons1.Ep3, TVSeasons1.Ep4, TVSeasons1.Ep5

    FROM MoviesTVGenre

    INNER JOIN TVMain ON MoviesTVGenre.GenreID = TVMain.GenreID
    INNER JOIN TVSeasons1 ON TVMain.TVTitle = TVSeasons1.TVTitle

    WHERE MoviesTVGenre.GenreDescrip = @GenreDescrip"




    The only way I can think to have it query two or more genres is to add more INNER JOIN statements and give correlated names for TVMain, such as:

    "SELECT TVSeasons1.TVTitle, MoviesTVGenre.GenreDescrip, TVSeasons1.Season, TVSeasons1.Ep1, TVSeasons1.Ep2, TVSeasons1.Ep3, TVSeasons1.Ep4, TVSeasons1.Ep5

    FROM MoviesTVGenre

    INNER JOIN TVMain ON MoviesTVGenre.GenreID = TVMain.GenreID
    INNER JOIN TVMain TV2 ON MoviesTVGenre.GenreID = TV2.GenreID2
    INNER JOIN TVSeasons1 ON TVMain.TVTitle = TVSeasons1.TVTitle

    WHERE MoviesTVGenre.GenreDescrip = @GenreDescrip"


    I get no errors but the queries don't work, depending how many INNER JOIN statements I add, the query will come up with nothing or it will work for only one TVShow and still just one Genre.

    Let's say the TV Show Dexter has a GenreID for Drama and for Thriller. I want, when the user selects "Drama" from the dropdown list for Dexter to show up and if they select "Thriller" it shows up there as well. Seems pretty simple to me, all the TVMain.GenreID(x) columns are Foreign Keys that refer back to MoviesTVGenre.GenreID. I know the relationships are all working because I can modify the query to use any of the GenerID(x) columns; just only one at a time.

    I probably made this way longer than necessary, I apologize and appreciate your time. If you can tell me where I am going wrong, that would be awesome. Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I think you should address your table structure and properly normalize it You want to make a "pass-through" table.

    tblTVMain(
    TVMainID, --PK
    Somefield)

    tblGenre(
    genreid, --PK
    somefield)

    tblTVMain_x_tblGenre
    (ID,
    tvMainID, --FK
    genreid, --FK
    )

    Once you do this, your query becomes easier to write. This looks more like a many to many relationship
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    swanny, congrats for learning SQL on your own

    i'm just curious, since you are so new to the field, where did you pick up the idea that a table name has to have the letters "tbl" stuck in it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Must be a military thing:
    F15, F16, F18, F22....the F stands for "Fighter".
    B17, B52, B1...the B stands "Bomber".
    tblPeople, tblAddresses, tblPhones....the tbl stands for "Table".
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by r937
    swanny, congrats for learning SQL on your own

    i'm just curious, since you are so new to the field, where did you pick up the idea that a table name has to have the letters "tbl" stuck in it?
    Don't most programmers preface object names with a type designator when working in a typed environment? I do and it really comes in handy, but I suppose some people don't see the value of it. Tables all tb..., views all qry...., stored procedures all sp.......... Even Microsoft does this for Stored Procedures "sp_" "xp_".

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "sp_" and "xp_" mean special things to the SQL Server engine, which is why you should NOT preface your sprocs with these particular characters.

    Prefixing your objects is not only pointless, it unnecessarily casts the object as a certain type, creating a large divide between the logical and physical models, and that is a BAD thing.

    Case in point. On my current assignment the application was referencing a reporting table that was being maintained by triggers. Using an indexed view made more sense, so we simply dropped the old reporting table and create the new view WITH THE SAME NAME AS THE OLD REPORTING TABLE. The change was invisible to the client application. Had we enforced the worn-out "tbl" prefix standard for tables and "vw" for views, we would not have been able to do this so easily.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SQL isn't a typed environment

    you cannot use a column name where a table name has to go, and you cannot use a table name where a column name has to go

    the thing i don't understand is how come the guys who religiously write tbl_employees and tbl_orders don't also stick "col_" in front of every column name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by r937
    the thing i don't understand is how come the guys who religiously write tbl_employees and tbl_orders don't also stick "col_" in front of every column name
    because...

    (said the dog)

    ... that'd be silly




    i love that old joke -- call me if you haven't heard it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by r937
    because...

    (said the dog)

    ... that'd be silly
    My Labrador Retriever, dog_Fido, does not think that is silly at all. But cat_Whiskers says he agrees with you completely.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Aug 2008
    Posts
    2
    Let me clear up all the "tbl' nonense! In my database, the tables are not prefixed with anything. I simply put "tbl" in the thread because I wasn't sure if I was using the correct standard on how to display the table structure. Probably a pretty dumb thing to not know, I admit, but that said, I have only been doing this for less than a week.

    Now that we have all had a good time at my expense; based on my tables...and possibly a pass-thru like Thrasy alluded to, how I can assign more than one genre to a particular title and be able to query for each one in a single query. Please look at the original post; I have multiple FK GenreID columns all refering back to one PK GenreID column. Each FK GenreID has an INT value and it corresponds to a GenreDescription. Basically, the query is called for when a genre is selected from the dropdown list. The dropdown list has each GenreDescription listed; the query is dynamically based on what is selected from the dropdown list. Would some sort of nested query work here?

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Don't mind them Swamy, sometimes they slip their leash when nobody is around to watch them! Around here (DBForums), if you don't get $#!+ from folks, you really don't get $#!+ at all... It kind of goes with the territory.

    Chew on this for a bit, see if a code snippet helps.
    Code:
    --  ptp  20080822  See http://www.dbforums.com/showthread.php?t=1633244
    
    CREATE TABLE tblGenre (
       genreId		INT		IDENTITY
       CONSTRAINT XPKtblGenre
          PRIMARY KEY (genreId)
    ,  name			NVARCHAR(50)
       )
    
    CREATE TABLE tblTitle (
       titleId		INT		IDENTITY
       CONSTRAINT XPKtblTitle
          PRIMARY KEY (titleId)
    ,  name			NVARCHAR(50)
       )
    
    CREATE TABLE tblTitleToGenre (
       titleId		INT		NOT NULL
       CONSTRAINT XFK01tblTitleToGenre
          FOREIGN KEY (titleId)
             REFERENCES tblTitle (titleId)
    ,  genreId		INT		NOT NULL
       CONSTRAINT XFK02blTitleToGenre
          FOREIGN KEY (genreId)
             REFERENCES tblGenre (genreId)
       CONSTRAINT XPKtblTitleToGenre
          PRIMARY KEY (titleId, genreID)
       )
    
    INSERT INTO tblGenre (
       name ) SELECT 'Science Fiction'
       UNION SELECT 'Fantasy'
       UNION SELECT 'Fiction'
       UNION SELECT 'Other'
    
    INSERT INTO tblTitle (
       name) SELECT 'Stranger In A Strange Land'
       UNION SELECT 'I Robot'
       UNION SELECT 'Dune'
       UNION SELECT 'Roget''s Thesaurus'
    
    INSERT INTO tblTitleToGenre (
       titleId, genreId) SELECT 1, 1
       UNION SELECT 2, 1
       UNION SELECT 3, 1
       UNION SELECT 4, 4
       UNION SELECT 1, 2
       UNION SELECT 2, 2
       UNION SELECT 3, 2
    -PatP

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nice code, pat

    i especially like how you used the "tbl" prefix consistently

    also, there are quite a number of commas missing, which should make for some interesting error messages if our new friend swanny tries to run your code...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think you need to have a little T-SQL refresher sir: its all valid.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    omg omg you are right

    sorry pat!!!

    FYI it also works with the commas added

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I still need to work on the code generator (a lot), but I'm pretty sure that it generates valid T-SQL. I found (and fixed) an "off by one" in the sample data extractor, but it still doesn't work for even mildly complex cases. I'd never considered the col prefix that r937 suggested, but that's a relatively simple tweak to the code emitter.

    Oh well, that's what toys are for!

    I'm pretty sure that the T-SQL that it emitted was what I wanted to show. I used the tbl prefix because that's Swami's style... I can do Hungarian Notation or not, as the user (customer) prefers.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •