Problem with table join using both Count() and Group By clause...

[marting]

Before I throw my new expensive laptop out of the window!

I'm stuck on getting my joins right with the correct amount in a column count. After speaking with someone a few weeks back, they suggested I redesigned my database to use more tables of info. Before I had 1 table for image information, now I have 9 and it's getting very, very confusing... especially as I don't know much about joins...!

I am using ASP, MySQL, IIS and VBScript.

My 9 tables consist of:

Images
ImageID, Height, Width, DateCreated, Orientation

Keywords
KeywordID
Keyword

ImageKeyword
ImageId
KeywordID

People
PeopleID
People

ImagePeople
ImageID
PeopleID

Events
EventID
EventName

ImageEvent
ImageID
EventID

Photographers
PhotographerID
FirstName
LastName
Byline

ImagePhotograph er
ImageID
PhotographerID


My code is as follows:

<td background="images/navigation_tabs/search-middle.gif" style="padding-top:5px;"> 
            <% 
            RefineSQL = " SELECT E.EventName , E.EventID , I.City , I.Country , I.DateCreated , " _ 
            & "IE.ImageID , I.ImageID , P.PeopleID , IE.EventID , Count(E.EventName) AS TotalEvent " _ 
            & "FROM images AS I , imagepeople AS IP , people AS P , events AS E , imageevent AS IE " _ 
            & "WHERE P.PeopleID = IP.PeopleID AND IP.ImageID = I.ImageID AND E.EventID = IE.EventID " 
             
            If (Session("event") & "") <> "" Then 
            RefineSQL = RefineSQL & "AND E.EventID = '"&Session("event")&"' " 
            End If 
            If (Session("people") & "") <> "" Then 
            RefineSQL = RefineSQL & "AND P.PeopleID = '"&Session("people")&"' " 
            End If 
            If (Session("location") & "") <> "" Then 
            RefineSQL = RefineSQL & "AND I.City = '"&Session("location")&"' " 
            End If 
            If (Session("keyword") & "") <> "" Then 
            RefineSQL = RefineSQL & "AND K.KeywordID = '"&Session("keyword")&"' " 
            End If 
            If (Session("orientation") & "") <> "" Then 
            RefineSQL = RefineSQL & "AND I.Orientation = '"&Session("orientation")&"' " 
            End If 
            If (Session("composition") & "") <> "" Then 
            RefineSQL = RefineSQL & "AND K.Keyword = '"&Session("composition")&"' " 
            End If 

            RefineSQL = RefineSQL & "GROUP BY E.EventName ORDER BY I.DateCreated DESC LIMIT 8" 

            Rs.Open RefineSQL 
             
            Counter = 0 
             
            Do until Rs.EOF 
            Counter = Counter + 1 
            Response.Write "<TABLE WIDTH=""100%"">" 
                Response.Write "<TR>" 
                    Response.Write "<TD STYLE=""padding-left:8px;"" CLASS=""content_lightgrey_small"">" 
                        ExpandShortDate(Rs("DateCreated")) 
                    Response.Write "</TD>" 
                Response.Write "</TR>" 
                Response.Write "<TR>" 
                    Response.Write "<TD STYLE=""padding-left:8px;"" CLASS=""content_small"">" 
                        Response.Write "<A HREF=""expressResults.asp?side=yes&event="&Rs("EventID")&""" " _ 
                        & "CLASS=""grey_link_small"">"&Replace(Rs("EventName"),"*","'")&"</A> ("&Rs("TotalEvent")&")" 
                    Response.Write "</TD>" 
                Response.Write "</TR>" 
                Response.Write "<TR>" 
                    Response.Write "<TD STYLE=""padding-left:8px;"" CLASS=""content_small"">" 
                        Response.Write Rs("City")&", "&Rs("Country") 
                    Response.Write "</TD>" 
                Response.Write "</TR>" 
                Response.Write "<TR>" 
                    Response.Write "<TD STYLE=""padding-left:8px;"" CLASS=""content_lightgrey_small"">" 
                        Response.Write "<IMG SRC=""images/shim.gif"" HEIGHT=""3"">" 
                    Response.Write "</TD>" 
                Response.Write "</TR>" 
            Response.Write "</TABLE>" 
            Rs.MoveNext 
            Loop 
            Rs.Close 
            %> 
            </td>

Hopefully, if it makes sense to you, it should display a list of events that matches the search criteria stored in the session variables.

As an example, If Victoria Beckham was at the Oscar Awards 2008 and MTV Awards 2007 and I searched for her name, it would show...

18 FEB 08
Oscar Awards 2008 (105 Images)
Los Angeles, USA.

07 JUL 07
MTV Awards 2008 (65 Images)
Berlin, Germany.

It seems pretty simple but I am so confused and behind with the project its starting to hurt a bit.... I know my GROUP BY clause is wrong because of the amount the COUNT is returning, and also maybe joining the wrong columns together.

Any chance I can get some help with the SQL string...? If I see the correct version compared to my crap version, I think I will learn from it quickly. Quicker than the 26 hours I've put into this problem already...!

Many thanks in advance, from a stressed amateur...

[jhardman]

Before I throw my new expensive laptop out of the window!

I'm stuck on getting my joins right with the correct amount in a column count. After speaking with someone a few weeks back, they suggested I redesigned my database to use more tables of info. Before I had 1 table for image information, now I have 9 and it's getting very, very confusing... especially as I don't know much about joins...!

Normalizing a db is a daunting task for a beginner so I can definitely commiserate with you, but as you get the hang of it it makes more sense. hang it there!

I am using ASP, MySQL, IIS and VBScript.

My 9 tables consist of:

Images
ImageID, Height, Width, DateCreated, Orientation

Keywords
KeywordID
Keyword

ImageKeyword
ImageId
KeywordID

People
PeopleID
People

ImagePeople
ImageID
PeopleID

Events
EventID
EventName

ImageEvent
ImageID
EventID

Photographers
PhotographerID
FirstName
LastName
Byline

ImagePhotograph er
ImageID
PhotographerID

I wouldn't really consider myself an expert, but it makes more sense to me to
divide it up like this:

Images
ImageID
Height
Width
DateCreated
Orientation
PhotographerID (each photo is only taken by one photographer, so you don't need a imagePhotograph er table)
EventID (each photo was taken at only one event, so you don't need a imageEvent table)

Keywords
KeywordID
Keyword

ImageKeyword
ImageId
KeywordID

People
PeopleID
People

ImagePeople
ImageID
PeopleID

Events
EventID
EventName

Photographers
PhotographerID
FirstName
LastName
Byline

Hopefully, if it makes sense to you, it should display a list of events that matches the search criteria stored in the session variables.

As an example, If Victoria Beckham was at the Oscar Awards 2008 and MTV Awards 2007 and I searched for her name, it would show...

18 FEB 08
Oscar Awards 2008 (105 Images)
Los Angeles, USA.

07 JUL 07
MTV Awards 2008 (65 Images)
Berlin, Germany.

It seems pretty simple but I am so confused and behind with the project its starting to hurt a bit.... I know my GROUP BY clause is wrong because of the amount the COUNT is returning, and also maybe joining the wrong columns together.

Any chance I can get some help with the SQL string...? If I see the correct version compared to my crap version, I think I will learn from it quickly. Quicker than the 26 hours I've put into this problem already...!

Many thanks in advance, from a stressed amateur...

This output format is hard to do with just SQL. It might be doable, but I woul;d have to think about it and consult a text. The problem is that either the "GROUP BY" statement or the SELECT DISTINCT" statement condense multiple records into a single record, this is why the count() is off. When I have tried to do this type of thing in the past I have brought up all the records and counted in the script. I am currently trying to brush up on my SQL though, so I will look into it again.

Let me know if this helps.

Jared

[DrBunchman]

Hi,
The Event Table will need further details such as EventDate and Location. Once you've added those you should be able to something like the following:

 SELECT E.EventDate, 
E.EventName,
E.Location, 
COUNT(I.ImageID)
FROM Event E
LEFT JOIN Images I ON I.EventID = E.EventID
LEFT JOIN ImageKeyword IK ON IK.ImageID = I.ImageID
LEFT JOIN Keywords K ON K.KeywordID = IK.KeywordID
LEFT JOIN ImagePeople IP ON IP.ImageID = I.ImageID
LEFT JOIN People Pe ON Pe.PeopleID = IP.PeopleID
LEFT JOIN Photographer Ph ON Ph.PhotographerID = E.PhotographerID
 
WHERE Pe.People = 'Victoria Beckham'
 
GROUP BY E.EventName, E.EventDate, E.Location
 
ORDER BY E.EventDate

I'd test this out in a query analyzer or similar if you have one to check that the count is returning the correct amounts because I haven't tested this on anything. If you need to add any more items to your selection then remember to include them in your group by clause. You can add as many conditions as you like after the WHERE clause as you have done in your code above.

Let me know how it goes or if you need any more help,

Dr B

[marting]

Thanks for your time...

I have actually fixed my main problem (first post) by running this script:

selection = "SELECT P.People, P.PeopleID, Count(IP.PeopleID) AS TotalPeople " 
fromClause = " FROM people AS P, imagepeople AS IP " 
where = " WHERE P.PeopleID = IP.PeopleID " 

If (Request.QueryString("people") & "") <> "" Then 
where = where & " AND P.PeopleID = '"&Request.QueryString("people")&"' " 
End If 

If (Request.QueryString("event") & "") <> "" Then
fromClause = fromClause & ", imageevent AS IE" 
where = where & " AND IE.EventID = '" & Request.QueryString("event") & "' " _ 
& " AND IE.imageID = IP.ImageID "
End If

If (Request.QueryString("keyword") & "") <> "" OR (Request.QueryString("composition") & "") <> "" Then 
fromClause = fromClause & ", keywords AS K, imagekeyword AS IK " 
where = where & " AND IK.ImageID = IP.ImageID AND K.keywordID = IK.keywordID " 
If (Request.QueryString("keyword") & "") <> "" Then 
where = where & " AND K.KeywordID = '" & Request.QueryString("keyword") & "' " 
End If 
If (Request.QueryString("composition") & "") <> "" Then 
where = where & " AND K.Keyword = '" & Request.QueryString("composition") & "' " 
End If 
End If 

SQL = selection & fromClause & where _ 
& " GROUP BY P.People, P.PeopleID " _ 
& " ORDER BY TotalPeople DESC LIMIT 5"

It works fine and returns the correct Count().

I'll write my next problem on a new post, below...

[marting]

Joining multiple tables with lots of queries...

As mentioned in above reply post, I DO need ImagePhotograph er table as I could have 2 or more photographers sharing a set of pictures.

Okay, on to the problem... I have 9 session variables coming from a search menu. Next to each is an example of what it contains.

1) Session("Event" ) - 645
2) Session("People ") - 23
3) Session("Keywor d") - 52
4) Session("Photog rapher") - 9
5) Session("Orient ation") - Vertical
6) Session("Compos ition") - Full Length
7) Session("DateFr om") - 01/01/2007
8) Session("DateTo ") - 01/01/2008
9) Session("Captio n") - blah blah

And now need to find the images that relate to these sessions. I have done the 'Do While Not' section, displaying and paging the results, but I am stuck on the SQL string.

All the info I need is coming from only the Images table but with all the session variables above to query against... I'm very confused with the joins, hence leaving them out!

SELECT ImageID , FeatureID , DateCreated , DateUploaded
FROM Images

WHERE
E.EventID = Session("Event") AND
P.PeopleID = Session("People") AND
K.KeywordID = Session("Keyword") AND
P.PhotographerID = Session("Photographer") AND
I.Orientation = Session("Orientation") AND
K.Keyword = Session("Composition") AND

If (Session("DateFrom") & "") <> "" AND (Session("DateTo") & "") <> "" Then
I.DateCreated BETWEEN Session("DateFrom") AND Session("DateTo") AND
Else
I.DateCreated = Session("DateFrom") AND
End If

I.Caption LIKE Session("Caption") AND

If Session("AdultFilter") = "On" Then
K.Keyword NOT IN ('Naked','Nude','Topless','Naturist','Nudist') AND
End If

GROUP BY I.ImageID, FeatureID, DateCreated, DateUploaded
ORDER BY I.DateCreated DESC, I.DateUploaded DESC

My DB design is:

Images
ImageID
FeatureID
Height
Width
DateCreated
DateUploaded
Orientation

Keywords
KeywordID
Keyword

ImageKeyword
ImageId
KeywordID

People
PeopleID
People

ImagePeople
ImageID
PeopleID

Events
EventID
EventName
EventDate

ImageEvent
ImageID
EventID

Photographers
PhotographerID
FirstName
LastName
Byline

ImagePhotograph er
ImageID
PhotographerID

I hope this makes sense and that someone can help me. I think after this help, I may have understood joins... Yipeee!

Many thanks in advance

[DrBunchman]

I don't think you need an ImageEvent table; you just need an EventID in your image table so each image is tied to a single event.

To do the joins take your first table, in this case Images:

FROM Images I

Then join the rest of your tables by their primary keys like this:

 JOIN ImagePeople IPe ON IPe.ImageID = I.ImageID 
JOIN People Pe ON Pe.PeopleID = IPe.PeopleID
 
JOIN ImagePhotographer IPh ON IPh.ImageID = I.ImageID
JOIN Photopgrapher Ph ON Ph.PhotographerID = IPh.PhotographerID

Do you see how the joins work? If so try and do the rest yourself.

Hope this helps,

Dr B

[marting]

Thanks Dr Bunchman...

I have re-written the messy code/example I gave you earlier but using your suggested method. I have used a mix of ASP/SQL to get the complete SQL string.

Could you check through it and make sure my joins are correct and point out any other possible errors...?

Selection = "SELECT ImageID , FeatureID , DateCreated , DateUploaded "
FromClause = "FROM Images I "

Joins = "JOIN ImageEvent IE ON IE.ImageID = I.ImageID " _
& "JOIN Events E ON E.EventID = IE.EventID " _
& "JOIN ImagePeople IP ON IP.ImageID = I.ImageID " _
& "JOIN People P ON P.PeopleID = IP.PeopleID " _
& "JOIN ImageKeyword IK ON IK.ImageID = I.ImageID " _
& "JOIN Keywords K ON K.KeywordID = IK.KeywordID " _
& "JOIN ImagePhotographer IPh ON IPh.ImageID = I.ImageID " _
& "JOIN Photographers Ph ON Ph.PhotographerID = IPh.PhotographerID "

If Session("AdultFilter") = "On" Then
WhereClause = "WHERE K.Keyword NOT IN ('Naked' , 'Nude' , 'Nudity' , 'Sex' , 'Topless') "
Else
WhereClause = "1=1 "
End If

If (Session("event") & "") <> "" Then
	WhereClause = WhereClause & "AND E.EventID = '"&Session("event")&"' "
End If
If (Session("people") & "") <> "" Then
	WhereClause = WhereClause & "AND P.PeopleID = '"&Session("people")&"' "
End If
If (Session("keyword") & "") <> "" Then
	WhereClause = WhereClause & "AND K.KeywordID = '"&Session("keyword")&"' "
End If
If (Session("event") & "") <> "" Then
	WhereClause = WhereClause & "AND P.PhotographerID = '"&Session("photographer")&"' "
End If
If (Session("orientation") & "") <> "" Then
	WhereClause = WhereClause & "AND I.Orientation = '"&Session("orientation")&"' "
End If
If (Session("composition") & "") <> "" Then
	WhereClause = WhereClause & "AND K.Keyword = '"&Session("composition")&"' "
End If
If (Session("datefrom") & "") <> "" AND (Session("dateto") & "") <> "" Then
	WhereClause = WhereClause & "AND I.DateCreated BETWEEN '"&Session("datefrom")&"' AND '"&Session("dateto")&"' "
End If
If (Session("datefrom") & "") <> "" AND (Session("dateto") & "") = "" Then
	WhereClause = WhereClause & "AND I.DateCreated = '"&Session("datefrom")&"' "
End If
If (Session("caption") & "") <> "" Then
	WhereClause = WhereClause & "AND I.Caption LIKE '"&Session("caption")&"' "
End If

SQL = Selection & FromClause & Joins & WhereClause _
& " GROUP BY I.ImageID, FeatureID, DateCreated, DateUploaded " _ 
& " ORDER BY I.DateCreated DESC, I.DateUploaded DESC;"

Thanks for your help. I much prefer doing the work myself but with assistance. Normally, I use ASP101 but they just give code away with out really explaining or making people learn from their mistakes. After 10 JOIN posts on ASP101, I'm none the wiser! After 2 in this forum, I have learnt a bit. Looks like I'm going to use The Scripts forum in future.

[DrBunchman]

Good work and i'm glad you've found The Scripts useful. I know I have on occasions to numerous to mention! A couple of things:

You should specify which tables your columns come from in your selection string. So:

Selection = "SELECT ImageID , FeatureID , DateCreated , DateUploaded "

Becomes:

Selection = "SELECT I.ImageID , I.FeatureID , I.DateCreated , I.DateUploaded "

Line 16 should become:

WhereClause = "WHERE 1=1 "

otherwise it will error.

I can't see anything else obvious so give it a whirl and let us know how you get on.

Dr B

[marting]

Thanks for pointing out the small errors. Once again, thanks for your time and knowledge.

Okay, this works really nicely apart from one small problem. It's only returning images that has an event. This is a problem, as most of my images in the database have no event.

The script is....

Selection = "SELECT I.ImageID , I.FeatureID , I.DateCreated , I.DateUploaded , I.SmallLoc , I.FileName "
FromClause = "FROM Images I "

Joins = "JOIN ImageEvent IE ON IE.ImageID = I.ImageID " _
& "JOIN Events E ON E.EventID = IE.EventID " _
& "JOIN ImagePeople IP ON IP.ImageID = I.ImageID " _
& "JOIN People P ON P.PeopleID = IP.PeopleID " _
& "JOIN ImageKeyword IK ON IK.ImageID = I.ImageID " _
& "JOIN Keywords K ON K.KeywordID = IK.KeywordID " _
& "JOIN ImagePhotographer IPh ON IPh.ImageID = I.ImageID " _
& "JOIN Photographers Ph ON Ph.PhotographerID = IPh.PhotographerID "

If Session("AdultFilter") = "On" Then
WhereClause = "WHERE K.Keyword NOT IN ('Naked' , 'Nude' , 'Nudity' , 'Sex' , 'Topless') "
Else
WhereClause = "WHERE 1=1 "
End If

If (Session("event") & "") <> "" Then
	WhereClause = WhereClause & "AND E.EventID = "&Session("event")&" "
End If
If (Session("people") & "") <> "" Then
	WhereClause = WhereClause & "AND P.PeopleID = "&Session("people")&" "
End If
If (Session("keyword") & "") <> "" Then
	WhereClause = WhereClause & "AND K.KeywordID = "&Session("keyword")&" "
End If
If (Session("photographer") & "") <> "" Then
	WhereClause = WhereClause & "AND P.PhotographerID = "&Session("photographer")&" "
End If
If (Session("orientation") & "") <> "" Then
	WhereClause = WhereClause & "AND I.Orientation = '"&Session("orientation")&"' "
End If
If (Session("composition") & "") <> "" Then
	WhereClause = WhereClause & "AND K.Keyword = '"&Session("composition")&"' "
End If
If (Session("datefrom") & "") <> "" AND (Session("dateto") & "") <> "" Then
	WhereClause = WhereClause & "AND I.DateCreated BETWEEN '"&Session("datefrom")&"' AND '"&Session("dateto")&"' "
End If
If (Session("datefrom") & "") <> "" AND (Session("dateto") & "") = "" Then
	WhereClause = WhereClause & "AND I.DateCreated = '"&Session("datefrom")&"' "
End If
If (Session("caption") & "") <> "" Then
	WhereClause = WhereClause & "AND I.Caption LIKE '"&Session("caption")&"' "
End If

SQL = Selection & FromClause & Joins & WhereClause _
& " GROUP BY I.ImageID , I.FeatureID , I.DateCreated , I.DateUploaded , I.SmallLoc , I.FileName " _ 
& " ORDER BY I.DateCreated DESC, I.DateUploaded DESC;"

I know the problem lies within the first 2 joins (event), it's only showing images where IE.EventID = I.ImageID but as I have no experience in joining tables just yet, I'm a little confused in how to rectify this...!

Any help would be gratefully appreciated.

[jhardman]

You need an explicit outer join then instead of an inner join. I don't have a book with me at the moment, I'll try to look it up this weekend.

Jared

[DrBunchman]

Use a LEFT JOIN instead of a JOIN on those tables where you wish to return NULL values. e.g

LEFT JOIN ImageEvent IE ON IE.ImageID = I.ImageID

That should do the trick.

And check out a tutorial on joins like this one for a bit more info on why: http://www.w3schools.com/sql/sql_join.asp

Dr B