Announcement

Collapse
No announcement yet.

Single SQL query to select distinct values from two columns?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Single SQL query to select distinct values from two columns?

    Afternoon all,

    I have a table which stores details regarding pupils and their parents. The three fields of interest are:

    • dbPupilId - primary key, auto-incremented SMALLINT
    • dbPupilFatherEmail
    • dbPupilMotherEmail

    The two email fields can be null or contain a string.

    What I need is a query to gather all of the unique entries in both of the email fields, so that no matter how many times a given email address appears in either or both columns, it'll only be returned once. I can do this per column as follows:

    Code:
    SELECT DISTINCT dbPupilMotherEmail FROM pupil WHERE dbPupilMotherEmail IS NOT NULL AND dbPupilMotherEmail NOT LIKE '%.invalid';
    
    SELECT DISTINCT dbPupilFatherEmail FROM pupil WHERE dbPupilFatherEmail IS NOT NULL AND dbPupilFatherEmail NOT LIKE '%.invalid';
    Is it possible to do this for both columns in a single query?

    Regarding the predicate "NOT LIKE %.invalid", as the system is in development, a number of entries have an email address ending in .invalid to indicate that they're dummy data only and shouldn't be returned by the query.

    Thanks in advance

    Edit: Shouldn't make a huge difference, but the database in question is running on MySQL 5.
    Gareth Howells

    BSc (Hons), MBCS, MCP, MCDST, ICCE

    Any advice is given in good faith and without warranty.

    Please give reputation points if somebody has helped you.

    "For by now I could have stretched out my hand and struck you and your people with a plague that would have wiped you off the Earth." (Exodus 9:15) - I could kill you with my thumb.

    "Everything that lives and moves will be food for you." (Genesis 9:3) - For every animal you don't eat, I'm going to eat three.

  • #2
    Re: Single SQL query to select distinct values from two columns?

    How about creating a union and selecting from that
    Something like:

    Select DISTINCT fldEmail FROM (SELECT fldFatherEmail AS fldEmail FROM pupil UNION SELECT fldMotherEmail AS fldEmail FROM pupil)

    Note not exact syntax but should give idea
    Tom Jones
    MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
    PhD, MSc, FIAP, MIITT
    IT Trainer / Consultant
    Ossian Ltd
    Scotland

    ** Remember to give credit where credit is due and leave reputation points where appropriate **

    Comment


    • #3
      Re: Single SQL query to select distinct values from two columns?

      Cheers buddy, worth a try. Watch this space to see if I owe you a pint

      Edit 1: MySQL complains: "#1248 - Every derived table must have its own alias". But it's a step forward now let's see what Google has to say about error 1248.

      Edit 2: You beauty. Come collect your voucher good for 1 free pint

      Code:
      SELECT DISTINCT dbParentEmail FROM (SELECT dbPupilFatherEmail AS dbParentEmail FROM pupil UNION SELECT dbPupilMotherEmail AS dbParentEmail FROM pupil) AS mailunion WHERE dbParentEmail IS NOT NULL AND dbParentEmail NOT LIKE '%.invalid';
      Last edited by gforceindustries; 30th June 2010, 16:05.
      Gareth Howells

      BSc (Hons), MBCS, MCP, MCDST, ICCE

      Any advice is given in good faith and without warranty.

      Please give reputation points if somebody has helped you.

      "For by now I could have stretched out my hand and struck you and your people with a plague that would have wiped you off the Earth." (Exodus 9:15) - I could kill you with my thumb.

      "Everything that lives and moves will be food for you." (Genesis 9:3) - For every animal you don't eat, I'm going to eat three.

      Comment


      • #4
        Re: Single SQL query to select distinct values from two columns?

        Want to turn that pint into a yard?

        I need to extend the query to be a little more specific... I need to get the email addresses as before, but only for the parents of pupils where cbPupilCurrent (bool) is true.

        I've tried a couple of queries but haven't had any luck. I can see why they're not working, just not sure what I need to do to achieve this. I'm sure it must still be possible to do it with one query...

        What I've tried:

        Code:
        SELECT DISTINCT dbParentEmail FROM
        (SELECT dbPupilFatherEmail AS dbParentEmail FROM pupil UNION SELECT dbPupilMotherEmail AS dbParentEmail FROM pupil UNION SELECT dbPupilCurrent AS dbIsCurrent FROM pupil)
        AS mailunion WHERE dbParentEmail IS NOT NULL AND dbPupilIsCurrent = true;
        Code:
        SELECT DISTINCT dbParentEmail, dbPupilIsCurrent FROM (SELECT dbPupilFatherEmail AS dbParentEmail FROM pupil UNION SELECT dbPupilMotherEmail AS dbParentEmail FROM pupil)
        AS mailunion, pupil WHERE dbParentEmail IS NOT NULL AND dbPupilIsCurrent = true;
        Gareth Howells

        BSc (Hons), MBCS, MCP, MCDST, ICCE

        Any advice is given in good faith and without warranty.

        Please give reputation points if somebody has helped you.

        "For by now I could have stretched out my hand and struck you and your people with a plague that would have wiped you off the Earth." (Exodus 9:15) - I could kill you with my thumb.

        "Everything that lives and moves will be food for you." (Genesis 9:3) - For every animal you don't eat, I'm going to eat three.

        Comment


        • #5
          Re: Single SQL query to select distinct values from two columns?

          I think you need the boolean test in the inner query, as WHERE conditions for each part of the UNION
          Tom Jones
          MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
          PhD, MSc, FIAP, MIITT
          IT Trainer / Consultant
          Ossian Ltd
          Scotland

          ** Remember to give credit where credit is due and leave reputation points where appropriate **

          Comment


          • #6
            Re: Single SQL query to select distinct values from two columns?

            I owe you a yard. I'll take a dirty pint as punishment for not having figured that one out myself

            Code:
            SELECT DISTINCT dbParentEmail FROM
            (SELECT dbPupilFatherEmail AS dbParentEmail FROM pupil WHERE dbPupilCurrent = true
            UNION SELECT dbPupilMotherEmail AS dbParentEmail FROM pupil WHERE dbPupilCurrent = true)
            AS mailunion WHERE dbParentEmail IS NOT NULL AND dbParentEmail NOT LIKE '%.invalid';
            Cheers buddy
            Gareth Howells

            BSc (Hons), MBCS, MCP, MCDST, ICCE

            Any advice is given in good faith and without warranty.

            Please give reputation points if somebody has helped you.

            "For by now I could have stretched out my hand and struck you and your people with a plague that would have wiped you off the Earth." (Exodus 9:15) - I could kill you with my thumb.

            "Everything that lives and moves will be food for you." (Genesis 9:3) - For every animal you don't eat, I'm going to eat three.

            Comment


            • #7
              Re: Single SQL query to select distinct values from two columns?

              I'll take you up on that sometime!

              Glad it's fixed and working
              Tom Jones
              MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
              PhD, MSc, FIAP, MIITT
              IT Trainer / Consultant
              Ossian Ltd
              Scotland

              ** Remember to give credit where credit is due and leave reputation points where appropriate **

              Comment


              • #8
                Re: Single SQL query to select distinct values from two columns?

                Me too. Damn deadlines...

                I do have a bug in my code that I can't figure out... nor can a colleague with significantly more experience that I have. We're about up to the point where we want to say it's a bug in MySQL, but it'd be a bit of a cop out to claim that
                Gareth Howells

                BSc (Hons), MBCS, MCP, MCDST, ICCE

                Any advice is given in good faith and without warranty.

                Please give reputation points if somebody has helped you.

                "For by now I could have stretched out my hand and struck you and your people with a plague that would have wiped you off the Earth." (Exodus 9:15) - I could kill you with my thumb.

                "Everything that lives and moves will be food for you." (Genesis 9:3) - For every animal you don't eat, I'm going to eat three.

                Comment

                Working...
                X