Databases
StackOverflow2013
Postsdbo
POHow to use a LEFT OUTER JOIN with a filter that returns the first row in a child table?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to use a LEFT OUTER JOIN with a filter that returns the first row in a child table?
    primarykey
    data
    text
    <p>Assume for a moment I have the following tables. </p> <pre><code>CREATE TABLE [dbo].[Party] ( [PartyId] [int] IDENTITY(1,1) NOT NULL, [DisplayName] [varchar](128) NULL, [IsDeleted] [bit] NOT NULL CONSTRAINT [PK_Party] PRIMARY KEY CLUSTERED ( [PartyId] ASC ) ) GO CREATE TABLE [dbo].[Address] ( [AddressId] [int] IDENTITY(1,1) NOT NULL, [PartyId] int NOT NULL, [AddressLine1] [varchar](128) NULL, [City] [varchar](128) NULL, [State] [varchar](128) NULL, [Zip] [varchar](128) NULL, [IsDeleted] [bit] NOT NULL CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED ( [AddressId] ASC ) ) GO </code></pre> <p>The Party table looks as follows:</p> <pre><code>PartyId DisplayName IsDeleted 1 Party A 0 2 Party B 0 3 Party C 0 4 Party D 0 </code></pre> <p>The address as follows:</p> <pre><code>AddressId PartyId AddressLine1 City State Zip IsDeleted 1 1 1 Market Street San Francisco CA 94111 1 2 1 20 Powell Street San Francisco CA 94111 0 3 1 3 Davis Street San Francisco CA 94111 0 4 2 1 ABC Street San Francisco CA 94111 1 5 2 NULL NULL CA 94111 0 6 2 NULL NULL NULL NULL 0 7 2 1 DEF Street San Francisco CA 94111 0 8 3 1 XYZ AVE San Francisco CA 94111 1 </code></pre> <p>I'm trying to write a query that would return the following addresses:</p> <pre><code>PartyId AddressLine1 City State Zip 1 20 Powell Street San Francisco CA 94111 2 1 DEF Street San Francisco CA 94111 3 NULL NULL NULL NULL 4 NULL NULL NULL NULL </code></pre> <p>The criteria are to return the first non deleted addresses that are complete for each party, otherwise return NULL.</p> <p>This is what I tried:</p> <p>First, I started with a query to give me the party and corresponding addresses that were not deleted. Lets call it Query1.</p> <pre><code>SELECT Party.PartyId, Address.AddressLine1, Address.City, Address.State, Address.Zip FROM Party LEFT OUTER JOIN Address ON Party.PartyId = Address.PartyId WHERE (Party.IsDeleted IS NULL OR Party.IsDeleted = 0) AND (Address.IsDeleted IS NULL OR Address.IsDeleted = 0) ORDER BY Party.PartyId desc </code></pre> <p>It does return addresses that are incomplete as well as several rows of addresses that I don't need. </p> <p>The following query gets rid of the rows other than the first, as well as any incomplete addresses. However, but it also eliminates rows of parties that had no address or incomplete addresses. Lets call it Query2. </p> <pre><code>SELECT DISTINCT p.PartyId, CASE WHEN LEN(LTRIM(RTRIM(pa.AddressLine1))) &gt; 0 AND LEN(LTRIM(RTRIM(pa.City))) &gt; 0 AND LEN(LTRIM(RTRIM(pa.State))) &gt; 0 AND LEN(LTRIM(RTRIM(pa.Zip))) &gt; 0 THEN LTRIM(RTRIM(pa.AddressLine1)) ELSE NULL END AddressLine1, CASE WHEN LEN(LTRIM(RTRIM(pa.AddressLine1))) &gt; 0 AND LEN(LTRIM(RTRIM(pa.City))) &gt; 0 AND LEN(LTRIM(RTRIM(pa.State))) &gt; 0 AND LEN(LTRIM(RTRIM(pa.Zip))) &gt; 0 THEN LTRIM(RTRIM(pa.City)) ELSE NULL END City, CASE WHEN LEN(LTRIM(RTRIM(pa.AddressLine1))) &gt; 0 AND LEN(LTRIM(RTRIM(pa.City))) &gt; 0 AND LEN(LTRIM(RTRIM(pa.State))) &gt; 0 AND LEN(LTRIM(RTRIM(pa.Zip))) &gt; 0 THEN LTRIM(RTRIM(pa.State)) ELSE NULL END State, CASE WHEN LEN(LTRIM(RTRIM(pa.AddressLine1))) &gt; 0 AND LEN(LTRIM(RTRIM(pa.City))) &gt; 0 AND LEN(LTRIM(RTRIM(pa.State))) &gt; 0 AND LEN(LTRIM(RTRIM(pa.Zip))) &gt; 0 THEN LTRIM(RTRIM(pa.Zip)) ELSE NULL END PostalCode FROM Party p LEFT OUTER JOIN ( SELECT PartyId, AddressLine1, City, State, Zip, ROW_NUMBER() OVER ( PARTITION BY PartyId ORDER BY AddressLine1, City, State, Zip ) RowNumber FROM Address WHERE LEN(LTRIM(RTRIM(AddressLine1))) &gt; 0 AND LEN(LTRIM(RTRIM(City))) &gt; 0 AND LEN(LTRIM(RTRIM(State))) &gt; 0 AND LEN(LTRIM(RTRIM(Zip))) &gt; 0 AND (IsDeleted IS NULL OR IsDeleted = 0) ) pa ON p.PartyId = pa.PartyId WHERE pa.RowNumber = 1 AND (p.IsDeleted IS NULL OR p.IsDeleted = 0) ORDER BY PartyId DESC </code></pre> <p>I'm a tad lost as to why the <code>LEFT OUTER JOIN</code> in Query2 doesn't behave the same as Query1. In essence, why Query2 doesn't parties with invalid addresses.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USbloudraak
    1. USbloudraak
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload