Sql Query Linking Two Tables

Question

Hi there,

One doubt in MSSQL. There are two tables in a databases.

Table 1 named Property contain fields PRPT_Id(int),PRPT_Name(varchar), PRPT_Status(bit)

Table 2 named PropertyImages contain fields PIMG_Id(int),PIMG_ImageName(varchar),PRPT_Id(int),PIMG_Status(bit)

These two tables follow a one-to-many relationship. That means the each Property can have zero, one or more PropertyImages corresponding to it.

What is required is a query to display

PRPT_Id, PRPT_Name, ImageCount(Count of all images corresponding to a PRPT_Id where PIMG_Status is true. o if there arent any images), FirstImageName(if there are n images, the name of the first image in the image table corresponding to the PRPT_Id with PIMG_Status true. if there aren't any images we fill that with whitespace/blank) . another condition is that PRPT_Status should be true.

Edit Note - Both the tables are having autoincremented integers as primary key. So first Image name will be the name with MIN(PIMG_Id), isn't that so?

I want the PIMG_ImageName corresponding to the MIN(PIMG_ID) in the resultset

Answer 1

It seems that you have to write nested queries to display what you want.

If that's the case (I'm no SQL expert), I'd recommend you to start with the innermost query, then you go out until you reach the outermost (and final) query.

First, you have to retrieve the PIMGs and group them by PRPT.

SELECT PRPT_Id, COUNT(PIMG_Id) AS PRPT_ImageCount, MIN(PIMG_Id) AS PRPT_MinImage
FROM PropertyImages
GROUP BY PRPT_Id

That retrieves the PRPT_Id's of the properties that do have associated images. However, you don't get any results for the properties that don't have any associated images.

After that, we will left join the Properties table with the previous query. The left join ensures that all the Properties will be retrieved, even if they don't appear in the results of the right query (that is, even if they don't have any associated images).

SELECT Properties.*, PRPT_ImageCount, PRPT_MinImage
FROM Properties LEFT JOIN (
SELECT PRPT_Id, COUNT(PIMG_Id) AS PRPT_ImageCount, MIN(PIMG_Id) AS PRPT_MinImage
FROM PropertyImages
GROUP BY PRPT_Id ) Temp ON ( Properties.PRPT_Id = Temp.PRPT_Id )

I hope that my SQL isn't wrong and that this post helps you.

Regards,

---

Edit:

SELECT Properties.*,
       PRPT_ImageCount,
       PRPT_MinImage,
       PIMG_ImageName
FROM ( Properties LEFT JOIN
       ( SELECT PRPT_Id,
                COUNT(PIMG_Id) AS PRPT_ImageCount,
                MIN(PIMG_Id) AS PRPT_MinImage
         FROM PropertyImages
         GROUP BY PRPT_Id ) Temp1
       ON ( Properties.PRPT_Id = Temp1.PRPT_Id ) ) Temp2 LEFT JOIN
     PropertyImages ON ( PropertyImages.PIMG_Id = Temp2.PRPT_MinImage )

Now, I'm really unsure of my SQL.

Answer 2

the name of the first image in the image table corresponding to the PRPT_Id with PIMG_Status true

You may want to define "first" in this context. Tables aren't really ordered so, unless you keep your own ordering, the term first needs to mean "first one found".

Assuming the above is true (you want first found image), then that's the only real hard part of the query (and the type of thing that's tripped me up before). The rest of it seems fairly straight forward. If I can find the time tomorrow, I'll try to put something together for you... but it seems likely someone else will be able to provide the answer before then.

Answer 3

Assuming that FirstImage means the one with the lowest Id, then this should be at least close enough to test to completion:

SELECT
PRPT_Id,
PRPT_Name,
ISNULL(pi1.ImageName, '') AS FirstImageName,
COUNT(1) AS ImageCount

FROM Property AS p

LEFT JOIN PropertyImages AS pi
ON p.PRPT_Id = pi.PRPT_Id

LEFT JOIN PropertyImage AS pi1
ON p.PRPT_Id = pi1.PRPT_Id

LEFT JOIN PropertyImgage AS pi2
ON p.PRPT_Id = pi2.PRPT_Id
AND pi1.PIMG_Id > pi2.PIMG_Id

WHERE PRPT_Status = TRUE
AND pi1.PIMG_Status = TRUE
AND pi2.PIMG_ImageName IS NULL

The double LEFT JOIN assures that you get the first image record in pi1. If the "First" rule is different, then adjust this join accordingly.

This should be about as efficient as possible. It has no subqueries.