[thelist] Sql join problem
Brian Cummiskey
Brian at hondaswap.com
Wed Dec 8 22:21:14 CST 2004
partyarmy wrote:
>Not sure what i'm doing wrong with my join in mysql.
>
>SELECT profile.member_id, members.member_id FROM members JOIN profile
>WHERE members.username LIKE '%party%' OR members.email_address LIKE
>'%party%' AND (profile.category_id = '1' AND profile.content = 'blah')
>LIMIT 0, 12
>
>the problem is if there is less than 12 results i get the result set
>repeated. is there not a way to select from two different tables and
>merge the results in mysql. does someone have an easy tutorial that i
>could follow. i tried the manual, but it's too confusing for me.
>
>thanks, codi lechasseur
>
>
try this instead:
SELECT p.member_id, m.member_id
FROM profile p
LEFT JOIN members m on m.member_id = p.member_id
WHERE m.username LIKE '%party%' OR m.email_address LIKE '%party%'
AND (p.category_id = '1' AND p.content = 'blah')
LIMIT 0,12
you mention merging- the only way to do that is by use of a temporary table
Depending on the sql server (MS, MySQL, etc) the create statements are
different.
but basically, you'd:
create table #blah (member_id varchar(50))
then,
insert into #blah
select member_id from members
insert into #blah
select member_id from profile
where member_id not in (select member_id from #blah)
then, select * from blah.
HTH
More information about the thelist
mailing list