Page 2 of 2

Posted: Tue Dec 26, 2006 12:05 pm
by volka
If you do not need anything from table 2 then it's a "simple" query without any JOIN or UNION?

Posted: Tue Dec 26, 2006 12:10 pm
by paul_20k
hi

no..I mean I don't need anything else except that common column name value from table 2. I need that column name value and all other values from table 1 with some different condition in the WHERE clause.

thanks

Posted: Tue Dec 26, 2006 12:16 pm
by volka

Code: Select all

( query1 ) UNION ( query2 )
You have two (more or less) completely separate querries. As long as the number of fields in the result set match you can do (more or less) anything you like with query1 and query2.

Posted: Tue Dec 26, 2006 1:36 pm
by paul_20k
Hi Volka

I tried to run 2 queriesseparately, they work fine individually and I can retrieve more column values from table1 also but as soon as I use UNION with these 2 queries, it gives me error like

Code: Select all

"Error 1222 The used SELECT statements have different number of columns"

I am trying these queries as

Code: Select all

SELECT Isnull(table2.commonColumnName) AS test ,table1.commonColumnName, table1.otherColumnName   FROM table1 LEFT JOIN table2 ON table1.commonColumnName=table2.commonColumnName
UNION
SELECT Isnull(table1.commonColumnName),table2.commonColumnName FROM table2 LEFT JOIN  table1 ON table2.commonColumnName=table2.commonColumnName 
		WHERE ISNULL(table1.commonColumnName)
with this query1(not query2), I am trying to use the WHERE CLAUSE as

Code: Select all

WHERE 3rdColumnName='$find'  AND 4thColumnName LIKE '%$field%'
I know its a complex query and without your help, I can't make it working. I tried to explain my best, hope I will get some reply soon.

Thanks

Posted: Tue Dec 26, 2006 1:45 pm
by volka
There was only one condition
volka wrote:As long as the number of fields in the result set match you can do (more or less) anything you like with query1 and query2.
query #1 selects three fields, query #2 only two, the number of columns do not match.
Select a third field/value in you second query, e.g. NULL.

Code: Select all

SELECT Isnull(table1.commonColumnName),table2.commonColumnName,null FROM table2

Posted: Tue Dec 26, 2006 6:34 pm
by paul_20k
Thanks so much Volka...everything is working now...:)