473,835 Members | 1,871 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Howto put these two queries together?

Howto put these two queries together?

sql = "SELECT DISTINCT medium_id, COUNT(tag) AS count FROM tags WHERE
tag = 'test' GROUP BY medium_id ORDER BY count DESC"

This query is working.

Id like to try something like

sql = "SELECT * FROM media WHERE id IN (SELECT DISTINCT medium_id FROM
tags WHERE tag = 'test')"

But how can I order the resut by quantity (counts of tag)?

Thank you for help.
//BEN

May 25 '06 #1
1 2352
Benny Schudel wrote:
Howto put these two queries together?
sql = "SELECT DISTINCT medium_id, COUNT(tag) AS count FROM tags WHERE
tag = 'test' GROUP BY medium_id ORDER BY count DESC"
sql = "SELECT * FROM media WHERE id IN (SELECT DISTINCT medium_id FROM
tags WHERE tag = 'test')"


SELECT m.*, COUNT(*) AS count
FROM media AS m JOIN tags AS t ON m.id = t.medium_id AND t.tag = 'test'
GROUP BY m.id
ORDER BY count DESC

Assuming m.id is a unique key for the media table.

Regards,
Bill K.
May 25 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
15237
by: Mike N. | last post by:
Hello: I am new to T-SQL programing, and relativly new to SQL statements in general, although I have a good understanding of database theory. I'm a little confused as to the fundamental differences between a view and a query, or rather, when it is more appropriate to use one vs. the other. It seems to me that most select queries can be implemented as views, and I can't see the downside to doing so.
9
4365
by: Dom Boyce | last post by:
Hi First up, I am using MS Access 2002. I have a database which records analyst rating changes for a list of companies on a daily basis. Unfortunately, the database has been set up (by my predecessor, I hasten to add) so that each day it creates a copy of the record for each company, changes the date to today's date, and prompts the user for any changes of ratings on that day. The resulting data table grows by approx 600 records per...
2
1416
by: nsh | last post by:
running access 2k; I have some stats (bowling) that I want to print in columns on a report. The top portion of the report will have a team-members name, and the bottom of the report will have a sub-report that presents the stats in UP-TO 4 columns.
0
1512
by: Mark Harrison | last post by:
HOWTO: Integrating Posgresql queries into an event loop. Mark Harrison mh@pixar.com May 27, 2004 Problem ------- The commonly used postgresql APIs will block until completed.
5
5561
by: listerofsmeg01 | last post by:
Hi, Pretty new to PHP and MySQL. I have a page on my site that displays a lot of information from various tables. Currently I have lots of small PHP wrapper functions around SQL queries to get each bit of information. This results in maybe 10 queries to display one page, but they are all very small and simple, and it keeps the PHP looking nice too as they are simple function calls to get each piece of info, which can be called from any
7
20339
by: =?Utf-8?B?QVRT?= | last post by:
HOWTO Run multiple SQL statements from ASP/ADO to an Oracle 10g. Please help, I'm trying to write an ASP page to use ADO to run a long query against an Oracle 10g database, to create tables, if they do not already exist. In terms of ASP/ADO, that would be fine in a SQL Server Sense by a simply ASP/Server-Side JavaScript as such: var cnTemp = Server.CreateObject("ADODB.Connection");
2
2093
by: garethfx | last post by:
Hi all Anybody had experience bring together 2 or more crosstab queries using the UNION? ALL 3 crosstabs come from 3 sourcing queries from the same table so of course normally runnning 3 crosstabs at the sametime throws an error I have to pull 3 crosstabs together. All are laid out as The first is those number of people starting a training programme Row
7
5100
by: dmitrey | last post by:
hi all, can anyone explain howto get function from module, known by string names? I.e. something like def myfunc(module_string1, func_string2, *args): eval('from ' + module_string1 + 'import ' + func_string2') return func_string2(*args) or, if it's impossible, suppose I have some modules
1
4438
by: JosAH | last post by:
Greetings, Introduction This week we start building Query objects. A query can retrieve portions of text from a Library. I don't want users to build queries by themselves, because users make mistakes. Instead, the Library hands out queries to the user given a simple query String. This is how the library does it:
0
9810
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9653
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10236
tracyyun
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...
0
9348
agi2029
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...
0
5639
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...
0
5805
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4434
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
2
3997
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3092
bsmnconsultancy
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.