MySQL Select Data from two Tables in 1 Query with out specifying wild card?

Databases
1

Below is an excerpt from an SQL query I am doing with php and it’s working as I expected and wanted however for my own curiosity’s sake I want to know how to only select the data I need in a multi-table query.

select * from item_inventory, order_form_items where item_inventory.item_inventory_id = "1796" and order_form_items.order_form_item_id = "7" OR item_inventory.item_inventory_id = "2785" and order_form_items.order_form_item_id = "6" OR item_inventory.item_inventory_id = "2602" and order_form_items.order_form_item_id = "5" OR item_inventory.item_inventory_id = "1793" and order_form_items.order_form_item_id = "8" order by item_inventory.item_inventory_title asc

I do no need or want * from both table rows. How would I adjust the query so that I only get * from item_inventory, and say “date_released” from order_form_items?

I have tried to no avail:

select * from item_inventory, date_released order_form_items where

Any tips?:shifty:

2

Don’t use the *, instead specify all column names you want to extract.
Good practice in a multiple table query is to specify the columns table as well:


    item_inventory.column1
  , item_inventory.column2
  ...
  , order_form_items.date_released
3

So I want to do:


select table1.column1 table1.column2 table2.column1 where yatta yatta?

or do I require the “from” keyword in there?

4

yes

And while you’re at it, use INNER JOIN instead of the implicit (,) join between tables.

5

I do appreciate you taking the time to help, but please understand MySQL is very challenging for me.

Where would I place the INNER JOIN keyword and why do I not want to do an Implicit Join?

Edit: I also do not understand where to place the “from” keyword in the query when selecting from multiple tables.

6 

SELECT
    table1.column1
  , table1.column2
  , table2.column3
FROM table1
INNER JOIN table2
ON table1.column = table2.column
WHERE ....

In a simple query like this with only one join there may not be much difference between using an implicit join and an explicit one, but when you start adding other tables the explicit join lets you see immediately the join conditions for each join:


SELECT
    table1.column1
  , table1.column2
  , table2.column3
FROM table1
INNER JOIN table2
ON table1.column5 = table2.column6
INNER JOIN table3
ON table1.column7 = table3.column8
AND table1.column9 = table3.column10
INNER JOIN table4
ON table1.column11 = table3.column12

ecc

WHERE ....
7

Wow! Fantastic help. Thank you. I shall go try a bunch of that.

8

That totally worked. Thank you so much man. Been struggling with the joins for a while.

The only part I’m confused by is the “from table1” part.

Why is it not

 from table1 and table2

Wouldn’t that be more congruent with the intuitive SQL syntax?

9

It’s because as well as INNER JOINs there are also other types of join in the sql syntax:

See Understanding JOINs in MySQL and Other Relational Databases » SitePoint for some more info. The other type not mentioned there is a CROSS JOIN.