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-10T16:16:35Z —
#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?

V/r,

^_^

r937
—
2014-04-10T22:09:01Z —
#3

[ot]

WolfShade said:

V/r

wha?[/ot]

WolfShade
—
2014-04-11T12:56:51Z —
#4

I work in a DoD environment. "V/r" = "Very respectfully".

V/r,

^_^

guido2004
—
2014-04-11T13:13:04Z —
#5

Off Topic:

DoD ?

DaveMaxwell
—
2014-04-11T13:18:32Z —
#6

guido2004 said:

Off Topic:

DoD ?

Off Topic:

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-11T13:24:33Z —
#7

DaveMaxwell said:

Off Topic:

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.

Yes, Department of Defense. And, yes, the military loves acronyms. Almost EVERYTHING has an acronym.

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-11T16:40:07Z —
#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-11T16:45:04Z —
#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-11T16:46:32Z —
#18

Thanks WolfShade do you know if php has a similar attribute?

oddz
—
2014-04-11T17:01:22Z —
#19

sketchgal said:

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?

Fair enough.

sketchgal said:

Thanks WolfShade do you know if php has a similar attribute?

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-11T17:03:22Z —
#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.