Search query help

NZ_Kiwis

I have a database table that I'm searching for a set of results from.

In this table I have a single StationID for railways station. I have a second table with StationID and stationName as columns and it's called 'Stations'.

What I want to do is search my main table for results sometimes when a stationName is entered.

Any ideas, I was going to join the tables at the ID's but I need to search the StationName first

Weedpacket

Yes, you would. You'd put the requirement that matches have a certain station name in the 'WHERE' clause.

NZ_Kiwis

Here is what I have, it returns multiple filenames

SELECT DISTINCT(p.Filename), p.ID, m.ID as MID, m.Username FROM `photo` p 

JOIN `stations` a 
JOIN `trains` l 
JOIN `members` m 

WHERE l.trainName LIKE '%%' AND a.Name LIKE '%%' AND p.Rego LIKE '%%'

Weedpacket

You'll probably find that each repeated occurrence of the filename is paired with a different combination of values of the other fields you're returning. Which of those combinations do you want?

NogDog

I don't have enough info to understand all the table relationships, but if for now we just narrow it down to photo and stations, and assume that there is a photo.StationID column, then it might be something like:

select <column list here>
from photo p
inner join station s on p.StationID = s.ID
where s.stationName = 'station name here'

If you then need columns from other related tables, they would have similar "on" clauses in their joins for whatever their relationship is to other tables in the query.

dalecosp

And note that NogDog has used an INNER JOIN rather than a "plain" (Cartesian) JOIN. Understanding JOIN types may very well shed some light on why you get so many results 😉

This doesn't look TOO bad for a tute: http://www.sql-join.com/sql-join-types/

NZ_Kiwis

When I do this

SELECT DISTINCT(Filename), p.ID, m.ID as MID, m.Username FROM `photo` p

I get 20+ results.

When I do this

SELECT DISTINCT(Filename) FROM `photo` p

I get 2 as expected.

Weedpacket

You'll probably find that each repeated occurrence of the filename is paired with a different combination of values of the other fields you're returning. Which of those combinations do you want?

Unless you provide constraints that say otherwise, you'll get all of them.

If you just want the filename, you should ask for just the filename and not all the other stuff. Presumably you want all the other stuff as well or you wouldn't be asking for it, so presumably you will want to know which filename goes with each such record - even if the same filename goes with more than one record.

NZ_Kiwis

I want the filename, I also want the relevant StationName from the stations table - getting this from the station ID in the primary table linked to the stations table.

Maybe it's better to just get the stationID then do a separate query

pbismad

Here's the main point from the information posted so far - When you JOIN tables, you need to supply a join condition, so that the db server knows what relationship exists between the tables being joined. If you don't supply a join condition, all the rows in the tables being joined will be joined to every row in the query, producing every combination, i.e. a product of all the rows. If you have 4 rows in one table and 5 rows in the other table, you will get 20 rows in the result. If you join with another table that had 2 rows, you would get 40 rows in the result set. When you add the join conditions to your query, you will get only the related rows between the tables joined together.

Next, DISTINCT is not a function. It is a keyword that when present in the sql query removes duplicate rows from the result set. DISTINCT(Filename) is exactly the same as DISTINCT Filename, other columns listed here.... Using DISTINCT to fix the issue at hand isn't working because the other values being selected in the rows are different (they are in fact every possible combination of values), so, nothing is being removed from the result set by having the DISTINCT keyword in the query.

If you post your table definitions and indicate which columns relate the data between the different tables, someone can post a definitive sql statement that will do what you want.

Weedpacket

Well, if you need the other information as well, then once you've read it all in, it's easy enough to go through the returned data and collect the filenames. [man]array_column[/man] would put them all in an array and of course [man]array_unique[/man] will eliminate duplicates.