Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Query Help

  1. #1
    Join Date
    May 2008
    Posts
    17

    Query Help

    I am trying to combine these two queries with or's into a combine result. I know the OR queries work by themselves but don't show any results when combined. Does any one have any suggestions?


    PHP Code:
    $resultsmysql_query("SELECT * FROM Schools (SELECT *FROM Schools WHERE Western=
    '
    $westernDisicipline' OR English= '$englishDisicipline' OR Fences='$fencesDisicipline'
    (SELECT * FROM Schools WHERE NCAA= '
    $NCAAtype' OR IHSA='$IHSAtype' OR OTHER= '$OTHERtype'))"); 

  2. #2
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    Try

    $results= mysql_query("SELECT * FROM
    (SELECT *FROM Schools WHERE Western= '$westernDisicipline' OR English= '$englishDisicipline' OR Fences='$fencesDisicipline'
    union all
    SELECT * FROM Schools WHERE NCAA= '$NCAAtype' OR IHSA='$IHSAtype' OR OTHER= '$OTHERtype') Schools")
    Last edited by Claire; 05-30-2008 at 09:29 PM.

  3. #3
    Join Date
    May 2008
    Posts
    17
    I just tried the UNION ALL and that does work to the extent that it returns results however it doesn't return the right ones. I need it to return only the results that match both queries not either which is what it seems to be doing.

  4. #4
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    $results= mysql_query("
    select a.* from
    (SELECT *FROM Schools WHERE Western= '$westernDisicipline' OR English= '$englishDisicipline' OR Fences='$fencesDisicipline') a
    inner join
    (SELECT * FROM Schools WHERE NCAA= '$NCAAtype' OR IHSA='$IHSAtype' OR OTHER= '$OTHERtype') b
    on a.col1 = b.col1 and a.col2 = b.col2 and a.col3 = b.col3
    ")

    Please note that col1, col2 and col3 are just the columns you want to exam whether they match on the results from query 1 and query 2, you can extend the join condition to all columns..

  5. #5
    Join Date
    May 2008
    Posts
    17
    isn't the INNER JOIN used when information resides in two different tables?

  6. #6
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    Yes. that's why I am setting from only one of the tables. Basically I am joining both tables and make sure they are equivalent, then I am selecting only from one table "select a.* from "

  7. #7
    Join Date
    May 2008
    Posts
    17
    So assuming that the one table I am working with is called "Schools" would this be the correct way to write the inner join. The Col names and table name is written out exactly as it is in the table.

    PHP Code:
    $resultsmysql_query("SELECT Schools.* FROM Schools 
    (SELECT *FROM Schools WHERE Western= '
    $westernDisicipline' OR English= '$englishDisicipline' OR Fences='$fencesDisicipline') 
    INNER JOIN
    (SELECT * FROM Schools WHERE NCAA= '
    $NCAAtype' OR IHSA='$IHSAtype' OR OTHER= '$OTHERtype')
    ON Schools.Western = Schools.Western and Schools.English = Schools.English and Schools.Fences = Schools.Fences
    "

    Thank you for your help! As you can probably guess I am very new at PHP and MySql!

  8. #8
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    Hello, Try this. You can see that I rename the sub-query#1 to schools1 and sub-query#2 to School2 and then do the join to find the matching rows, then return the result set only from School1 since both School1 and School2 possess the same rows.

    PHP Code:
    $results= mysql_query("SELECT Schools1.* FROM
    (SELECT *FROM Schools WHERE Western= '$westernDisicipline' OR English= '$englishDisicipline' OR Fences='$fencesDisicipline') Schools1
    INNER JOIN
    (SELECT * FROM Schools WHERE NCAA= '$NCAAtype' OR IHSA='$IHSAtype' OR OTHER= '$OTHERtype') Schools2
    ON Schools1.Western = Schools2.Western and Schools1.English = Schools2.English and Schools1.Fences = Schools2.Fences ")

  9. #9
    Join Date
    May 2008
    Posts
    17
    I think I am starting to see what your doing...so the queries are getting named school(s)1 and school(s)2 but they are still pulling there infromation both from the real table Schools. I will try it...

  10. #10
    Join Date
    May 2008
    Posts
    17
    THANK YOU!! That worked perfectly. One last question If I were to add a third query on to that what would be the syntax?

    PHP Code:
    $resultsmysql_query("SELECT Schools1.* FROM
    (SELECT *FROM Schools WHERE Western= '
    $westernDisicipline' OR English= '$englishDisicipline' OR Fences='$fencesDisicipline') Schools1
    INNER JOIN
    (SELECT * FROM Schools WHERE NCAA= '
    $NCAAtype' OR IHSA='$IHSAtype' OR OTHER= '$OTHERtype') Schools2
    INNER JOIN
    (SELECT * FROM Schools WHERE State= '
    $state' OR State= '$state2' OR State= '$state3') Schools3
    ON Schools1.Western = Schools2.Western = Schools3.Western and Schools1.English = Schools2.English = Schools3.English and Schools1.Fences = Schools2.Fences = Schools3.Fences and Schools1.NCAA = Schools2.NCAA = Schools3.NCAA"
    ); 
    Last edited by masonite; 06-04-2008 at 11:44 PM.

  11. #11
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    Try this. Comparison must be done in pair and cover all comparison possibility.

    $results= mysql_query("SELECT Schools1.* FROM
    (SELECT *FROM Schools WHERE Western= '$westernDisicipline' OR English= '$englishDisicipline' OR Fences='$fencesDisicipline') Schools1
    INNER JOIN
    (SELECT * FROM Schools WHERE NCAA= '$NCAAtype' OR IHSA='$IHSAtype' OR OTHER= '$OTHERtype') Schools2
    INNER JOIN
    (SELECT * FROM Schools WHERE State= '$state' OR State= '$state2' OR State= '$state3') Schools3
    ON
    Schools1.Western = Schools2.Western and Schhols1= Schools3.Western and Schhols2= Schools3.Western
    and
    Schools1.English = Schools2.English and Schools1.= Schools3.English and Schools2.English = Schools3.English
    and
    Schools1.Fences = Schools2.Fences and Schools1.Fences = Schools3.Fences and Schools2.Fences = Schools3.Fences
    and
    Schools1.NCAA = Schools2.NCAA and Schools1.NCAA = Schools3.NCAA and Schools2.NCAA = Schools3.NCAA");

  12. #12
    Join Date
    May 2008
    Posts
    17
    That worked perfectly! I can't Thank you enough! This has been bothering me for a while now!!!

  13. #13
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    Glad to help.

  14. #14
    Join Date
    May 2008
    Posts
    17
    The one last thing I need to solve is how to deal with what happens when the user doesn't fill in any criteria for one of the queries the way the table is written and the limited amount of possibilities for the first two make it a bit easier to solve. But the state variable is more difficult. As you can see what I have tried to do is put part of the query in a variable called stateQ and then change stateQ only if all of the state fields on my form are empty. I tested it being written this way with contents in the state variable and it works correctly. I don't like it when they are empty where is should change stateQ to return any state. I was attempting to tell it at that point, in the if statement to return everything that wasn't equal to N/A.

    PHP Code:
    $stateQ "SELECT * FROM Schools WHERE State= '$state' OR State= '$state2' OR State= '$state3'";


    if(empty(
    $westernDisicipline))
    {
        if(empty(
    $englishDisicipline))
        {
            if(empty(
    $fencesDisicpline))
            {
                
    $westernDisicipline "YES";
                
    $englishDisicipline "YES";
                
    $fencesDisicipline "YES";
            }
        }


    if(empty(
    $NCAAtype))
    {
        if(empty(
    $IHSAtype))
        {
            if(empty(
    $OTHERtype))
            {
                
    $NCAAtype "YES";
                
    $IHSAtype "YES";
                
    $OTHERtype "YES";
            }
        }

    if(empty(
    $state))
    {
        if(empty(
    $state2))
        {
            if(empty(
    $state3))
            {
                
    $stateQ "SELECT * FROM Schools WHERE State= <> N/A";
            }
        }
    }
    $resultsmysql_query("SELECT Schools1.* FROM
    (SELECT *FROM Schools WHERE Western= '
    $westernDisicipline' OR English= '$englishDisicipline' OR Fences='$fencesDisicipline') Schools1
    INNER JOIN
    (SELECT * FROM Schools WHERE NCAA= '
    $NCAAtype' OR IHSA='$IHSAtype' OR OTHER= '$OTHERtype') Schools2
    INNER JOIN
    (
    $stateQ) Schools3
    ON
    Schools1.Western = Schools2.Western and Schools1.Western = Schools3.Western and Schools2.Western = Schools3.Western
    and
    Schools1.English = Schools2.English and Schools1.English = Schools3.English and Schools2.English = Schools3.English
    and
    Schools1.Fences = Schools2.Fences and Schools1.Fences = Schools3.Fences and Schools2.Fences = Schools3.Fences
    and
    Schools1.IHSA = Schools2.IHSA and Schools1.IHSA = Schools3.IHSA and Schools2.IHSA = Schools3.IHSA
    and 
    Schools1.State = Schools2.State and Schools1.State = Schools3.State and Schools2.State = Schools3.State"
    ); 

  15. #15
    Join Date
    May 2008
    Posts
    17
    I think i just figured it out myself... it need to be written with <> not = <>

Posting Permissions

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