wondering how to select this

[Shadowing]

Hey guys i need to do a selection and not sure exactly how to do it.

 

 

I need to select x,y from planets where alliance is equal to the same and x and y is also equal to the same

 

 

so i have 15 rows for every x,y and im trying to see which set of 15 all share the same alliance name

[awjudd]

Please show us your table structure.

 

Should be a simple WHERE clause with an AND in it.

 

~awjudd

[Shadowing]

ya but im comparing to a unpredetermind value for the where clause

 

i want to select the x,y where all the alliances equal the same , and the x and y are the same

 

so all 3 columns need to be the same duplicate

 

the alliance column is blank in the example

 

 

 

 

[Shadowing]

sounds confusing how im wording this lol

 

 

im only wanting to select x1 y1 if all the columns that are x1 y1 all have the same alliance name

 

but i need it to search each set of x and y so i cant just say

 

select x,y from planets where x = 1 and y = 1 and alliance = "reavers"

[DavidAM]

You can try this:

 

SELECT x, y, COUNT(*) AS CountRows, COUNT(DISTINCT alliance) AS CountAlliance
FROM planets
WHERE x = y
GROUP BY x, y
HAVING CountRows = CountAlliance 

 

Unfortunately, it is not going to give you the alliance value.

 

I may have misunderstood the requirements. This query returns rows where x = y. If you want any combination of x,y where all rows with that combination have the same alliance, you can take the WHERE clause out.

 

This query is going to scan the entire table. So, the more data you have, the slower it will be. (depending on indexes, etc).