...

View Full Version : Need help with union



cosmicsea
07-01-2011, 05:29 PM
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?


$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.

Fumigator
07-01-2011, 05:52 PM
A union is simply two SELECT queries glued together with the word "UNION" (or "UNION ALL" if you want to see duplicates).



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)

cosmicsea
07-01-2011, 06:09 PM
A union is simply two SELECT queries glued together with the word "UNION" (or "UNION ALL" if you want to see duplicates).



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.

Fumigator
07-01-2011, 06:17 PM
Oops, I managed to @#$ that up, lol. Copy/paste error. Try this.



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)

cosmicsea
07-01-2011, 06:26 PM
Oops, I managed to @#$ that up, lol. Copy/paste error. Try this.



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.

cosmicsea
07-01-2011, 06:30 PM
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.

cosmicsea
07-01-2011, 07:17 PM
After some messing around I got it going. Thanks for your time :thumbsup:



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum