Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81

    Unanswered: Problem iwth sql query

    Hey all,
    I have 5 tables with TID columns in it.. I need to select similar data from different tables.
    But as i do join the data gets so huge that i cant make use of it properly...
    Even though i have put where clause but its just returning hell of data...
    Most of the times i m reading different tables for just one column.
    Table A
    colA
    colAB
    ColAC

    Table B
    colA
    colBA
    colBC

    Table C
    colA
    ColCA
    colCD

    Tbale D
    colA
    colDA
    colDB

    Tble E
    colA
    ColEA
    colEB


    my sql is

    Select ,,,
    FROM tblA INNER JOIN
    TblB ON tbelA.COlA = tblB.ColA INNER JOIN
    TbaleC ON tbelA.COlA = TbaleC.COlA INNER JOIN
    tableD ON tbelA.COlA = tableD.ColA INNER JOIN
    tableE ON tbelA.COlA = tableE.ColA
    where blah blah...

    Any help would be appreciated
    thx

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, you were pretty liberal with your As and Bs and Cs, very generic, so i will answer your question with an example of my own

    the short answer is you're getting cross join effects

    here's my example: suppose you have a table for people, a table for blouses, and a table for hats

    each blouse belongs to only one person, and each hat belongs to only one person

    so now you want a query to find all of mary's blouses and hats

    if you just do the join the way you're doing it, here's what happens -- if mary has 4 blouses and 7 hats, the query returns 28 rows for mary

    that's your problem, except greatly compounded because of the extra tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81
    thats true..
    how could i avoid this situation????coz its necessary to hv all the tables in the query.....

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you'll have to tell me

    there's nothing in any or your table layouts to suggest what they mean

    what about a UNION of tables B through E?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81
    I would ask how would you put the above mentioned example of blouses and stuff in query form...?
    So that only limited data is selected...

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, fair deal

    but please first show me how you want the blouses and hats data arranged in tabular form
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81
    Here is the query...
    Would it help???


    SELECT DISTINCT
    RESULTS_CHECKS.CHECKNAME, ASSETMAIN.VERSIONNUMBER, RESULTS_SUMMARY.STARTTIME,
    ' XYZ 'AS ScriptName, '3' AS Version,
    RESULTS_RECORDS.NOTES, RESULTS_RECORDS.LINENUMBER,USERname, machinename
    FROM RESULTS_CHECKS INNER JOIN
    RESULTS_RECORDS ON RESULTS_CHECKS.ID = RESULTS_RECORDS.ID INNER JOIN
    ASSETMAIN ON RESULTS_CHECKS.ID = ASSETMAIN.ID INNER JOIN
    RESULTS_SUMMARY ON RESULTS_CHECKS.ID = RESULTS_SUMMARY.ID INNER JOIN
    RESULTS_USERS ON RESULTS_CHECKS.ID = RESULTS_USERS.ID
    WHERE ( ASSETMAIN.OBJECTTYPE = 8) AND ( ASSETMAIN.NAME = 'XYZ') AND
    ( ASSETMAIN.VERSIONNUMBER = 1) AND ( RESULTS_CHECKS.CHECKNAME IS NOT NULL) AND
    ( RESULTS_SUMMARY.STARTTIME >= '') AND ( RESULTS_RECORDS.RESULT = 2) AND
    ( RESULTS_USERS.USERNAME = 'dumm')and (Results_Records.ID = RESULTS_CHECKS.ID)
    AND (Results_Records.ID = Results_Summary.ID)
    AND(Results_Records.ID = Results_Users.ID)

  8. #8
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I'd say that you probably want a simple display of the counts of each item, which in your case are represented by a distinct table, for each row in some main table. The output will be in the format of one row for each person with a single column for the count of each product.

    But because you want to display aggregate information, from more than one "one to many" child table, as a single set, you will need to first collapse each of these tables so that they contain only one row per common joining key. Otherwise, you'll encounter the problem that Rudy described above.

    Your sample output can be,

    MainID, count(ItemA), count(ItemB), count(ItemC)
    etc

    You can use correlated sub-queries to solve this, but that is an awful approach to use. Namely because it's too much like a procedural approach. These type are also annoying to read for people who don't use a procedural approach as their main tool to solving these kinds of problems.

    The other two ways you can solve it are to use either a UNION for each set and a CASE clause at the SELECT, or to create a collapsed inline view of the aggregation for each child table and then join each of these tables back to the master.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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