Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2002
    Posts
    11

    Unanswered: getting multiple field values from join

    I am running into a situation where I need to do a join on a couple of tables and combine them into a single result. My problem is that I need multiple id#'s from a table but since the join creates a single resultset, I can only get 1 of the 2 id numbers.

    Here's an example of my query - I need to get the values of s.scid of either or both of s.contactid=j.userid AND s.jobid=j.jobid. Currently, the value of s.scid is only a single value when I need 2.

    Code:
    SELECT j.jobid, u.company_name, s.scid AS bookmarkid, s.jobid AS bookmarkjob, s.contactid AS bookmarkcontact
    FROM joblisting j, users u
    LEFT OUTER JOIN savedcontact s ON (s.userid=8 AND (s.contactid=j.userid OR s.jobid=j.jobid))
    WHERE j.userid=u.userid
    GROUP BY j.jobid
    Here are the trimmed down table structures:

    CREATE TABLE savedcontact (
    scid int(10) unsigned NOT NULL auto_increment,
    userid int(10) unsigned NOT NULL default '0',
    contactid int(10) unsigned NOT NULL default '0',
    jobid int(10) unsigned NOT NULL default '0'
    )

    CREATE TABLE joblisting (
    jobid int(10) unsigned NOT NULL auto_increment,
    userid int(10) unsigned NOT NULL default '0'
    )

    CREATE TABLE users (
    userid int(10) unsigned NOT NULL auto_increment
    )

    I realize this sounds pretty confusing, so let me know if I should clarify further.

    Thanks for any input!

  2. #2
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80

    Some sample data would help

    It looks like it will work, assuming the s.scids you're looking for are in two separate rows in the savedcontact table.

    Perhaps it would help if you posted a bit of data as well.

  3. #3
    Join Date
    Jan 2002
    Posts
    11
    Yes, the savedcontacts are in 2 separate rows however the way it is now, it combines those 2 rows into a single result so I lose part of the data.

    Running the query results in the following: (sorry, don't have the command prompt version)

    jobid - 2
    company_name - test company2
    bookmarkid - 2
    bookmarkjob - 0
    bookmarkcontact - 9

    Basically, I need 2 results from "bookmarkid" and "bookmarkjob". "Bookmarkcontact" and "company_name" can remain the same as the contact is always related to the company.

    The scenerio behind what I'm after is that a user can bookmark either the the result from joblistings table or from the users table... The query should display results whether or not the user has done so.

    Thanks again or your time

  4. #4
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    Could you post the relevant rows from the source tables?

  5. #5
    Join Date
    Jan 2002
    Posts
    11
    No probs...

    From savedcontacts:
    +--------+------------+-------------+-----------------+
    | userid | bookmarkid | bookmarkjob | bookmarkcontact |
    +--------+------------+-------------+-----------------+
    | 8 | 1 | 2 | 9 |
    | 8 | 2 | 0 | 9 |
    +--------+------------+-------------+-----------------+

    From users:
    +--------+--------------+
    | userid | company_name |
    +--------+--------------+
    | 9 | test company |
    +--------+--------------+


    From joblisting:
    +-------+--------+
    | jobid | userid |
    +-------+--------+
    | 2 | 9 |
    +-------+--------+


    So my desired resultset would include the data from the users & joblisting table which would be combined into a single result. But with that result, I need the 2 "bookmarkids" from savedcontact as they match the userid of the person doing the search. So the scenerio above is user 8 has bookmarked both the company AND the joblisting of user 9.

    Hope that helps out Thanks again!!

    Jason

  6. #6
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    Have you changed your column names since the CREATE TABLE statement, or are bookmarkid, bookmarkjob, and bookmarkcontact additional columns?

  7. #7
    Join Date
    Jan 2002
    Posts
    11
    Oh ya, I should have mentioned... I aliased the resultset according to how I did it in the original query in my first post. The column names are still what is shown in the CREATE TABLE part of post. The aliases aren't really necessary.

    Sorry about that.
    Jason Dulberg

  8. #8
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    OK.

    There's one other thing that doesn't make sense to me. How can you have savedcontact.scid as autoincrement without also having it as a primary key? If it is actually a primary key, how do you have two rows with scid=8?

  9. #9
    Join Date
    Jan 2002
    Posts
    11
    scid is a primary key, autoincrement. I just took that stuff out of the CREATE TABLE stuff to make it shorter in the posting...

    scid has the value of 1 or 2 above - both of which should show up in the results.

    Here is the full structure:

    CREATE TABLE savedcontact (
    scid int(10) unsigned NOT NULL auto_increment,
    userid int(10) unsigned NOT NULL default '0',
    contactid int(10) unsigned NOT NULL default '0',
    jobid int(10) unsigned NOT NULL default '0',
    PRIMARY KEY (scid),
    KEY altids (userid,contactid,jobid),
    ) TYPE=MyISAM;

    The only thing that I have thought of so far is to somehow create an alias for scid depending on whether or not "jobid" is populated as it won't always be. Contactid will always be populated.
    Jason Dulberg

  10. #10
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80

    Try this...

    How about:

    SELECT j.jobid,
    u.userid,
    s.scid AS bookmarkid,
    s.jobid AS bookmarkjob,
    s.contactid AS bookmarkcontact
    FROM savedcontact s
    LEFT OUTER JOIN joblisting j ON s.jobid = j.jobid
    LEFT OUTER JOIN users u ON s.contactid = u.userid
    WHERE s.userid = 8
    GROUP BY j.jobid

    ?

  11. #11
    Join Date
    Jan 2002
    Posts
    11
    ok, with that query it is getting the appropriate data however there is one problem, its in 2 results. I need to combine them into a single result for display purposes. Is it possible to use mysql's IF structure to create an alias if 2 bookmarkid's are present?

    +-------+--------+------------+-------------+-----------------+
    | jobid | userid | bookmarkid | bookmarkjob | bookmarkcontact |
    +-------+--------+------------+-------------+-----------------+
    | NULL | 9 | 2 | 0 | 9 |
    | 2 | 9 | 1 | 2 | 9 |
    +-------+--------+------------+-------------+-----------------+


    Thanks
    Jason Dulberg

  12. #12
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    What do you want the output to look like?

  13. #13
    Join Date
    Jan 2002
    Posts
    11
    Basically I'd like to take the bookmarkid from the result with NULL jobid and place it into the NOT NULL jobid result:

    +-------+--------+------------+-------------+-----------------+
    | jobid | userid | bookmarkid | bookmarkjob | bookmarkcontact |
    +-------+--------+------------+-------------+-----------------+
    | NULL | 9 | 2 | 0 | 9 |
    | 2 | 9 | 1 | 2 | 9 |
    +-------+--------+------------+-------------+-----------------+

    I created the "2ndbookmarkid" alias below as an example result below:

    +-------+--------+------------+-------------+-----------------+
    | jobid | userid | bookmarkid | 2ndbookmarkid | bookmarkjob | bookmarkcontact |
    +-------+--------+------------+-------------+-----------------+
    | 2 | 9 | 1 | 2 | 2 | 9 |
    +-------+--------+------------+-------------+-----------------+

    Sorry, I should have been more explanatory from be beginning. Thank you again for your ongoing help!
    Jason Dulberg

  14. #14
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    This might do it...

    SELECT j.jobid,
    u.userid,
    s.scid AS bookmarkid,
    s2.scid AS 2ndbookmarkid,
    s.jobid AS bookmarkjob,
    s.contactid AS bookmarkcontact
    FROM savedcontact s
    LEFT OUTER JOIN joblisting j ON s.jobid = j.jobid
    LEFT OUTER JOIN users u ON s.contactid = u.userid
    LEFT OUTER JOIN savedcontact s2 ON s2.jobid = 0
    WHERE s.userid = 8 AND j.jobid IS NOT NULL
    GROUP BY j.jobid

  15. #15
    Join Date
    Jan 2002
    Posts
    11
    Thanks for all of your help... you sent me in the right direction. I combined what you suggested and came up with the following which now works great.

    SELECT j.jobid, u.company_name, s.scid AS bookmarkid, s2.scid AS 2ndbookmarkid, s.contactid, s2.contactid AS 2ndcontactid, s.jobid AS bookmarkjobid
    FROM users u, joblisting j
    LEFT OUTER JOIN savedcontact s ON (s.jobid=j.jobid AND s.userid=8)
    LEFT OUTER JOIN savedcontact s2 ON (s2.jobid=0 AND s2.contactid=u.userid AND s2.userid=8)
    WHERE j.jobid IS NOT NULL AND u.userid=j.userid
    GROUP BY j.jobid

    I changed the aliases around a little bit to make it easier to understand what's what.

    Thanks again!!
    Jason Dulberg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •