I have a table "Subscriptions" .
Subscriptions contains the columns "ChannelID" & "MemberID".
The "Channels" table contains a column called "Title"
When someone is viewing a channel, I would like to display "Members
who subscribe to this channel also subscribe to ..." and then list the
Titles of 3 or 4 channels ordered by the most popular.
I'm having trouble coming up with the correct MySQL syntax. Has
someone here done something like this before?
2  2068 
"Tim Constantine" <sp**@togosolo. com> wrote in message
news:bb******** *************** ***@posting.goo gle.com... I have a table "Subscriptions" .
Subscriptions contains the columns "ChannelID" & "MemberID".
The "Channels" table contains a column called "Title"
When someone is viewing a channel, I would like to display "Members
who subscribe to this channel also subscribe to ..." and then list the
Titles of 3 or 4 channels ordered by the most popular.
I'm having trouble coming up with the correct MySQL syntax. Has
someone here done something like this before?
What do you mean by channel ? If I assume its something like a particular
magazine, then you want to select all the channels people who bought this
channel also bought (self join on your table), then order and choose.
Something like
select s2.ChannelID, count(*) c from subscriptions s1 join subscriptions s2
on s1.MemberID=s2. MemberID and s1.ChannelID!=s 2.ChannelID where s1.ChannelID
= ?? group by s2.ChannelID order by c desc limit 5
Try that, its untested but should be a start.
Tim Constantine wrote: When someone is viewing a channel, I would like to display "Members
who subscribe to this channel also subscribe to ..." and then list the
Titles of 3 or 4 channels ordered by the most popular.
SELECT COUNT(S.MemberI D) AS MemberCount, C.Title
FROM Subscriptions S INNER JOIN Channels C ON S.ChannelID = C.ChannelID
INNER JOIN Subscriptions M ON S.MemberID = M.MemberID
WHERE M.ChannelID = ?
GROUP BY S.MemberID
ORDER BY MemberCount DESC
LIMIT 4
Substitute the currently viewed channel ID for the '?' parameter in that
query, and I think that'll work. I tried it out with a set of tables in
the app I'm working on, where my tables have similar relationships to
one another as your tables.
Regards,
Bill K. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: krystoffff |
last post by:
Hi
I would like to paginate the results of a query on several pages. So I
use a query with a limit X offset Y to display X results on a page,
ok.
But for the first page, I need to run the same query with a count(*)
to know how many pages I will get (number total of rows/ X).
The problem is my query is very slow (maybe 5s) because there is much
|
by: Robin Tucker |
last post by:
Hi,
Can anyone tell me how to select the "most recent" date values from a
grouped query? Consider the following:
CREATE TABLE . ( NOT NULL , NOT
NULL , NOT NULL ) ON
This is a simplified adjacency list. What I want to do is find the highest
|
by: Ellen Manning |
last post by:
Using A2K. In my table I have the field "Grant" which can have a
value or be null. I have a query that counts the number of records
and has a Where clause on the Grant field. The query won't work when
Grant is null. What's the proper syntax for getting it to work when
Grant is null?
Here's my Where criteria:
!!
|
by: |
last post by:
I found similiar issues in MS-KB but nothing that helped
me; got the Windows and Office updates from the MS website
but that hasn't changed the behavior of this problem, and
I don't see anything relevant in the newsgroup; help will
be gratefully received.
Windows 2000 system, I'm logged in with admin rights, using
Office 2003. I have two databases on my local drive, in
the same subdirectory. In the database HAMCHEESE, I create
an...
|
by: Mike Bridge |
last post by:
I am using MS Access 2003, and I typed in a query in SQL view which was working fine. Now when I open it, it gives me
the error "Join expression not supported". Apparently, Access "fixed" it for me.
Unfortunately, I can't correct it because when I click "OK", the window disappears. I don't see any way to open the
query directly in SQL view, and both the Design and Data views exit as soon as I close the error dialog.
Is there any way...
| | |
by: NotGiven |
last post by:
I want to add some shopping pages to my site. I'd lilke to use the easiest
things possible, thought I am a PHP developer.
What's the easiest way to add a "web store" using Amazon web services or a
drop in availabel through someone.
I'm surprised it has not been written about in the Macromedia's web site.
|
by: Dave |
last post by:
http://snipr.com/UltimateHTML
|
by: Ted |
last post by:
http://snipr.com/VisualC2008
|
by: geolemon |
last post by:
I'm trying to perform an update, and I can't avoid this error - I've tried this query what must be 5 different very fundamentally different ways now.
Arg.
I used to be a DBA in large DB2 and SQL Server environments, but I'm a little rusty, plus I'm very new to Access.
I am trying to perform a calculation based on information in a few tables, and use the result to update another.
Should be simple, here's what counts from the tables...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
