I have written a forum and am using the following query to search in it:
$query="select topics.tid,f_me ssages.messid from f_messages left join
topics on f_messages.tid = topics.tid where ($title_list) OR ($msg_list)";
I trimmed it a bit to save space here (the last two are just strings).
It works fine, but it's returning more than once the same topics.tid
I want it to return only unique topic id's.
distinct doesn't work. I think it has to do with the f_messages.tid =
topics.tid as it isn't using distinct there.
I tried returning distinct(f_mess ages.tid) instead but again, no help!
This is using mysql. Re-structuring the db isn't an option.
Thanks for any light on this one, I can only think of dumping the tid's
into a TEMP dbase and selecting distinct from there, although it sure is
messy!
-- po**@sixbit.org
SDF Public Access UNIX System - http://sdf.lonestar.org 2 3871
In article <be************ @ID-30799.news.uni-berlin.de>, Agelmar wrote: This should produce an output of something like Topic ID | Message ID 1 |1 1 |2 1 |3 2 |7 2 |13 3 |4 3 |5 ...
Right, thing is, on a forum search you don't want to see the same topic a
gazillion times.
I want unique topic id's, which goes against my query.
The (group by topics.tid) part seemed to reduce this, but I'm not getting
unique (distinct) tid's.
unless of course you only care if *some* message in a given thread matches, and you just want the first message in the thread that matches and don't care about the rest...
Yeah.
in this case, you can just do something like:
$query="select topics.tid, MIN(f_messages. messid) from f_messages left join topics on f_messages.tid = topics.tid GROUP BY topics.tid where ($title_list) OR ($msg_list)";
I hope this helps, I must admit I am not totally sure that I understand your question, but I gave it my best and I hope this is what you're looking for.
Thanks, this actually solved it, where above I have duplicate tid's even
after your addition this is due to proper db duplications which shouldn't
be there.
Thanks!
On a side note I had to put the GROUP BY at the end of the query to get it
to work.
-- po**@sixbit.org
SDF Public Access UNIX System - http://sdf.lonestar.org
poff <po**@sixbit.or g> wrote in message
news:<sl******* **********@otak u.freeshell.org >... I have written a forum and am using the following query to search in it:
$query="select topics.tid,f_me ssages.messid from f_messages left join topics on f_messages.tid = topics.tid where ($title_list) OR ($msg_list)";
I trimmed it a bit to save space here (the last two are just strings).
It works fine, but it's returning more than once the same topics.tid
And it should... Let's say topic number 5 has three messages in it
(messid = 123, 135, and 148), so if you run
SELECT topics.tid, f_messages.mess id
FROM f_messages LEFT JOIN topics
ON f_messages.tid = topics.tid
WHERE topics.tid = 5;
you will get something like this back:
+------------+-------------------+
| topics.tid | f_messages.mess id |
+------------+-------------------+
| 5 | 123 |
| 5 | 135 |
| 5 | 148 |
+------------+-------------------+
Remember, DISTINCT applies to an ENTIRE ROW IN THE RESULT SET,
NOT to a single field and NOT to an entire record in the source
table. So the three records in the result set above are NOT
distinct and will all be returned.
I want it to return only unique topic id's.
Then ask for them accordingly:
SELECT DISTINCT tid FROM topics WHERE [your WHERE clause];
Also, it seems to me you are trying to fetch the first (and only
the first) message from each topic; this can be done like this:
SELECT topics.tid AS topic, MIN(f_messages. messid) as message
FROM f_messages LEFT JOIN topics
ON f_messages.tid = topics.tid
WHERE [your WHERE clause]
GROUP BY topics.tid;
Cheers,
NC This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: B. Fongo |
last post by:
I learned MySQL last year without putting it into action; that is why
I face trouble in formulating my queries. Were it a test, then you
would have passed it, because your queries did help me solve my problem.
I'll turn to MySQL doc after getting through this pressing project.
Thanks a lot Roger!
Babale
-----Urspr=FCngliche Nachricht-----
|
by: Good Man |
last post by:
Hi there
Yes, I've read about JOINs, albeit after coding for a couple of years
already using queries like the following:
"SELECT m.LastName, m.FirstName, o.Address FROM members m, offices o WHERE
o.City='$vCity' AND m.Status<>'Retired' AND m.Status<>'Suspended' AND
o.MemberID=m.MemberID ORDER BY LastName,FirstName"
($vCity comes from a drop-down list of city names)
|
by: Greg |
last post by:
I'm a quantitative securities analyst working with Compustat data
(company fiscal reports and pricing feeds).
My coworker came across a problem that we fixed, but I'd like to
understand 'why' it was happening and just don't get it yet.
Here's the starting query (reduced to simple prefixes):
----INITIAL-----
|
by: Steve |
last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three
"Left Outer Joins" in order to return every transaction for a specific
set of criteria.
Using three "Left Outer Joins" slows the system down considerably.
I've tried creating a temp db, but I can't figure out how to execute
two select commands. (It throws the exception "The column prefix
'tempdb' does not match with a table name or alias name used in the
query.")
|
by: dskillingstad |
last post by:
I would really appreciate someone's help on this, or at least point me
in the right direction....
I'm working on a permit database that contains 12 tables, and rather
than list all of the tables, I'll just list a few, as the links are the
same for all tables. These tables are:
tblPermitMain
tblApplicant
tblContractor
tblEngineer
| |
by: bhargav.desai |
last post by:
Hello Gurus,
I need help! I have two table, tblCurrent and tblPrevious. What I
want to join the tables, and create a new table that have matching
records from both the tables, plus this new table also includes
records from tblcurrent that were are not in the tblprevious and also
records from tblprevious that are not in tblcurrent!
How can I accomplish this in one query? or Can I?
|
by: davegb |
last post by:
I'm trying to create a self-join table to show the relationship between
employee and supervisor. In another thread, I was advised to create a
SupervisorID in the employee table, a separate Supervisor table, and
join the Supervisor table to the Employee table and a copy of the
Supervisor table to create the self-join. I can't figure out how to do
this from reading Viescas or from researching it here. I can start a
query and create 2 copies...
|
by: CSN |
last post by:
I have a pretty simple select query that joins a table
(p) with 125K rows with another table (pc) with almost
one million rows:
select p.*
from product_categories pc
inner join products p
on pc.product_id = p.id
where pc.category_id = $category_id
order by p.title
|
by: MP |
last post by:
Hi
trying to begin to learn database using vb6, ado/adox, mdb format, sql
(not using access...just mdb format via ado)
i need to group the values of multiple fields
- get their possible variations(combination of fields),
- then act on each group in some way ...eg ProcessRs (oRs as RecordSet)...
the following query will get me the distinct groups
|
by: Zeff |
last post by:
Hi all,
I have a relational database, where all info is kept in separate tables
and just the id's from those tables are stored in one central table
(tblMaster)...
I want to perform a query, so all data from the separate tables is shown
in a view (instead of the reference id's pointing to the separate tables...)
I have some troubles formulating the SQL statement:
I tried:
|
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...
| |
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,...
|
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: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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: 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: 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...
| |