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
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.
Presumably Department of Defense (though there seem to be a load of alternates ) - they short hand/acronymize EVERYTHING - drives you nuts after a while.
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.
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.
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
FROM Virtual_VIP_Offers
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'
OR
Virtual_VIP_Offers.Site IN ('123318', '123155') AND Virtual_VIP_Offers.OfferB !='' AND Virtual_VIP_Offers.OfferA !='sold'
OR
Virtual_VIP_Offers.Site IN ('123318', '123155') AND Virtual_VIP_Offers.OfferC !='' AND Virtual_VIP_Offers.OfferA !='sold'
OR
Virtual_VIP_Offers.Site IN ('123318', '123155') AND Virtual_VIP_Offers.OfferD !='' AND Virtual_VIP_Offers.OfferA !='sold'
OR
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);
echo $total_results_start;
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.
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.
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.
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.
) - they short hand/acronymize EVERYTHING - drives you nuts after a while.
Yes, Department of Defense. And, yes, the military loves acronyms. Almost EVERYTHING has an acronym.