Page 1 of 1
union selects
Posted: Thu Jan 19, 2006 9:08 pm
by s.dot
Using MySQL 4.1
When I use a union query to select friend relationships in my friends table, such as this query:
Code: Select all
$sql_text = "
SELECT
`whoadded` AS user
FROM
`friends`
WHERE
`friendname` = '$u'
UNION SELECT
`friendname` AS user
FROM
`friends`
WHERE
`whoadded` = '$u'
";
I'm having trouble figuring out how to order the results by the id field, which is always unique. If I throw an ORDER BY id DESC onto the end of the query, MySQL gives me the error 'wrong usage of limit claus'
How can I order unions?
Posted: Thu Jan 19, 2006 10:37 pm
by josh
http://dev.mysql.com/doc/refman/5.0/en/union.html
read about 25-35 lines down
Code: Select all
If you want to use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one. The following example uses both clauses:
(SELECT a FROM tbl_name WHERE a=10 AND B=1)
UNION
(SELECT a FROM tbl_name WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;
Posted: Fri Jan 20, 2006 1:18 am
by s.dot
ok, a few problems
Code: Select all
$sql_text = "
(SELECT
`id` AS ids,
`whoadded` AS user
FROM
`friends`
WHERE
`friendname` = '$u')
UNION
(SELECT
`id` AS ids,
`friendname` AS user
FROM
`friends`
WHERE
`whoadded` = '$u')
ORDER BY
`ids`
ASC
";
This shows every result twice.
if I just select id, without AS ids, it still shows every result twice.
The column names used in the first SELECT statement are used as the column names for the results returned.
If I don't select the id, it says 'unknown column id in field list'... so that makes sense.
Posted: Fri Jan 20, 2006 1:39 am
by s.dot
i think this might be because of duplicates in my database though
maybe a select distinct would work?