SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2007
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Concat two fields with the same name (using a join)

    I have a master query in my PHP that's fetching all possible info about a set of book publications. A few types of books have additional classifications, so I broke that data out into separate tables. But I am joining all of those tables to get all info at once.

    I have two tables with a field `current` and both tables are part of my join. The catch is, only one of the two will be populated in each returned record and the other will be blank. In order to get `current` back and not `table1_current` and `table2_current` I thought I could concat the two fields so I would be sure to get the value.

    The problem is, I'm adding ``CONCAT(table1.current, table2.current) as current`` to my query, and `current` is coming back blank. If I select each table's `current` separately (e.g. table1.current as table1_current, table2.current as table2_current) `table1_current` comes back as `yes` while `table2_current_ comes back as blank. So I know the strings are not blank.

    Here is my query:

    Code:
    		SELECT 
    			r.resource,
    			p.resource_id,
    			p.primary_topic_id,
    			p.primary_user_id,
    			p.title,
    			p.in_press,
    			p.pub_year_start,
    			p.pub_year_end,
    			p.cite,
    			p.description,
    			p.status,
    			p.show_dept,
    			p.show_search,
    			p2u.show_homepage,
    			u.user_id,
    			fp.investigators,
    			DATE_FORMAT(fp.start_date, '%m/%d/%Y') as start_date,
    			DATE_FORMAT(fp.end_date, '%m/%d/%Y') as end_date,
    			fp.sponsor,
    			fp.grant_number,
    			fp.funded_amount,
    			sa.first_name,
    			sa.last_name,
    			sa.program,
    			sa.advisors,
    			CONCAT(fa.organization, ea.organization, lr.organization) as organization,
    			CONCAT(fa.division, ea.division, lr.division) as division,
    			CONCAT(ea.role, lr.role) as role,
    			CONCAT(ea.current, lr.current) as current
    		FROM
    			pubs_to_users p2u
    			LEFT JOIN publications p 
    				ON p.pub_id = p2u.pub_id
    			LEFT JOIN users u
    				ON u.user_id = p2u.user_id
    			LEFT JOIN resources r
    				ON r.resource_id=p.resource_id
    			LEFT JOIN faculty_projects fp
    				ON fp.pub_id=p.pub_id
    			LEFT JOIN faculty_awards fa
    				ON fa.pub_id=p.pub_id
    			LEFT JOIN student_awards sa
    				ON sa.pub_id=p.pub_id
    			LEFT JOIN editorial_activities ea
    				ON ea.pub_id=p.pub_id
    			LEFT JOIN leadership_roles lr
    				ON lr.pub_id=p.pub_id
    		WHERE
    			p2u.pub_id=2069
    			AND p2u.pub_id=p.pub_id
    			AND p2u.user_id=u.user_id

  2. #2
    SitePoint Zealot
    Join Date
    Apr 2002
    Location
    Sydney, Australia
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    From ...

    http://dev.mysql.com/doc/refman/5.0/...functions.html

    CONCAT() returns NULL if any argument is NULL.
    Could this be your problem ?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if they are NULLs, and not blanks as you said, and if only one of them will have a value, then you should use COALESCE, not CONCAT

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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