A federated query problem, if the first left JOIN does not conform to the record of use under a left-CodePudding

Such as

Select * from HouseType HT
If the following leftjon query record
LEFT the JOIN (SELECT img, img_t img_s, itemID, imgType from ClientHouseImg where imgType='TypeImg') ST on (HT) HouseTypeID=st. itemID)
Otherwise,
LEFT the JOIN (SELECT img, img_t img_s, itemID, imgType from ClientHouseImg where imgType='ybj') SH on (SH) itemID=HouseID)

Meaning, the two left the JOIN but can only choose one, or in front of this without a record, with a second left JOIN somehow, please tall person to give directions, thank you very much

CodePudding user response:

Write a if decide first

CodePudding user response:

 
Select * 
The from 
(select *, dense_rank () over (partition by st. itemID order by rn) as rn_new from HouseType HT 
LEFT the JOIN (SELECT img, img_t img_s, itemID, imgType, 
Case the when imgType='TypeImg' then one else 2 end as an rn from ClientHouseImg where imgType in (' TypeImg ', 'ybj)) ST on (HT) HouseTypeID=st. itemID)) as A 
Where A.r n_new=1

CodePudding user response:

refer to the second floor RINK_1 response:

 
Select * 
The from 
(select *, dense_rank () over (partition by st. itemID order by rn) as rn_new from HouseType HT 
LEFT the JOIN (SELECT img, img_t img_s, itemID, imgType, 
Case the when imgType='TypeImg' then one else 2 end as an rn from ClientHouseImg where imgType in (' TypeImg ', 'ybj)) ST on (HT) HouseTypeID=st. itemID)) as A 
Where A.r n_new=1

thank you very much, as if can realize my demand, just don't understand the case when imgType='TypeImg' then one else 2... What is the meaning of these statements, why choose 1, no. 2 that 1 and 2 represent what meaning, can help?

CodePudding user response:

reference goodgame365 reply: 3/f

Quote: refer to the second floor RINK_1 response:

Select *
The from
(select *, dense_rank () over (partition by st. itemID order by rn) as rn_new from HouseType HT
LEFT the JOIN (SELECT img, img_t img_s, itemID, imgType,
Case the when imgType='TypeImg' then one else 2 end as an rn from ClientHouseImg where imgType in (' TypeImg ', 'ybj)) ST on (HT) HouseTypeID=st. itemID)) as A
Where A.r n_new=1

thank you very much, as if can realize my demand, just don't understand the case when imgType='TypeImg' then one else 2... What is the meaning of these statements, why choose 1, no. 2 that 1 and 2 represent what meaning, can help?

This field is equivalent to a priority flag, 1 above 2,

For an ItemID, if have the record of 1 and 2 at the same time, it is 1, otherwise it is only 2, the records of this is through the dense_rank () over (partition by st. ItemID order by rn) implemented as rn_new and rn_new=1.