Sql Query Linking Two Tables
One doubt about MSSQL. There are two tables in the databases.
Table 1 named Property contains fields PRPT_Id (int), PRPT_Name (varchar), PRPT_Status (bit)
Table 2 named PropertyImages contains fields PIMG_Id (int), PIMG_ImageName (varchar), PRPT_Id (int), PIMG_Status (bit)
These two tables correspond to a one-to-many relationship. This means that each property can have zero, one or more PropertyImages corresponding to it.
Requires request to display
PRPT_Id, PRPT_Name, ImageCount (count of all images matching PRPT_Id, where PIMG_Status is true. O if any images are missing), FirstImageName (if there are n images, the name of the first image in the image table that matches PRPT_Id with PIMG_Status true if no images we fill with space / space). another condition is that PRPT_Status must be true.
Edit note. Both tables have auto-incrementing integers as their primary key. So, first the image name will be the name MIN (PIMG_Id), right?
I want the PIMG_Image name corresponding to MIN (PIMG_ID) in the result set
source to share
Assuming FirstImage means the one with the lowest Id, then this should be at least close enough to check completion:
SELECT
PRPT_Id,
PRPT_Name,
ISNULL (pi1.ImageName, '') AS FirstImageName,
COUNT (1) AS ImageCountFROM Property AS p
LEFT JOIN PropertyImages AS pi
ON p.PRPT_Id = pi.PRPT_IdLEFT JOIN PropertyImage AS pi1
ON p.PRPT_Id = pi1.PRPT_IdLEFT USE PropertyImgage AS pi2
ON p.PRPT_Id = pi2.PRPT_Id
AND pi1.PIMG_Id> pi2.PIMG_IdWHERE PRPT_Status = TRUE
AND pi1.PIMG_Status = TRUE
AND pi2.PIMG_ImageName IS NULL
The double LEFT JOIN ensures that you get the first record of the image in pi1. If the rule "First" is different, then configure this connection accordingly.
It should be as effective as possible. It has no subqueries.
source to share
It seems that you need to write nested queries to display what you want.
If this is the case (I'm not a SQL expert), then I would suggest you start with the innermost query, then you exit until you reach the outer (and final) query.
First you have to get 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
This returns the PRPT_Id of properties that have associated images. However, you won't get any results for properties that don't have associated images.
After that, we will go to the properties table with the previous query. The left join ensures that all properties are restored even if they don't appear in the results of the correct query (that is, even if they don't have 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 my SQL isn't wrong and that this post helps you.
Hello,
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 not sure about my SQL.
the name of the first image in the image table corresponding to PRPT_Id with PIMG_Status true
In this context, you can define "first". The tables are not really ordered, so unless you save your own order, the term should mean "first found" first.
Assuming the above is true (you want to find the image first), then this is the only real tricky part of the query (and the type of thing that worked before). The rest seems pretty straight forward. If I find time tomorrow, I will try to piece something together for you ... but it looks like someone else might be able to provide an answer sooner.
source to share