I have an SQL statement like this,

FROM (select "John" as FirstName Union All select "Mary" Union All select "Alis"), 
     (select "Doe" as LastName union all select "Queen" union all select "Gunter")

FirstName   LastName
John        Doe
John        Queen
John        Gunter
Mary        Doe
Mary        Queen
Mary        Gunter
Alis        Doe
Alis        Queen
Alis        Gunter

This gives me a Cartesian, how do I get the following result,

FirstName  LastName
John       Doe
Mary       Queen
Alis       Gunter

Any help would be great.

  • You are arbitrarily selecting a (first_name, last_name) combination. What are your selection criteria? – Vérace Apr 25 '19 at 19:05
  • @Vérace I would like to use this as a criteria for a left join with another table. I have about 35 first and last names that I want to filter out and get data from a table, with some calculated columns. – user9431057 Apr 25 '19 at 19:32
  • Ok - but how are you selecting those first_name and surname combinations? – Vérace Apr 25 '19 at 19:44
  • @Vérace My thought was to create a concatenated field from there. Which not sure if I could to it in sql. Either way, can I have the output as I want? May be based on the index? – user9431057 Apr 25 '19 at 20:10
  • There is no order between the rows in a table, it is an un-ordered set. So, unless you want to match any firstname to any lastname, you need some way to "connect"/relate John with Doe. – Tibor Karaszi Apr 26 '19 at 8:03

It's hard to understand your end goal, but if you want a virtual table with a specific set of values in it, one way is to use a table value constructor:

    VALUES ('John', 'Doe'), ('Mary', 'Queen'), ('Alis', 'Gunter')
) AS Names(FirstName, LastName);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.