How to make my three table sql join work?

MySQL database
#1

Hi Everybody,
i am working on a project. and i am trying to query three table through [php]INNER JOIN[/php]…But it i not working…So i want to expose the issue to you…so somebody could help…here is how i design my db
[php]
item table

id_item(PK,AI) item_name (char, 100) item_price(char,10)
1 nike 12000[/php]

[php]groupe table
id_groupe (PK, AI) groupe_name (char, 100)
1 homme[/php]

[php]color table
id_color (PK, AI) name_color(char, 30)
1 white[/php]

[php]item_groupe
it_ID (PK) groupeid(PK)
1 1
[/php]
[php]item_color
itemId(PK) colorId(PK)
1 1
[/php]
So, I want to achieve is to query the tables to sort out the following information about the item:
the item name, item price and item color. So I run the follwoing sql query.

[php]$sql ="SELECT items.id_item,item_name FROM items INNER JOIN item_color ON item_color.itemId=items.id_item INNER JOIN color ON color.id_color = item_color.colorId INNER JOIN
 	item_groupe ON item_groupe.it_ID = id_item INNER JOIN groupe ON item_groupe.groupeid = groupe.id_groupe WHERE id_groupe =1";
 	$stmt = $pdo->prepare($sql);
 	$stmt->execute();[/php]

Can somebody help!

#2

Confused on your tables. Normally in a sales site all of your options for a product would be in one large
items table. Or as most call it a products table. Colors of an item would make it a different item. A red
shirt and a yellow shirt would be two individual items. If an item is a part of a group of items or several
groups of items, that would also be in the items table as an array of groups that item belongs to. In that
way, you do not need to over tax your server doing a large number of joins. So, normally all of these tables
would be just one table with a unique key (id_item). Then, you can do quick queries to pull all variations of
the items in one simple query. Let’s say you have two shirts in a group clothing, there would be two entries
for them in the items table. Each row would have all of the data for each shirt. Usually, there would be a
field for item-type. In this case shirts. Then, you just do a query to select all from items where type is shirt
and you get all the shirts. You group them by color or a group field if there is one. If your items all use the
same colors and same groups, then, you can do it your way with separate tables.

But, if you must do the joins, here is a tutorial on how they work. This page explains the ways to join tables
and press Next-Chapter and you can read up on INNER JOINS. Or click on the left to look at other MySQL
functions. http://www.w3schools.com/sql/sql_join.asp

And, for designing a database from scratch, simple is always the best way in my humble opinion. But, you
need to layout all of your data needed first. Then, decide which of these data items belong to what. If all
of the data belongs to one item or product, then it goes into one table. As an example, you normally place
all user info such as name, address, phone, etc into one table called users. You would never place this info
into a table named items. So, colors attached to an item would be needed to be in the items table and the
row for that one item. Hope that makes sense! Now, here is a tutorial on database design. It might give
you ideas on how to recreate your tables to work better. Hope this helps!

A good place to start: (tons of small tutorials on SQL)
http://sqlzoo.net/
Another one:
http://www.dreamincode.net/forums/topic/53955-mysql-tutorial-part-1-design/
And another one…
http://www.atlasindia.com/sql.htm (Harder to read, but, a lot of knowledge in it.)

Not sure if this all helps you or not. If not, ask more questions…

#3

No, normally you want to follow some standards on database normalization which usually involve splitting large tables into smaller tables.

Normally in a web shop the color is a variation of an item. Same with size, fabric, etc.

Relational databases were made for joins, storing values in some array format in a column (enum or json or whatever) is considered bad practise - for one it makes querying for the data a pain.

Then you lock down your application to only allow one user to have one address, one phone number, etc.

OP:
your scheme makes sense to me, the only things I would like to pick on is

you don’t need the table name in the column names
First of all item.id_item is redundant, and you can see - even in your small starter scheme - how this becomes confusing quickly (item and groupe use tableName_columnName, while color uses columnName_tableName)

The same goes for small/large letters, atm you have a kind of convention mix going on. It’s much easier to stick to camelCase in PHP and snake_case in SQL.

On your problem: please post your best attempt at a query and we’ll see if we can push you in the right (left?) direction

#4

Well, JimL, I agree with your comments, but, not really in this case. The poster is a beginner and only needs
to get started. I do not suggest they need to split up a seven-field database into multiple tables. In my
humble opinion, seven fields can be in one table with ease and it makes the queries much more simple.
(Especially for a beginner…) I was just attempting to get them to start thinking of their data and how it
all connects…

Also, he asked how does he design his database. Perhaps you should help with that answer!?!

#5

Then you should say that instead of

#6

Back to the OP, saw you had posted your query. You just need to use the table names and the column names so the SQL server know what you want to select. This might be why you’ve decided to use different names for the different columns (not sure), but that’s not necessary.

This would be no problem for sql

SELECT item.id, color.id, group.id

[hr]

Your query with table names, spun up a docker container to run it so I know it works. Please post any error messages if you still have problems as we (might) have different table/column names etc.

SELECT items.id_item, items.item_name, color.color_name FROM items JOIN item_color ON item_color.itemId = items.id_item JOIN color ON color.id_color = item_color.colorId JOIN item_groupe ON item_groupe.it_ID = id_item JOIN groupe ON item_groupe.groupeid = groupe.id_groupe WHERE groupe.id_groupe = 1

Note that you do not need the last join in this query though - since you might as well query for item_groupe.groupeid = 1 directly.

Sponsor our Newsletter | Privacy Policy | Terms of Service