Joining search queries

Newbie question.

Its really an SQL question, but have had no reply from the SQL
newsgroup.

I want to search the same table for two different criteria, and then
join the search results together into one new list. See below.


$result = @mysql_query ("SELECT company, priority FROM table1 WHERE
priority ='high' "); UNION; @mysql_query ("SELECT company, priority
FROM table1 WHERE priority ='low' ");

while ($row = mysql_fetch_array($result))

{

$company = $row['company']; echo $company;
$priority = $row['priority']; echo $priority;

}
?>

BUT it only returns the first search result, even though there are
records that relate to the second.

What am I doing wrong? Also can UNION be used to combine more than two
searches?

Many thanks

Alec

Re: Joining search queries

Alec wrote:
> Newbie question.
>
> Its really an SQL question, but have had no reply from the SQL
> newsgroup.
>
> I want to search the same table for two different criteria, and then
> join the search results together into one new list. See below.
>
> >
> $result = @mysql_query ("SELECT company, priority FROM table1 WHERE
> priority ='high' "); UNION; @mysql_query ("SELECT company, priority
> FROM table1 WHERE priority ='low' ");
>
> while ($row = mysql_fetch_array($result))
>
> {
>
> $company = $row['company']; echo $company;
> $priority = $row['priority']; echo $priority;
>
> }
> ?>
>
> BUT it only returns the first search result, even though there are
> records that relate to the second.
>
> What am I doing wrong? Also can UNION be used to combine more than two
> searches?
>
> Many thanks
>
> Alec
>

Where did you ask? I didn't see it in comp.databases.mysql, which is
where you should be asking.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: Joining search queries

Alec wrote:
> $result = @mysql_query ("SELECT company, priority FROM table1 WHERE
> priority ='high' "); UNION; @mysql_query ("SELECT company, priority
> FROM table1 WHERE priority ='low' ");

First thing to remember: post readable code.

$result = @mysql_query("SELECT company, priority FROM table1
WHERE priority ='high' ");
UNION;
@mysql_query ("SELECT company, priority FROM table1
WHERE priority ='low' ");

Now it is quite visible what is happening. You only assign the
first query to $result.

Solution if the only priorities that exist are "high" and "low":

SELECT company, priority FROM table1;

Other solution:

SELECT company, priority FROM table 1
WHERE priority in ('high', 'low');

The less-than-optimal, but correct UNION-solution:

SELECT company, priority FROM table 1
WHERE priority ='high')
UNION
SELECT company, priority FROM table 1
WHERE priority = 'low');

You REALLY should read some SQL-Tutorial, because this is
really BASIC knowledge about SQL.