[RESOLVED] SQL issue

[bengt]

Hello there.. I need help for an sql query.

I have this db

|id	|activity	|team	|region	|others	|
+-------+---------------+-------+-------+-------+
|1	|Aktivitet1	|6	|4	|1	|
|2	|aktivitet2	|6	|4	|2	|
|3	|Aktivitet3	|5	|4	|1	|
|4	|aktivitet4	|5	|4	|2	|
|5	|Aktivitet5	|4	|3	|3	|
|6	|aktivitet6	|3	|3	|3	|
|7	|Aktivitet7	|3	|3	|3	|
|8	|aktivitet8	|2	|5	|2	|

The numbers in the "others" column means:
1: Only show within the same team
2: Show my team, and other teams but in same region
3: Show my team, and other teams in all regions

How can i base my query on these criterias?

This is what i want to show

|id	|activity	|team	|region	|others	|
+-------+---------------+-------+-------+-------+
|1	|Aktivitet1	|6	|4	|1	|
|2	|aktivitet2	|6	|4	|2	|
|4	|aktivitet4	|5	|4	|2	|
|5	|Aktivitet5	|4	|3	|3	|
|6	|aktivitet6	|3	|3	|3	|

My english is poor, and my explaining is poorer, hopw you understand what I mean...🙂

Bengt

[tomhath]

"My english is poor, and my explaining is poorer, hopw you understand what I mean..."

...and your typing isn't all that great either 😉

I think this what you want. But I don't see why your example shows row 6 and not row 7.

select distinct a2.*
from aktiv a2
inner join aktiv a2
on a1.team = a2.team
or (a1.region = a2.region and a2.others = 2)
or a2.others = 3
where a1.team = 6
order by a2.activity

[bengt]

WOW

Right on the spot....

Noticed the typos...😉
I'll try to fix that next time...🙂

[bengt]

Can't seem to get the query to work on MySQL 4.0.25

[tomhath]

I tested it on SQLServer. What error is MySQL giving you?

[bengt]

i altered it to fit my db.

$sql = "select distinct a2.*,DATE_FORMAT(a2.dato, '%d/%m-%Y kl %H:%i') as ddato from aktiviteter a1 inner join aktiviteter a2 on a1.lag = a2.lag or (a1.region = a2.region and a2.andre = 2) or a2.andre = 3 where a1.lag = '$lagid' order by a2.dato";

it works fine locally on my computer with mysql 4.1, but not on my isp's server who runs 4.0.
not receiving any error. but not working as it should.

[tomhath]

I have MySQL 5.0 so I can't try it.

Does your ISP let you run phpmyadmin? And are you sure it isn't just different data in the table?

[bengt]

The problem is when another team doesn't have any entries, it won't show other team's entries... In the lates query "team" is called "lag".
So if team 1 is making an entry withe team = 1, and others=2, then team 2 won't get it listet up before they make an entry themself.

You understand?

This is the query I'm using:

select distinct a2.*,DATE_FORMAT(a2.dato, '%d/%m-%Y kl %H:%i') as ddato
from aktiviteter a1 <- This was a2, but that produced an error.
inner join aktiviteter a2
on a1.lag = a2.lag
or (a1.region = a2.region and a2.andre = 2)
or a2.andre = 3
where a1.lag = '$lagid'
order by a2.dato