Join or union on the same table

I’m working with a radius database meaning that a user will have many attributes and I just need a few. The code below only returns [maxsession] => 86400. expiration is not in the array, only maxsession is. I was hoping that by using AS would separate them in an array. I would also think that expiration would overwrite maxsession if that was the case.
Here is what I am trying to do.


select Value AS maxsession
from
    radcheck
Where
    Attribute = 'Max-All-Session' AND Username = '$username'
union 
	select Value AS expiration
	from
		radcheck
	Where
		Attribute = 'Expiration' AND Username = '$username'

Many thanks

INSERT INTO radcheck (id, UserName, Attribute, op, Value) VALUES
(9, ‘Adrian’, ‘User-Password’, ‘:=’, ‘test’),
(10, ‘Adrian’, ‘Simultaneous-Use’, ‘:=’, ‘1’),
(11, ‘Adrian’, ‘Max-All-Session’, ‘:=’, ‘2592000’),
(12, ‘Adrian’, ‘Expiration’, ‘:=’, ‘1442281403’),
(97, ‘loren’, ‘User-Password’, ‘:=’, ‘wolsiffer’),
(98, ‘loren’, ‘Simultaneous-Use’, ‘:=’, ‘1’),
(99, ‘loren’, ‘Max-All-Session’, ‘:=’, ‘86400’),
(100, ‘loren’, ‘Expiration’, ‘:=’, ‘1287878400’),

Thanks for looking.

it most definitely is, but since i don’t do php, i don’t know the exact syntax

it’s something like

// run query, then ...
array2 = $shuffle(rows,columns)

:slight_smile:

I was too fast to post, when I do print_r on the $row I get.

Array
(
[Attribute] => Max-All-Session
[Value] => 2592000
)

I don’t get the expiration unless it is in a loop. I was hoping to get both values as an array contained in $row. Not possible?

Edit:

I’m looking for an array like I get doing
SELECT Attribute
, Value
, id
, op
FROM radcheck
WHERE Username = ‘loren’";
then when I do a print_r on $row I get
Array
(
[Attribute] => User-Password
[Value] => wolsiffer
[id] => 97
[op] => :=
)
Thats why I was trying AS in hopes that I would get the results I wanted.

Awesome, you are the guru. Many thanks and I understand exactly what is happening now. You make it look easy.

two things…

first, a union query creates a result set, and this result set has column names, and those column names are taken ~exclusively~ from the first SELECT in the union

so whether the value comes from the first SELECT or the second, it will always have the column name “maxsession”

in fact, if the first SELECT does not return a row, and the second one does, it will still have that column name

second, if you say UNION instead of UNION ALL, duplicate rows are removed

so if both SELECTs return a row with a value of 86400, only one of those rows survives in the final result set

You’ll also really want to rethink your table design. You are using what is called Entity, Attribute, Value table type. It is one of the most difficult table types to query with any kind of efficiency.

please do me a favour, dump the table and a few representative rows of data that would illustrate the problem, so that i can do a test or two

okay, thanks

here is your query on that data:


SELECT Value AS maxsession
  FROM radcheck
 WHERE Attribute = 'Max-All-Session' 
   AND Username = 'Adrian'
UNION 
SELECT Value AS expiration
  FROM radcheck
 WHERE Attribute = 'Expiration' 
   AND Username = 'Adrian'

[COLOR="Red"][U]maxsession[/U]
2592000
1442281403[/COLOR]

which is the correct result, right?

the result set contains two rows, with the correct values extracted, and the column name is like i explained it earlier

would you prefer to see this?


SELECT Attribute
     , Value 
  FROM radcheck
 WHERE Attribute = 'Max-All-Session' 
   AND Username = 'Adrian'
UNION 
SELECT Attribute
     , Value 
  FROM radcheck
 WHERE Attribute = 'Expiration' 
   AND Username = 'Adrian'

[COLOR="Blue"][U]Attribute[/u]	[u]Value[/U]
Max-All-Session	2592000
Expiration	1442281403[/COLOR]

:cool:

:rofl:

I finally tested it in phpmyadmin and IT WORKS, now I need to figure out what is going on on the php side of things. This is a head scratcher. Thank you so much Rudy. I’ll visit the php forum.

Thanks Rudy,
Expiration is 1287878400 and maxsession is 86400. I tried UNION ALL but still just returns maxsession. This is a query I have been trying on and off for years but just not getting it. This is my first attempt at using UNION because all other attempts failed. Is this possible and most importantly, how?
If its any help I do know of over a hundred ways that it doesn’t work :rolleyes:

Cheers