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%'
精彩评论