Too many results returned once I add another table
Currently on my results page when I leave the search fields blank it returns every row in my LINE table which it should. Right now there are only three rows and it returns all three. The search works for the other fields so there is no problem here.
Now, I have a second table of pictures that belong to the LINE table connected by a LINE_ID. There could be many pictures for each LINE so I went this route instead of just adding a picture column in the LINE table. Make sense?
My problem is that when I add the table and column to my query, instead of returning only three results with the pictures linked, I get 15 results returned. I have 5 rows currently in my PICTURES table, so it returns 5 rows for every LINE returned. Likewise, if I narrow the search so only one result should return. I get 5 results using the second query while using the original query the one proper result displays.
--------------------------
Here is the original query that works correctly but doesn't contain the table/column needed to display my images. This returns all results as I would expect.
SELECT Line_Id, Line_Name, Line_Nickname, Start_Decade, End_Decade, Manufacturer, Size, Cat_1, Cat_2, Cat_3, Approved, Locked, Added_By, Line_Text
FROM figure_lines
ORDER BY Line_Name DESC
--------------------------
--------------------------
Here is my modified query that gives my recordset the binding I need for the pictures. This returns all the results the above query does plus everything that has a Filename. Which I get, but I do not know how to go about being able to get the bindings for the pictures another way. I've tried messing with the WHERE statement and variables a bit to no avail.
SELECT figure_lines.Line_Id, Line_Name, Line_Nickname, Start_Decade, End_Decade, Manufacturer, Size, Cat_1, Cat_2, Cat_3, figure_lines.Approved, Locked, figure_lines.Added_By, Line_Text, picture.Filename
FROM figure_lines, picture
ORDER BY Line_Name DESC
--------------------------
I'd also like to be able to only have the picture row returned that has the thumbnail column marked. All suggestions are welcome. Thanks in advance.