php - 使用UNION JOIN从多个用户类型中选择项目

我有个问题。我有三张桌子，即tbl_ads, tbl_ps, tbl_dealer

tbl_ads ( ad_id, ad_type, poster_id,  cat,  title, description, images ... , ad_status )

tbl_ps ( ps_id, name , address, city, state )

tbl_dealer ( id, name , address, city, state, pack, bussname )

现在我想根据title，description在tbl_ads中搜索广告，但我想根据city和state搜索广告，这两个广告根据tbl\u广告在不同的表格中
基本上在这里，我想得到广告如果ad_type = 'ps'然后从tbl_ps和if ad_type = 'dealer'然后从tbl_经销商。表ad与ps之间的关系为tbl_ps.ps_id = poster_id，表ad与dealer之间的关系为tbl_dealer.id = poster_id
问题是，我想根据广告title、广告description、city和state搜索广告，并获得仅可能是PS或经销商的TBL广告记录的组合结果
城市和州位于PS和经销商表上
我已经尝试了所有的多重连接、联合以及从堆栈溢出中得到的所有东西。我没有得到我真正想要的，它真的进入了地狱。

最佳答案

也许是这样的事情。

SELECT ad_id,ad_type,poster_id,cat,title,description,images,ad_status,
       IF (ad_type = 'ps', ps.name, dealer.name) as name,
       IF (ad_type = 'ps', ps.address, dealer.address) as address,
       IF (ad_type = 'ps', ps.city, dealer.city) as city,
       IF (ad_type = 'ps', ps.state, dealer.state) as state,
       IF (ad_type = 'ps', NULL, dealer.pack) as pack,
       IF (ad_type = 'ps', NULL, dealer.bussname) as bussname
FROM tbl_ads ads
LEFT JOIN tbl_ps ps ON (ps.ps_id = ads.poster_id)
LEFT JOIN tbl_dealer dealer ON (dealer.id = ads.poster_id)

然后搜索你可以用这样的东西

SELECT ad_id,ad_type,poster_id,cat,title,description,images,ad_status,
       IF (ad_type = 'ps', ps.name, dealer.name) as _name,
       IF (ad_type = 'ps', ps.address, dealer.address) as _address,
       IF (ad_type = 'ps', ps.city, dealer.city) as _city,
       IF (ad_type = 'ps', ps.state, dealer.state) as _state,
       IF (ad_type = 'ps', NULL, dealer.pack) as _pack,
       IF (ad_type = 'ps', NULL, dealer.bussname) as _bussname
FROM tbl_ads ads
LEFT JOIN tbl_ps ps ON (ps.ps_id = ads.poster_id)
LEFT JOIN tbl_dealer dealer ON (dealer.id = ads.poster_id)
WHERE title LIKE '%tit%'
   OR description LIKE '%crip%'
HAVING _city LIKE '%Yorba%'
    OR _state LIKE '%Cali%'

检查这个sqlFiddle
这是一个非常基本的搜索，搜索可能会变得很激烈，比如匹配描述上的标题匹配顺序，以及标题中有多少匹配项等。

关于php - 使用UNION JOIN从多个用户类型中选择项目，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20810739/