Mysql Select Problem

[Guest]

Good day all.

Im having problems with selecting information from a mysql database.

This is what i want to do.

I have a table with a bunch of products and prices in them.

This is the query i use to access the info which works perfectly

"
SELECT CONCAT(groupno,"-",subcode) AS 'Key', CONCAT(groupno, " - ",subcode," - ",headertext," - ",description," -R",price) as 'VALUE'
FROM subgroups
WHERE
subcode LIKE '#FORM.Search#'

"

Now While that query is running i want to check the table "inventory" to see if i have the items in stock

a sample query would be something like

"
SELECT *
FROM
inventory WHERE subgroup = ''#FORM.search#' AND sold = 'no'
"

I beleive one could get this total with the COUNT option in mysql. But how do i make the initla query go find the total inventory items in stock.

Ive tried reading about inner JOIN. but that does not make much sence to me.

Any help would be greatly appreciated

[-__cfSearching__-]

ShapeShift wrote:
> Ive tried reading about inner JOIN. but that does not make much sence to me.

The MySQL documentation is good for understanding the syntax of different commands. But it assumes you are already familiar with JOINs, which you should be as they are a fundamental part of sql. If you are not familiar with them, I would strongly suggest getting a book on sql and/or reading some a high level tutorials. That will give you an understanding of how joins and other sql concepts. Once you understand those, the syntax is simple:

http://www.w3schools.com/Sql/sql_join.asp

(But to give a brief explanation in terms of your tables) The concept of a join is simple: you have two or more tables that store related information. For example, "subgroups" stores information about products and "inventory" stores how many of those products you have in stock. To retrieve information from both tables, in the same query, MySQL needs to know how the records from each table are related. In other words, which stock records belong to which products. This is done through a JOIN .

It sounds as if your two tables are related through the "subgroup" column. So you would JOIN the two tables on the "subgroup" column. I do not know all of the columns in your inventory table, but this might be an example:

SELECT sg.SubGroup, sg.Description, i.Sold
FROM subgroups sg INNER JOIN inventory i ON sg.subgroup = i.subgroup

There are also different types of JOINS. The type of join you should use depends on what information you need to return. But that is something you can read about on your own.

HTH