Search query help
Results 1 to 11 of 11

Thread: Search query help

  1. #1
    Senior Member
    Join Date
    Apr 2005
    Posts
    1,336

    Search query help

    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

  2. #2
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Contact Unit "Coping Mechanism"
    Posts
    22,503
    Yes, you would. You'd put the requirement that matches have a certain station name in the 'WHERE' clause.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  3. #3
    Senior Member
    Join Date
    Apr 2005
    Posts
    1,336
    Here is what I have, it returns multiple filenames


    PHP Code:
    SELECT DISTINCT(p.Filename), p.IDm.ID as MIDm.Username FROM `photo

    JOIN 
    `stations
    JOIN 
    `trains
    JOIN 
    `members

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

  4. #4
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Contact Unit "Coping Mechanism"
    Posts
    22,503
    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?
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  5. #5
    High Energy Magic Dept. NogDog's Avatar
    Join Date
    Aug 2006
    Location
    Ankh-Morpork
    Posts
    14,843
    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:
    Code:
    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.
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

  6. #6
    Settled 4 red convertible dalecosp's Avatar
    Join Date
    Jul 2002
    Location
    Accelerating Windows at 9.81 m/s....
    Posts
    8,493
    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/
    /!!\ mysql_ is deprecated --- don't use it! Tell your hosting company you will switch if they don't upgrade! /!!!\ ereg() is deprecated --- don't use it!

    dalecosp "God doesn't play dice." --- Einstein "Perl is hardly a paragon of beautiful syntax." --- Weedpacket

    Getting Help at All --- Collected Solutions to Common Problems --- Debugging 101 --- Unanswered Posts --- OMBE: Office Machines, Business Equipment

  7. #7
    Senior Member
    Join Date
    Apr 2005
    Posts
    1,336
    When I do this

    PHP Code:
    SELECT DISTINCT(Filename), p.IDm.ID as MIDm.Username FROM `photo
    I get 20+ results.

    When I do this

    PHP Code:
    SELECT DISTINCT(FilenameFROM `photo
    I get 2 as expected.

  8. #8
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Contact Unit "Coping Mechanism"
    Posts
    22,503
    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.
    Last edited by Weedpacket; 06-18-2017 at 04:41 AM.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  9. #9
    Senior Member
    Join Date
    Apr 2005
    Posts
    1,336
    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

  10. #10
    Senior Member
    Join Date
    Apr 2016
    Posts
    121
    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.
    Last edited by pbismad; 06-18-2017 at 05:34 AM.
    Programming should not be a painful activity. If you are experiencing pain while programming, you are probably doing something wrong.

  11. #11
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Contact Unit "Coping Mechanism"
    Posts
    22,503
    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. array_column would put them all in an array and of course array_unique will eliminate duplicates.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •