homepage Welcome to WebmasterWorld Guest from 54.145.252.85
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Searching Multiple Tables and Returning Unique Results
sql query spanning multiple tables to return unique results
ccDan

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4151996 posted 6:05 pm on Jun 13, 2010 (gmt 0)

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

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4151996 posted 7:49 pm on Jun 13, 2010 (gmt 0)

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)?' '$animal;
echo "<tr><td>$currentname</td><td>$state</td></tr>";
}


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

DWarp9

5+ Year Member



 
Msg#: 4151996 posted 9:39 pm on Jun 13, 2010 (gmt 0)

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

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4151996 posted 11:24 pm on Jun 13, 2010 (gmt 0)

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!

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved