Left join - Show one entry 1

[apex82]

I have a System_PDF table and a System table. The System_PDF table allows more than one entry to be associated with the System table with the join being on the System.System_ID and System_PDF.SystemID.

My problem is when I show a list of records from the System table - any record with more than one associated record in the System_PDF table is duplicated in the list.

I have the tables joined so that if any record in the System table that has a missing field in an associated table can be highlighted

I’ve spent a long time tying to figure this out how I make it so I only get one entry in the list?

Can anyone help?

Below is the code with the highlighted areas:

<%

sql = "SELECT * FROM Business  " & _
"LEFT JOIN [Business_Logos] ON Business.LogoID = [Business_Logos].Logo_ID   " & _
"INNER JOIN System ON Business.Business_Id = System.BusinessID AND [UnitID] = '1'  " & _
[COLOR=red]"LEFT JOIN System_PDF ON System_PDF.SystemID = System.System_ID " & _[/color]
"LEFT JOIN System_Terms ON System_Terms.SystemID = System.System_ID " & _
"ORDER BY [System Code] ASC;"

Set rs = obj_CN.Execute(sql, adBoolean)
While Not rs.EOF

s_logo =  rs("Logo_ID")

s_code =  rs("System Code")

Response.Write "<a href=""records.asp?id=" & server.UrlEncode(s_code) & """"

if trim(rs("System Code")) = "" or _
trim(rs("System Description")) = "" or _
isnull(rs("BusinessID")) or _
isnull(rs("Installment System")) or _
isnull(rs("Terms Text")) or _
[COLOR=red]isnull(rs("PDF_ID")) or _[/color]
isnull(rs("CoverID")) then

Response.Write "class=""highlight"""
end if

Response.Write ">" & "<img src=""display.asp?id=" & s_logo & """ border=""0"">" &  " " & rs("System Code") & "</a>" & "|" & vbTab & "<br />"

rs.MoveNext
Wend

%>

 

[gmmastros]

If I understand correctly, for each SystemId, you want to display only one row? If this is correct...

<%

sql = "SELECT * FROM Business  " & _
"LEFT JOIN [Business_Logos] ON Business.LogoID = [Business_Logos].Logo_ID   " & _
"INNER JOIN System ON Business.Business_Id = System.BusinessID AND [UnitID] = '1'  " & _
"LEFT JOIN System_PDF ON System_PDF.SystemID = System.System_ID " & _
"LEFT JOIN System_Terms ON System_Terms.SystemID = System.System_ID " & _
"ORDER BY [System Code] ASC;"

Set rs = obj_CN.Execute(sql, adBoolean)
[!]SystemId = -1[/!]
While Not rs.EOF

  [!]If SystemId <> RS("SystemID") Then[/!]
    s_logo =  rs("Logo_ID")
    s_code =  rs("System Code")

    Response.Write "<a href=""records.asp?id=" & server.UrlEncode(s_code) & """"

    if trim(rs("System Code")) = "" or _
       trim(rs("System Description")) = "" or _
       isnull(rs("BusinessID")) or _
       isnull(rs("Installment System")) or _
       isnull(rs("Terms Text")) or _
       isnull(rs("PDF_ID")) or _
       isnull(rs("CoverID")) then

       Response.Write "class=""highlight"""
    end if

    Response.Write ">" & "<img src=""display.asp?id=" & s_logo & """ border=""0"">" &  " " & rs("System Code") & "</a>" & "|" & vbTab & "<br />"
  [!]End If
  SystemId = RS("SystemId")  [/!]
  rs.MoveNext
Wend

%>

The idea here is.... when a SystemId has multiple pdf's, we will display that data from the first one. When you loop again, you will have the same SystemID but a different pdf_id. If the SystemId is the same as it was in the previous loop, then don't display anything.

Alternatively, you could modify the SQL so that only a single row is returned. This is how I would approach the problem. It makes the SQL Code a little more difficult to write, but it makes the ASP code simpler.

If you prefer to do this in the SQL code, then please indicate the table that each piece of data is in. Specifically... the columns you are using in the ASP code.

It seems to me like you don't care what data is in the system_pdf table, and that you are only concerned whether there is any data in the table for a specific system_id.

What type of database are you using? Microsoft SQL Server?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[apex82]

Thanks for your help gmmastros.

Yep, you're right, I am only concerned about whether there is data in the system_pdf table that relates to the system_id.

The database is Microsoft SQL Server.

I have added the amends and that prevents any "duplicates".

The snag is that is seems to prevent any other records from the System table showing in the list.

Am I missing something obvious?

 

[gmmastros]

The problem could be caused by the Select * part. Think of it this way, you are joining system to System_PDF on System_ID, which means that one column exists in both tables. The * will return this column multiple times (per row). Since this code references System_ID, which one is it actually referring to? The one in System, or the one in System_PDF?

If this were my project, I would re-write the query so that it uses a derived table to return the count of rows from the system_pdf table.

For example, run this in a Query Window to see what I mean.

SELECT *, Coalesce(PDF_Counts.PDF_Count, 0) As PDF_Count 
FROM   Business  
       LEFT JOIN [Business_Logos] 
         ON Business.LogoID = [Business_Logos].Logo_ID
       INNER JOIN System 
         ON Business.Business_Id = System.BusinessID 
         AND [UnitID] = '1'  
       LEFT JOIN [!](
         Select System_ID,
                Count(*) As PDF_Count
         From   System_PDF
         Group BY System_ID
         ) As PDF_Counts
         ON PDF_Counts.SystemID = System.System_ID [/!]
       LEFT JOIN System_Terms 
         ON System_Terms.SystemID = System.System_ID 
ORDER BY [System Code] ASC

Also, notice the part in RED. The inner query part simply returns each system_id and the number or rows for it. This is joined to the outer query so that it returns the count in the output column list.

I also suggest that you remove the * part and list out the columns you need instead. This will make the query run faster, too.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[apex82]

Thanks for all your help.

I think as you guessed I was unbelievably referring to the wrong one!

I just needed to change the following and it is now working.

If SystemId <> RS("SystemID") Then
…
SystemId = RS("System_ID")

I will also look at the other example you have posted.

Thanks again!

 

[gmmastros]

Like I said earlier... I think it's better to handle this in the SQL code. Imagine the extreme. Suppose you have 100 system_id's and 99 of them have 1000 PDF's, and one doesn't have any. The query you have written will return 99001 rows when you really only need 100 rows. Suffice to say, you end up with needless data and extra looping. All of this will ultimately cause your page to load slower and your server to be less scalable.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom