Loading ...
Sorry, an error occurred while loading the content.

[SQLQueriesNoCode] Only select where 1-to-1 relationship exists?

Expand Messages
  • Travis Truax
    If I have table A and I want to join it to table B , but would only like to select rows in B that have a 1-to-1 relationship with A - how could I
    Message 1 of 4 , Jul 24, 2007
    • 0 Attachment
      If I have table "A" and I want to join it to table "B", but would only like
      to select rows in "B" that have a 1-to-1 relationship with "A" - how could I
      accomplish this?

      My first thoughts were using count() and a having clause - but I couldn't
      piece anything together that worked properly.

      Thanks,
      Travis-
    • William Taylor
      You could do something like this: Select TableA.Field1,TableB.Field1 from TableB (nolock) inner join TableA (nolock) on TableA.Field1=TableB.Field1 ... From:
      Message 2 of 4 , Jul 24, 2007
      • 0 Attachment
        You could do something like this:

        Select TableA.Field1,TableB.Field1
        from TableB (nolock)
        inner join TableA (nolock) on
        TableA.Field1=TableB.Field1


        ----- Original Message ----
        From: Travis Truax <travis.truax@...>
        To: "SQLQueriesNoCode@yahoogroups.com" <SQLQueriesNoCode@yahoogroups.com>
        Sent: Tuesday, July 24, 2007 8:34:33 AM
        Subject: [SQLQueriesNoCode] Only select where 1-to-1 relationship exists?

        If I have table "A" and I want to join it to table "B", but would only like
        to select rows in "B" that have a 1-to-1 relationship with "A" - how could I
        accomplish this?

        My first thoughts were using count() and a having clause - but I couldn't
        piece anything together that worked properly.

        Thanks,
        Travis-



        [Non-text portions of this message have been removed]
      • John Warner
        What determines if there is a one to one relationship between rows in the two tables? What I mean is, how do you know such a relation exists unless you have
        Message 3 of 4 , Jul 24, 2007
        • 0 Attachment
          What determines if there is a one to one relationship between rows in the
          two tables? What I mean is, how do you know such a relation exists unless
          you have already imposed a constraint to enforce this? I guess what would
          help here is to see some sample tables and data (DDL).

          John Warner




          > -----Original Message-----
          > From: SQLQueriesNoCode@yahoogroups.com
          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Travis Truax
          > Sent: Tuesday, July 24, 2007 8:35 AM
          > To: 'SQLQueriesNoCode@yahoogroups.com'
          > Subject: [SQLQueriesNoCode] Only select where 1-to-1
          > relationship exists?
          >
          >
          > If I have table "A" and I want to join it to table "B", but
          > would only like to select rows in "B" that have a 1-to-1
          > relationship with "A" - how could I accomplish this?
          >
          > My first thoughts were using count() and a having clause -
          > but I couldn't piece anything together that worked properly.
          >
          > Thanks,
          > Travis-
        • Tim Mitchell
          Try something like this: Table 1: Item_id Some_value1 Table 2: Item_id Some_value2 SELECT a.Item_id, a.Some_value1, b.Some_value2 FROM [Table 1] a INNER JOIN
          Message 4 of 4 , Jul 24, 2007
          • 0 Attachment
            Try something like this:



            Table 1:
            Item_id

            Some_value1



            Table 2:

            Item_id

            Some_value2



            SELECT a.Item_id, a.Some_value1, b.Some_value2

            FROM [Table 1] a INNER JOIN [Table 2] b ON a.Item_id = b.Item_id

            WHERE 1 = (SELECT COUNT(*) from [Table 2] WHERE Item_id = a.Item_id)



            ________________________________

            From: SQLQueriesNoCode@yahoogroups.com
            [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Travis Truax
            Sent: Tuesday, July 24, 2007 7:35 AM
            To: 'SQLQueriesNoCode@yahoogroups.com'
            Subject: [SQLQueriesNoCode] Only select where 1-to-1 relationship
            exists?



            If I have table "A" and I want to join it to table "B", but would only
            like
            to select rows in "B" that have a 1-to-1 relationship with "A" - how
            could I
            accomplish this?

            My first thoughts were using count() and a having clause - but I
            couldn't
            piece anything together that worked properly.

            Thanks,
            Travis-





            [Non-text portions of this message have been removed]
          Your message has been successfully submitted and would be delivered to recipients shortly.