Searching Multiple Tables and Returning Unique Results - Databases forum at WebmasterWorld - WebmasterWorld

Forum Moderators: open

Message Too Old, No Replies

Searching Multiple Tables and Returning Unique Results

sql query spanning multiple tables to return unique results

ccDan

6:05 pm on Jun 13, 2010 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

I am trying to do a search across two tables and not having much success.

Here are essentially what the tables are like:

Table 1: animals
Fields: name, description, imageURL

Table 2: states
Fields: state, name

In table 2, you would have entries like this:
WA, cow
WA, bird
WA, turtle
OR, bird
OR, snake
CA, turtle
CA, snake

What I want to do is select two states and list the animals that are in those two states, but listing each animal only once.

My latest attempt is this:

SELECT *
FROM animals,states
WHERE states.area = 'WA' OR states.area = 'OR'
AND animals.name = states.name
ORDER BY animals.name ASC

What I keep getting, through my various attempts, is a list of all animals in all states, including animals that are not listed in any states.

I've tried several variations on the search string, and keep ending up getting nowhere, which is a real head-scratcher because, believe it or not, I usually don't have this much of a problem setting up search strings.

I'm hoping I'm just forgetting something really, really simple. Any ideas?

Thanks!

rocknbil

7:49 pm on Jun 13, 2010 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Couple things. You always have to be cautious with or.

WHERE states.area = 'WA' OR states.area = 'OR'
AND animals.name = states.name

So it will match on **any** record where states area='WA'. Any. And since you're joining on a second table, it will output [the number of records in states] for each 'WA' entry in animals.

match in wa ->
-> join table animals,
-> record 1 in animals . . .
-> record 2 in animals . . .
-> record 3 in animals . . .
another match in wa ->
-> join table animals,
-> record 1 in animals . . .
-> record 2 in animals . . .
-> record 3 in animals . . .

It's not tied to the animals table by name. If there are no states records for 'WA', it will **only** match on area records for 'OR' where it joins on name. Which is the desired effect. So bracket them,

WHERE (states.area = 'WA' OR states.area = 'OR')
AND animals.name = states.name

and that should fix it.

You are joining on state name, and since you're using the state name in output, I guess it doesn't matter - you can just reference $row['name'], but note there are two fields named 'name' when you do select * which can sometimes create problems. current discussion [webmasterworld.com].

but listing each animal only once.

You may need some programming logic here. The previous will return

eagle OR
eagle WA

as it's going to select the animal name for each record. If you want
eagle OR
------WA

you may have to do something like


$currentname=null; 
while ($row=mysql_fetch_array($result)) { 
 $animal = $row['name']; // just gets the first 
 $state = $row['state']; 
 $currentname=($currentname==$animal)?'&nbsp;'$animal; 
 echo "<tr><td>$currentname</td><td>$state</td></tr>"; 
}

The ternary will only display the name if it's changed.

DWarp9

9:39 pm on Jun 13, 2010 (gmt 0)

10+ Year Member

You might want to take a look at the "In" keyword.
That way your query would be something like this:

Select name, description, imageURL
From animals Where name In
(Select name From states Where state = 'WA' Or state = 'OR')
Order By name Asc

The inner query generates a list of animals in the 2 states and the outer query selects all the animals whose name appear in that list.
I hope that was what you were trying to do. :)
Let me know if it works.

Edit: Formatting and explanation.

ccDan

11:24 pm on Jun 13, 2010 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

rocknbil wrote:

It's not tied to the animals table by name. If there are no states records for 'WA', it will **only** match on area records for 'OR' where it joins on name. Which is the desired effect. So bracket them,

WHERE (states.area = 'WA' OR states.area = 'OR')
AND animals.name = states.name

and that should fix it.

Bingo! Yep, that fixed that part of the equation. Thanks!

DWarp9 wrote:

Let me know if it works.

Yes! Thanks much!

I did it like this:

Select *
From animals Where name In 
(Select name From states Where state = 'WA' Or state = 'OR') 
Order By name Asc

And that did the trick.

Thanks all!