php mysql query

[darkhelmet]

I ve got a table like this and need the query to get the result.  anyone?

MY BAD, THE IDS DO HAVE DUPES...UPDATE D.


ID   Name   Type   Value

1   Store1   Region   9000   
2   Store2   Region     9000
3   Store3      Region     8000

1   Store1   Service   6000
2   Store2   Service     6000
3   Store3   Service     6000

1   Store1   Wage   5555
2   Store2   Wage   4444
3   Store3   Wage   3333


I want to get all Name where Region = 9000 and Service = 6000

The result should be Name is Store1 and Store2.

What is the query to get this?

select store where region = 9000 and service = 6000 doesn't pull it?

[darkhelmet]

i know but but it's not my table design, i just have to get data from it.

[darkhelmet]

i had another developer look at it too and he was also stumped as well.  he didn't have any idea off hand.

[darkhelmet]

I think i know where you're going with this.  It might just work with some tweaks.  I'm going to do some test right now.

Wow, then it's going to be difficult. I'm not even sure if you can do it in one query.
Because you'll have to test for the Type= Region AND Value=9000, but then you can't test for Type=Service again...
unless you did more than one query. Plus that entails that you do filtering in your php code.

If doing more than one query isn't an issue than I would suggest:

SELECT Name FROM table WHERE Type='Region' AND Value='9000'

Then take the result and use it in a new query:

SELECT * FROM table WHERE Name='result' AND Type='Service' AND Value='6000'

That's all I can think of without actually writing the code and testing it. Hope this helps.

[jbutton]

Try this:

Code: [Select]

select Name where (Type='Service' and Value = 600) or (Type = 'Region' and Value = 900)


[darkhelmet]

I got a seasoned developer to answer my question.

It seems like you can do it in one query.  Something like this

SELECT a.name, count( id ) as value_count FROM table AS a WHERE  ( (type = region AND value IN (9000)) OR (type = service AND  value IN (6000)) ) GROUP BY a.id HAVING value_count = 2

[jbutton]

I got a seasoned developer to answer my question.

It seems like you can do it in one query.  Something like this

SELECT a.name, count( id ) as value_count FROM table AS a WHERE  ( (type = region AND value IN (9000)) OR (type = service AND  value IN (6000)) ) GROUP BY a.id HAVING value_count = 2

Did you try my code?  Did it work?

[jetter]

Or you can get it simply using:

Code: [Select]

SELECT Name FROM <TABLE> WHERE (Type='Region' AND  Value = '9000') OR (Type='Service' AND Value= '6000') GROUP BY Name
Assuming the two types "region" and "Service" are different and you want the ability to say, if a Name has A or B then the above should work.  If you change the A or B to AND then you simply change the OR to AND.

Jbutton got the right idea but it will get duplicates, op just wants the name and no duplicates so adding the GROUP BY clause will accomplish that.

OP original assumption is an error because it seems "region = 9000 and service = 6000"
region, service are text within the type field and 9000, 6000 are values within the value field.

[jetter]

I got a seasoned developer to answer my question.

It seems like you can do it in one query.  Something like this

SELECT a.name, count( id ) as value_count FROM table AS a WHERE  ( (type = region AND value IN (9000)) OR (type = service AND  value IN (6000)) ) GROUP BY a.id HAVING value_count = 2

this will return duplicates since you are grouping by ID and id are different for each entry but has the same name.  This query is way to slow because it uses too many built in SQL functions that you do not need.  What is the purpose of counting id?  You are not using it and by Grouping by ID?  what is that used for since the id are unique anyway; so this makes the having clause pointless because the id will all have 1 as their count and the sql will error out because group by clause requires every field in the returning tuples to be group also.

[jbutton]

From the table, I conclude that there can be no duplicates.

Why would you have duplicates?  Makes no sense.

OK, I forgot.. I was thinking about row and not Name.  Names duplicates.

[darkhelmet]

jbutton, thanks for the input.  i originally had used the same query as you suggested but gave too many results.

Did you try my code?  Did it work?

[darkhelmet]

thanks jetter, you have the right answer and i understand what you're saying.  my bad, my table was wrong.  the field `id` do have dupes so grouping by id is appropriate.  sorry, just got sloppy in my post.  

the counting is required...the table i posted above is just a very very shortened version of the one i'm working on.
the count is for couting the number of 'type' because i need to be able to search by any number of type, it counts the number of matches and the total count must match the number of types i'm searching for.

thanks for your input.

this will return duplicates since you are grouping by ID and id are different for each entry but has the same name.  This query is way to slow because it uses too many built in SQL functions that you do not need.  What is the purpose of counting id?  You are not using it and by Grouping by ID?  what is that used for since the id are unique anyway; so this makes the having clause pointless because the id will all have 1 as their count and the sql will error out because group by clause requires every field in the returning tuples to be group also.

[jetter]

grouping by id is not worth it because it will add more overhead to it,  why not just count the name instead.  You will have the same count of the names; which is the same as the id, and since you already are returning it, it makes the query more efficient.