union selects

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

union selects

Post 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?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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;
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

i think this might be because of duplicates in my database though
maybe a select distinct would work?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Post Reply