Jump to content

Archived

This topic is now archived and is closed to further replies.

mcmuney

Connect Two Tables

Recommended Posts

I'm currently using this code to display recent members who have signed on:
[code]$sel_mem="select * from sc_member where scm_mem_id<>'1' order by scm_lastlogin desc LIMIT 0,2";[/code]

I'd like to modify the above code to show ONLY members with images. To do this, I have to use 'sc_member_images' table, scm_mem_id would be the unique identifier on both tables. So, from results pulled from above code, show ONLY if scm_mem_id exists on table sc_member_images WHERE sci_main=1

I don't know how to incorporate the two into a single SELECT statement and I can't use sub-queries because of current mysql version. Thanks.

Share this post


Link to post
Share on other sites
This list members where a matching id is found in the images table
[code]$sel_mem = "SELECT m.* FROM sc_member m
    INNER JOIN sc_member_images i
    ON m.scm_mem_id = i.scm_mem_id
    WHERE m.scm_mem_id<>'1'
    ORDER BY m.scm_lastlogin DESC
[/code]

Share this post


Link to post
Share on other sites
If the relationship between your images and users table is 1 -> 1 then you can do an INNER JOIN on the tables and only the rows where your "scm_mem_id" values match in both tables will be returned.

Don't forget that as you are dealing with two tables in the 1 query you will have to prefix your table names

This should do it although I haven't checked it.

SELECT * from (sc_member INNER JOIN imagestable ON sc_member.scm_mem_id=imagestable.scm_mem_id) WHERE sc_member.scm_mem_id != '1' ORDER BY sc_member.scm_lastlogin desc LIMIT 0,2

....And barand beat me to it :)
he seems like a knowlegeable chap maybe he can help me with my availability calendar problem ;)

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.