Inner Join

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • sade
    New Member
    • Jul 2007
    • 6

    #1

    Inner Join

    I have two tables, table a and table b.
    I do :

    [CODE=mysql]select *
    from a
    inner join b
    on b.some = a.something
    where b.another = somevalue[/CODE]

    How can I put another WHERE to sort my data but for an attribute from table a.
    When I put it after the last WHERE it doesn't bring me any results.

    Thanks
    Last edited by mwasif; Jul 31 '07, 03:21 PM. Reason: Added code tags
  • r035198x
    MVP
    • Sep 2006
    • 13225

    #2
    Originally posted by sade
    I have two tables, table a and table b.
    I do :

    select *
    from a
    inner join b
    on b.some = a.something
    where b.another = somevalue

    How can I put another WHERE to sort my data but for an attribute from table a.
    When I put it after the last WHERE it doesn't bring me any results.

    Thanks
    Can you post the query that didn't work?
    You only put one where and separate the conditions with an and if you want to add additional filtering. For sorting you use order by not a where clause.

    Comment

    • mwasif
      Recognized Expert Contributor
      • Jul 2006
      • 802

      #3
      Elboratingr 035198x's answer with examples

      If you want to add another WHERE condition, simply add AND and the condition
      [CODE=mysql]select * from a
      inner join b
      on b.some = a.something
      where b.another = somevalue
      AND a.comecolumn = 'some value'[/CODE]
      This is the answer to your adding order by (sorting the results)
      [CODE=mysql]select * from a
      inner join b
      on b.some = a.something
      where b.another = somevalue
      ORDER BY a.somecolumn[/CODE]
      But still we want to see which query you have tried.

      Comment

      • sade
        New Member
        • Jul 2007
        • 6

        #4
        [CODE=mysql]SELECT * Person.FullName , Person.BirthDat e, Person.Address
        FROM Partners
        INNER JOIN Person
        ON Person.PartnerI D = Partners.Custom er and Person.BirthDat e > '1980-10-05'
        where Partners.Produc er = '158'[/CODE]

        That's the query. If I don't use the last line (where Partners.Produc er = '158'), it returns me results. But when I use a last where for any property of table Partners, the query just runs and runs... no results.
        Thank you for your time.
        Last edited by mwasif; Aug 1 '07, 11:53 AM. Reason: Added code tags

        Comment

        • r035198x
          MVP
          • Sep 2006
          • 13225

          #5
          Originally posted by sade
          SELECT * Person.FullName , Person.BirthDat e, Person.Address
          FROM Partners
          INNER JOIN Person
          ON Person.PartnerI D = Partners.Custom er and Person.BirthDat e > '1980-10-05'
          where Partners.Produc er = '158'

          That's the query. If I don't use the last line (where Partners.Produc er = '158'), it returns me results. But when I use a last where for any property of table Partners, the query just runs and runs... no results.
          Thank you for your time.
          I suppose you have read the responses given above?

          Comment

          • sade
            New Member
            • Jul 2007
            • 6

            #6
            Thanks for your quick responses.
            I am trying the way, you told me, but it doesn't work.
            Maybe it's a database problem.

            Comment

            • mwasif
              Recognized Expert Contributor
              • Jul 2006
              • 802

              #7
              Kinldy use appropriate code tags when posting code/syntax. Please follow this site's Posting Guidelines when posting questions or answers.

              Comment

              • mwasif
                Recognized Expert Contributor
                • Jul 2006
                • 802

                #8
                You can not write the query like
                [CODE=mysql]SELECT * Person.FullName , Person.BirthDat e...[/CODE]
                You must have to use comma to separate column names in the SELECT.
                [CODE=mysql]SELECT *, Person.FullName , Person.BirthDat e...[/CODE]
                You can write your query as below too (I have moved Person.BirthDat e > '1980-10-05' to WHERE clause)
                [CODE=mysql]SELECT * FROM Partners
                INNER JOIN Person
                ON Person.PartnerI D = Partners.Custom er
                WHERE Person.BirthDat e > '1980-10-05' AND Partners.Produc er = '158'[/CODE]
                Make sure you have the records where Producer = '158' and the date is greater than '1980-10-05'.
                Last edited by mwasif; Aug 2 '07, 11:39 AM. Reason: Corrected the syntax error

                Comment

                • sade
                  New Member
                  • Jul 2007
                  • 6

                  #9
                  Because of many records I have in the database, it takes too long to bring results and most times it just crashes.
                  Question can be closed.
                  Thank you for your answers and your time.

                  Comment

                  • mwasif
                    Recognized Expert Contributor
                    • Jul 2006
                    • 802

                    #10
                    Because of many records I have in the database, it takes too long to bring results and most times it just crashes.
                    Consider optmizing your query by creating indexes on Person.BirthDat e and Partners.Produc er. This will definetly reduce your query time.

                    Comment

                    Working...