What is multiple variable?
The result is not right, but what it is?
Could someone enlighten me on this?
Look
What is multiple variable?
I don’t understand a thing, sorry
Or temporary table
Don’t know table variables?
Okey no problem bro
there is no table variable here.
Each variable in MySQL can contain only one value of one type. It can’t contain set or vector of values
there is no table variables here and MySQL doesn’t support something which can be called ‘table variables’
Anyone have upgradtion documentation of MySQL 5.5 on windows

hi guys i have a database with tables like this, i want to count number of each stream_type (0 or 1) and vod_type (0 or 1) in a collection . i tried joining them like this but the result is not right
select c.id,
c.name,
count(IF(s.stream_type = 0, 1, null)) as channelCount,
count(IF(s.stream_type = 1, 1, null)) as radioCount,
count(IF(v.vod_type = 0, 1, null)) as movieCount,
count(IF(v.vod_type = 1, 1, null)) as seriesCount
from collection c
left join collection_stream cs on c.id = cs.collection_id
left join stream s on s.id = cs.stream_id
left join collection_vod cv on c.id = cv.collection_id
left join vod v on v.id = cv.vod_id
group by c.id
select c.id,
c.name,
count(IF(s.stream_type = 0, 1, null)) as channelCount,
count(IF(s.stream_type = 1, 1, null)) as radioCount,
count(IF(v.vod_type = 0, 1, null)) as movieCount,
count(IF(v.vod_type = 1, 1, null)) as seriesCount
from collection c
left join collection_stream cs on c.id = cs.collection_id
left join stream s on s.id = cs.stream_id
left join collection_vod cv on c.id = cv.collection_id
left join vod v on v.id = cv.vod_id
group by c.id
if code is not readable:
https://hastebin.com/afadufimuv.sql
Col1 Col2. Col3
1 ABC 10
1. ABC. 15
Select * from table1 order by col1,col2 ;
What will be order of col3 in output.
select c.id,
c.name,
count(IF(s.stream_type = 0, 1, null)) as channelCount,
count(IF(s.stream_type = 1, 1, null)) as radioCount,
count(IF(v.vod_type = 0, 1, null)) as movieCount,
count(IF(v.vod_type = 1, 1, null)) as seriesCount
from collection c
left join collection_stream cs on c.id = cs.collection_id
left join stream s on s.id = cs.stream_id
left join collection_vod cv on c.id = cv.collection_id
left join vod v on v.id = cv.vod_id
group by c.id
The result is not right, but what it is? What is wrong with it?
select c.id,
c.name,
count(IF(s.stream_type = 0, 1, null)) as channelCount,
count(IF(s.stream_type = 1, 1, null)) as radioCount,
count(IF(v.vod_type = 0, 1, null)) as movieCount,
count(IF(v.vod_type = 1, 1, null)) as seriesCount
from collection c
left join collection_stream cs on c.id = cs.collection_id
left join stream s on s.id = cs.stream_id
left join collection_vod cv on c.id = cv.collection_id
left join vod v on v.id = cv.vod_id
group by c.id
At least you have wrong group by here
select c.id,
c.name,
count(IF(s.stream_type = 0, 1, null)) as channelCount,
count(IF(s.stream_type = 1, 1, null)) as radioCount,
count(IF(v.vod_type = 0, 1, null)) as movieCount,
count(IF(v.vod_type = 1, 1, null)) as seriesCount
from collection c
left join collection_stream cs on c.id = cs.collection_id
left join stream s on s.id = cs.stream_id
left join collection_vod cv on c.id = cv.collection_id
left join vod v on v.id = cv.vod_id
group by c.id
Split this query to streams and vods using UNION ALL
The numbers are greater than what they should be
Hmm I’ll try that thanks
Split the query
You must have carthesian product there between two alternatives
thanks bro, i split the query and used union and it’s fixed now

I am learning MySQL right now. At this step, I need to remove the duplicates shown in the result below, where the pair of AID and BID is the same except they swap position. Could someone enlighten me on this? Thank you
hmmmm, I solved this by only including the result of A.StuID < B.StuID . Not sure this is the intended solution or not
For telling you if this is intended solution one at least should know your task
I can only say your query is over complicated.