SQL Query returns fake duplicate results?
I've been trying to write a few little plugins for personal use with WHMCS. Essentially what I'm trying to do here is grab a bunch of information about a certain order(s), and return it as an array in Perl.
The Perl bit I'm fine with, it's the MySQL query I've formed that's giving me stress..
I know it's big and messy, but what I have is:
SELECT tblhosting.id, tblhosting.userid, tblhosting.or开发者_运维知识库derid, tblhosting.packageid, tblhosting.server, tblhosting.domain, tblhosting.username, tblorders.invoiceid, tblproducts.gid, tblservers.ipaddress, tblinvoices.status
FROM tblhosting, tblproducts, tblorders, tblinvoices, tblservers
WHERE tblorders.status = 'Pending'
AND tblproducts.gid = '2'
AND tblservers.id = tblhosting.server
AND tblorders.id = tblhosting.orderid
AND tblinvoices.id = tblorders.invoiceid
AND tblinvoices.status = 'Paid'
I don't know if this /should/ work, but I assume I'm on the right track as it does return what I'm looking for, however it returns everything twice.
For example, I created a new account with the domain 'sunshineee.info', and then in PHPMyAdmin ran the above query.
id userid orderid packageid server domain username invoiceid gid ipaddress status
13 7 17 6 1 sunshineee.info sunshine 293 2 184.22.145.196 Paid
13 7 17 6 1 sunshineee.info sunshine 293 2 184.22.145.196 Paid
Could anyone give me a heads up on where I've gone wrong with this one.. Obvioiusly (maybe not obviously enough) I want this as only one row returned per match.. I've tried it with >1 domain in the database and it returned duplicates for each of the matches..
Any help would be much appreciated :)
SELECT distinct tblhosting.id, tblhosting.userid, tblhosting.orderid, tblhosting.packageid, tblhosting.server, tblhosting.domain, tblhosting.username, tblorders.invoiceid, tblproducts.gid, tblservers.ipaddress, tblinvoices.status
FROM tblhosting, tblproducts, tblorders, tblinvoices, tblservers
WHERE tblorders.status = 'Pending'
AND tblproducts.gid = '2'
AND tblservers.id = tblhosting.server
AND tblorders.id = tblhosting.orderid
AND tblinvoices.id = tblorders.invoiceid
AND tblinvoices.status = 'Paid'
Well, its near impossible without any table definitions, but you are doing a lot of joins there. You are starting with tblhosting.id and working your way 'up' from there. If any of the connected tables has a double entry, you'll get more hits
You could add a DISTINCT
to your query, but that would not fix the underlying issue. It could be a problem with your data: do you have 2 invoices? Maybe you should select everything (SELECT * FROM
) and check what is returned, maybe check your tables for double content.
Using DISTINCT
is most of the time not a good choice: it means either your query or your data is incorrect (or you don't understand them thoroughly). It might get you the right result for now, but can get you in trouble later.
A guess about the reason this happens: You do not connect the products table to the chain of id's. So you are basically adding a '2' to your result as far as I can see. You join on products, and the only thing that limits that table is that "gid" should be 2. So if you add a product with gid 2 you get another result. Either join it (maybe tblproduct.orderid = tblorders.id ? just guessing here) or just remove it, as it does nothing as far as I can see.
If you want to make your query a bit clearer, try not implicitly joining, but do it like this. So you can actually see what's happening
SELECT tblhosting.id, tblhosting.userid, tblhosting.orderid, tblhosting.packageid, tblhosting.server, tblhosting.domain, tblhosting.username, tblorders.invoiceid, tblproducts.gid, tblservers.ipaddress, tblinvoices.status
FROM tblhosting
JOIN tblproducts ON /*you're missing something here!*/
JOIN tblorders ON tblorders.id = tblhosting.orderid
JOIN tblinvoices ON tblinvoices.id = tblorders.invoiceid
JOIN tblservers ON tblservers.id = tblhosting.server
WHERE
tblorders.status = 'Pending'
AND tblproducts.gid = '2'
AND tblinvoices.status = 'Paid'
I don't see in your query JOIN to tblproducts
, it seems to be a reason.
精彩评论