sketchgal — 2014-04-10T12:12:38-04:00 — #1
Looking for some help with a SELECT query please.
I have two tables of data, each table has a reference column in it which is what matches the data.
Table 1 contains product information and Table 2 contains discount information on the products.
I am trying to set up a query which will only show items from Table 1 where a discount for that item is set in Table 2.
I've had a look at some of the SQL JOIN statements but I can't get them to work correctly
Can anyone help me with this please?
Thanks in advance
wolfshade — 2014-04-10T12:16:35-04:00 — #2
Standard JOIN should get you only data where there is a match in both tables. It's the LEFT/RIGHT OUTER JOINS that will get all data even if no matching data is in the other table.
May we see your query, so far?
r937 — 2014-04-10T18:09:01-04:00 — #3
wolfshade — 2014-04-11T08:56:51-04:00 — #4
I work in a DoD environment. "V/r" = "Very respectfully".
guido2004 — 2014-04-11T09:13:04-04:00 — #5
davemaxwell — 2014-04-11T09:18:32-04:00 — #6
Presumably Department of Defense (though there seem to be a load of alternates
:eek:) - they short hand/acronymize EVERYTHING - drives you nuts after a while.
wolfshade — 2014-04-11T09:24:33-04:00 — #7
Yes, Department of Defense. And, yes, the military loves acronyms. Almost EVERYTHING has an acronym.
wolfshade — 2014-04-11T09:35:03-04:00 — #8
Follow up: Here's an idea of how much the military loves acronyms.
UPDATE: I just realized that this is not the comprehensive list I thought it was. It's missing a few.
guido2004 — 2014-04-11T09:49:44-04:00 — #9
Ahhh, NIGI. TYVM
wolfshade — 2014-04-11T10:14:08-04:00 — #10
I just realized that the OP (sketchgal) has not returned. I hope we didn't chase her off. I apologize for hijacking the thread.
davemaxwell — 2014-04-11T10:48:12-04:00 — #11
Or bizarre terms - my favorite was Sneaker Net, which was for physically walking a disk from a computer on a secure network to a computer on a unsecure network and vice verse.
davemaxwell — 2014-04-11T10:51:18-04:00 — #12
Yup - there are a handful that I worked for that aren't on that list.....
sketchgal — 2014-04-11T11:00:41-04:00 — #13
Nope, not scared off, just not had a chance to get back to this!
Here now though, I'll have a look at the JOIN statement thats been suggested!
Thanks so far guys.
sketchgal — 2014-04-11T12:23:20-04:00 — #14
Brilliant! I've now got it to return the rows I need it to which is great. Only need to work out how I get it to count these rows now for pagination etc.
Here's my query now:
$testquery = "SELECT Virtual_VIP_Offers.Reg, Virtual_VIP_Offers.OfferA, Virtual_VIP_Offers.OfferB, Virtual_VIP_Offers.OfferC, Virtual_VIP_Offers.OfferD, Virtual_VIP_Offers.OfferE, Virtual_VIP_Offers.OfferF, Virtual_VIP_Offers.Reserved, Used_Stock.Full_Registration, Used_Stock.Year, Used_Stock.Make, Used_Stock.Model, Used_Stock.Variant, Used_Stock.Picture_Refs, Used_Stock.Price
INNER JOIN Used_Stock
ON Virtual_VIP_Offers.Reg=Used_Stock.Full_Registration WHERE
Virtual_VIP_Offers.Site IN ('123318', '123155') AND Virtual_VIP_Offers.OfferA !='' AND Virtual_VIP_Offers.OfferA !='sold'
Virtual_VIP_Offers.Site IN ('123318', '123155') AND Virtual_VIP_Offers.OfferB !='' AND Virtual_VIP_Offers.OfferA !='sold'
Virtual_VIP_Offers.Site IN ('123318', '123155') AND Virtual_VIP_Offers.OfferC !='' AND Virtual_VIP_Offers.OfferA !='sold'
Virtual_VIP_Offers.Site IN ('123318', '123155') AND Virtual_VIP_Offers.OfferD !='' AND Virtual_VIP_Offers.OfferA !='sold'
Virtual_VIP_Offers.Site IN ('123318', '123155') AND Virtual_VIP_Offers.OfferE !='' AND Virtual_VIP_Offers.OfferA !='sold'
$qry_result = mysql_query($testquery) or die(mysql_error());
Can anyone tell me how I would use this sort of count query for this please?
$firstcountquery = "SELECT COUNT(*) as Num FROM Used_Stock WHERE Feed_Id IN ('123318', '123155')";
$total_results_start = mysql_result(mysql_query("$firstcountquery"),0);
any help greatly appreciated.
oddz — 2014-04-11T12:33:52-04:00 — #15
Were you the one responsible for having products support multiple offers by adding columns offerB, offerC, offerD, and offerE? The reason I ask is because the addition of those columns does not adhere to first normal form. The means of resolving the business problem of supporting multiple offers per product has introduced a quite apparent amatuer mistake and flaw in the database architecture. If you made that decision than I highly suggest taking the proper time to do things right. If that wasn't your doing than forget I said anything because we all have had to deal with other peoples poor decisions and make the best of a bad situation. However, from where I stand it looks to me like the requirement was products would only ever support a single offer and someone has hacked on multiple offer support due to a change in scope or feature enhancement. That way of adding multiple offer support if not amatuer is just sloppy and quite frankly unprofessional.
sketchgal — 2014-04-11T12:40:07-04:00 — #16
It's the structure I have to work with, not my design I'm affraid. Is there a way to get a count based on this sort of query?
wolfshade — 2014-04-11T12:45:04-04:00 — #17
Most server-side solutions have that information already. For instance, ColdFusion <cfquery> has an attribute called "recordCount" that will tell you how many records were returned in the query.
sketchgal — 2014-04-11T12:46:32-04:00 — #18
Thanks WolfShade do you know if php has a similar attribute?
oddz — 2014-04-11T13:01:22-04:00 — #19
If the relationship between Virtual_VIP_Offers and Used_Stock is 1:1 SQL_CALC_FOUND_ROWS can be used. Otherwise duplicates need to be collapsed so the true number of rows shown client side match the result set. That would most likely be done by using a combination of grouping and subqueries.
wolfshade — 2014-04-11T13:03:22-04:00 — #20
I'm pretty sure it does, but can't say for certain. Even if it didn't, you could always query the db, place the query object into an array, close the db connection, and use the length of the array as the number of returned records. I like doing it that way, just because it allows the connection to close before outputting the data.
next page →