Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Jan 2010
    Location
    Washington
    Posts
    223
    Thanks
    34
    Thanked 0 Times in 0 Posts

    Question Need help with union

    I am trying to create a UNION so that i can use the same column name for both sets of tables in 2 separate databases that are setup exactly the same. db2 is a continuance of db1 and I think I need to use union but I have never used it before and I am tired of my server locking up with me messing around with it so I thought i would ask for help. Can someone show me how to create a union with this query?

    Code:
    $CONF['mysql_query'] = 'SELECT db1.l.link_id AS id, db1.l.title AS title, db2.r.link_id AS id, db2.r.title AS title FROM db1.links AS l LEFT OUTER JOIN db2.links AS r ON (r.link_id = l.link_id) WHERE l.link_id IN ($ids)';
    I need to use the same column names such as title as title etc for both tables to make things display properly using $row['title'] etc.

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    A union is simply two SELECT queries glued together with the word "UNION" (or "UNION ALL" if you want to see duplicates).

    Code:
    SELECT 
    db1.l.link_id AS id
    , db1.l.title AS title
    FROM db1.links AS l
    WHERE db1.l.link_id IN ($ids)
    UNION
    SELECT db2.links AS r
    db2.r.link_id AS id
    , db2.r.title AS title
    WHERE db2.r.link_id IN ($ids)

  • #3
    Regular Coder
    Join Date
    Jan 2010
    Location
    Washington
    Posts
    223
    Thanks
    34
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fumigator View Post
    A union is simply two SELECT queries glued together with the word "UNION" (or "UNION ALL" if you want to see duplicates).

    Code:
    SELECT 
    db1.l.link_id AS id
    , db1.l.title AS title
    FROM db1.links AS l
    WHERE db1.l.link_id IN ($ids)
    UNION
    SELECT db2.links AS r
    db2.r.link_id AS id
    , db2.r.title AS title
    WHERE db2.r.link_id IN ($ids)
    Thanks for the reply. I tried that and get a syntax error "'db2.r.link_id AS id, db2.r.title AS title WHERE db2.r.link_id IN (238360,8' at line 7".
    I am using sphinx and trying to pull doc ids and such from mysql. I had this working perfectly until i had to recently create a new db to lessen some load and now search results are continued in db2 and there is no chance of duplicates but I have the task of creating a new sql query that works properly.

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Oops, I managed to @#$ that up, lol. Copy/paste error. Try this.

    Code:
    SELECT 
    db1.l.link_id AS id
    , db1.l.title AS title
    FROM db1.links AS l
    WHERE db1.l.link_id IN ($ids)
    UNION
    SELECT
    db2.r.link_id AS id
    , db2.r.title AS title
    FROM db2.links AS r
    WHERE db2.r.link_id IN ($ids)

  • #5
    Regular Coder
    Join Date
    Jan 2010
    Location
    Washington
    Posts
    223
    Thanks
    34
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fumigator View Post
    Oops, I managed to @#$ that up, lol. Copy/paste error. Try this.

    Code:
    SELECT 
    db1.l.link_id AS id
    , db1.l.title AS title
    FROM db1.links AS l
    WHERE db1.l.link_id IN ($ids)
    UNION
    SELECT
    db2.r.link_id AS id
    , db2.r.title AS title
    FROM db2.links AS r
    WHERE db2.r.link_id IN ($ids)
    syntax error again "syntax error near ') UNION SELECT db2.r.link_id AS id , db2.r.title AS title FROM db2.links A' at line 5". This query is starting to seem impossible. I have been trying to figure this out for hours and I always run into something.

  • #6
    Regular Coder
    Join Date
    Jan 2010
    Location
    Washington
    Posts
    223
    Thanks
    34
    Thanked 0 Times in 0 Posts
    I am thinking I can only use 1 WHERE instead of 2 but I need to make sure that i grab a list of ids from both databases so I am pretty stumped.

  • #7
    Regular Coder
    Join Date
    Jan 2010
    Location
    Washington
    Posts
    223
    Thanks
    34
    Thanked 0 Times in 0 Posts
    After some messing around I got it going. Thanks for your time


  •  

    Posting Permissions

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