开发者

How will i join this two query in one single query?

I have this first query.

SELECT item.id, item.name, item.description, 
       item.tnURL, item.spec_id, item.item_type, item.sub_category  
FROM item, fb_item_promo 
WHERE fb_item_promo.item_id=item.id;

And the second query.

SELECT item.id, item.name, item.description, 
       item.tnURL, item.spec_id, item.item_type, item.sub_category  
FROM item 
WHERE item.description LIKE '%package%';

I need to combine the first query result plus the second query result. How can i do it on a much faster way?

@Fosco your query seems to work but what if i want to add a search function to all the results on item.name? i tried this one but it still gets all fb_item_promo.item_id results.

New Query:

SELECT item.id, item.name, item.description, item.tnURL,item.spec_id, 
 item.item_type, item.sub_category 
FROM item 
LEFT JOIN 开发者_JS百科fb_item_promo 
ON fb_item_promo.item_id = item.id 
WHERE fb_item_promo.item_id IS NOT NULL 
OR item.description LIKE '%package%' 
AND item.name LIKE '%my item name%'

The result is it search for the right item but still return all the items on fb_item_promo.item_id that is present. How can i run a filter on that result?


It doesn't make sense to include ALL records from the first query, PLUS the records from the second query. The records from the second query will already have been included in the first.

I think this is what you need:

SELECT item.id, item.name, item.description, 
       item.tnURL, item.spec_id, item.item_type, 
       item.sub_category 
FROM item
left JOIN fb_item_promo ON fb_item_promo.item_id=item.id
WHERE fb_item_promo.item_id is not null
  or item.description LIKE '%package%'


SELECT item.id, item.name, item.description, item.tnURL,
item.spec_id, item.item_type, item.sub_category 
FROM 
item, fb_item_promo WHERE fb_item_promo.item_id=item.id;

UNION ALL

SELECT item.id, item.name, item.description, item.tnURL, 
item.spec_id, item.item_type, item.sub_category
FROM 
item WHERE item.description LIKE '%package%';


You can do this in one query without a UNION:

select i.id, 
    i.name, 
    i.description, 
    i.tnURL, 
    i.spec_id, 
    i.item_type, 
    i.sub_category  
from item i
left join fb_item_promo ip on i.id = ip.item_id
where ip.item_id is not null 
    or i.description LIKE '%package%';


you could use a subquery instead of an innerjoin :

SELECT item.id, item.name, item.description, 
   item.tnURL, item.spec_id, item.item_type, item.sub_category  
FROM item
WHERE item.description LIKE '%package%'
AND item.id IN (SELECT item_id FROM fb_item_promo);


select item.id,
       item.name, 
       item.description, 
       item.tnURL, 
       item.spec_id, 
       item.item_type, 
       item.sub_category
       from 
       item 
inner join
       fb_item_promo on item.id = fb_item_promo.item_id
where 
       item.description like '%package%'
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜