Returning data from two tables

blackbox

Hi all,

I have two tables and both tables have a columns with the same name "ImageID".

Table 1: SalesItems has multiple records each with a ImageID
Table 2: Images has multiple record some of which have the same imageID.

What I am trying to do is extract all the data from table1 and all the data from table2 where the ImageID's in table 2 match the ImageId in table 1.

So far I have this:

SELECT 
SaleItems.RecordID,
SaleItems.StockNo,
SaleItems.Description,
SaleItems.Comment,
SaleItems.Size,
SaleItems.Category,
SaleItems.SubCategory,
SaleItems.Position,
SaleItems.NewArrival,
SaleItems.Sold,
SaleItems.ArchivedItem,
SaleItems.Cost,
SaleItems.Value,
SaleItems.WebImagePath,
SaleItems.ImageID,
Images.ImageID,
Images.ImageName
FROM Images
JOIN SaleItems USING (ImageID)
WHERE Category = 5

The issue I have it returns rows for all the data in SalesItems multiple times, one row for every ImageID match.

Is there a way to return one row with all the data from table 1 and table 2.

I hope I have explained this clearly.

many thanks for any help you can provide.

Blackbox

Weedpacket

Which row from table 1 should the row from table 2 be paired with?
What should the other rows from table 1 be pair with if not the corresponding row in table 2?

Or are you asking for the results of two queries: on on table 1 for rows with matching records in table 2, and a second on table 2 for rows with matching records in table 1?

blackbox

Weedpacket;11046357 wrote:
Which row from table 1 should the row from table 2 be paired with?
What should the other rows from table 1 be pair with if not the corresponding row in table 2?

Or are you asking for the results of two queries: on on table 1 for rows with matching records in table 2, and a second on table 2 for rows with matching records in table 1?

Table 1 contains columns (short verison):

StockNo | ImageId | Category
--A1----|---A1----|---5---
--B1----|---B1----|---5---
--C1----|---C1----|---5---

Table 2 contains columns

ImageID | ImageName
--A1----|-image1.png
--A1----|-image2.png
--A1 ---|-image3.png
--B1----|-image1.png
--B1----|-image2.png

My aim is to try and get a row result like

---A1---|---A1---|----5-----|-image1.png-|-image2.png-|-image3.png

Is this possible?

Many thanks for any help

Weedpacket

Well, a relation (table or result set) isn't supposed to have multiple columns with the same name because of the obvious confusion it would cause (besides: how many would it need to have in this case?).

If your DBMS has an array type you could write something like [font=monospace]SELECT StockNo,ImageID,Category,ARRAY(SELECT ImageName FROM...)...)[/font] depending on the precise syntax involved and whether PHP's database driver doesn't mangle the result.

Without an array type it should be possible to make a string by concatenating the results of the inner query using some function - then you can have the fun in the application of parsing it back into an array.

Or use the data as-is: just because something is repeated in the result set it doesn't mean you have to repeat whatever you're planning to do with it. It would be straight forward to read a result set with repeated ImageID rows with different ImageName values and construct an array indexed by ImageID that contains arrays of ImageName values.

NogDog

In MySQL, you could use the GROUP_CONCAT() function, along with an appropriate GROUP BY clause.