[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