Question regarding a MySQL SELECT statement

[worldcyclist]

Hey folks!
I tried hunting for a MySQL specific NG and didn't find one but I did
see that MySQL questions get asked here. If this isn't appropriate and
you know of an appropriate NG then that would be great.

In one table I have inventory...
table name is "inventory"
columns are..
-----------------------------
item_number item_name
-----------------------------
1 radishes
2 carrots
3 potatoes
4 oranges
etc..etc

Table two is "sold"
------------------------------
date item_number
13Mar 1
14Mar 4
15Mar 2
16Mar 1
What I am querying is the table "sold" for the Unique items (1,4,2)
and then retrieving their names from table 1.

My query below isn't working..
SELECT item_name FROM inventory WHERE inventory.item_number =
sold.item_number;

I am just now starting to get into complicated MySQL queries and
actually the more I learn about it the more I love it.

I appreciate any advice anyone may have.
Many thanks!
Wilkie

[Malcolm Dew-Jones]

wo**********@gmail.com wrote:
: Hey folks!
: I tried hunting for a MySQL specific NG and didn't find one but I did
: see that MySQL questions get asked here. If this isn't appropriate and
: you know of an appropriate NG then that would be great.

: In one table I have inventory...
: table name is "inventory"
: columns are..
: -----------------------------
: item_number item_name
: -----------------------------
: 1 radishes
: 2 carrots
: 3 potatoes
: 4 oranges
: etc..etc

: Table two is "sold"
: ------------------------------
: date item_number
: 13Mar 1
: 14Mar 4
: 15Mar 2
: 16Mar 1
: What I am querying is the table "sold" for the Unique items (1,4,2)
: and then retrieving their names from table 1.

: My query below isn't working..
: SELECT item_name FROM inventory WHERE inventory.item_number =
: sold.item_number;

: I am just now starting to get into complicated MySQL queries and
: actually the more I learn about it the more I love it.

: I appreciate any advice anyone may have.
: Many thanks!
: Wilkie
You need to "join" the tables.

Try google mysql join

You need to join inventory and sold on inventory.item_number =
sold.item_number

I would type out the query but I haven't used mysl recently enough to get
the syntax right in a message like this.

--

This space not for rent.

[NC]

wo**********@gmail.com wrote:

In one table I have inventory... table name is "inventory"
columns are..
-----------------------------
item_number item_name
-----------------------------
1 radishes
2 carrots
3 potatoes
4 oranges
etc..etc

Table two is "sold"
------------------------------
date item_number
13Mar 1
14Mar 4
15Mar 2
16Mar 1

What I am querying is the table "sold" for the Unique items (1,4,2)
and then retrieving their names from table 1.

This should work:

SELECT DISTINCT
sold.item_number AS num,
inventory.item_name AS item
FROM inventory LEFT JOIN sold
ON inventory.item_number = sold.item_number;

Cheers,
NC

[vishal]

select * from inventory where item_number in (select distinct
item_number from sold);
that's it

tell me is this solved your problem or not????

[worldcyclist]

Hi Vishal..
I actually got a syntax error on this one..
You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'select distinct
item_number from sold)

By the way, I can't thank you and NC enough for the help. It's great
and appreciated.

[worldcyclist]

Hi NC,
this ALMOST hits it..... I get this ..

num item
--------------------
1 radishes
4 oranges
2 carrots
NULL tangerines
NULL mushrooms
NULL potatoes
.....
.....
......
The first three are right but all the rest of the items are quoted with
NULL as the num.

Any ideas?
Thanks again..
Wilkie

[NC]

wo**********@gmail.com wrote:

this ALMOST hits it..... I get this ..

num item
--------------------
1 radishes
4 oranges
2 carrots
NULL tangerines
NULL mushrooms
NULL potatoes
....
....
.....
The first three are right but all the rest of the items
are quoted with NULL as the num.

Most likely, this is because there were no tangerines,
mushrooms, or potatoes in the `sold` table. You can
correct the situation by including a WHERE clause in
your query:

SELECT DISTINCT
sold.item_number AS num,
inventory.item_name AS item
FROM inventory LEFT JOIN sold
ON inventory.item_number = sold.item_number
WHERE sold.item_number IS NOT NULL;

Cheers,
NC

[Steve]

NC wrote:

wo**********@gmail.com wrote:

this ALMOST hits it..... I get this ..

num item
--------------------
1 radishes
4 oranges
2 carrots
NULL tangerines
NULL mushrooms
NULL potatoes
....
....
.....
The first three are right but all the rest of the items
are quoted with NULL as the num.

Most likely, this is because there were no tangerines,
mushrooms, or potatoes in the `sold` table. You can
correct the situation by including a WHERE clause in
your query:

SELECT DISTINCT
sold.item_number AS num,
inventory.item_name AS item
FROM inventory LEFT JOIN sold
ON inventory.item_number = sold.item_number
WHERE sold.item_number IS NOT NULL;

Cheers,
NC

Why not get rid of the left join? It's incorrect in this case.

SELECT DISTINCT
sold.item_number AS num,
inventory.item_name AS item
FROM
inventory,
sold
WHERE inventory.item_number = sold.item_number;

Steve

[worldcyclist]

Yep, this one nailed it on the head and soundly. I like NC's
contribution above as well. I'm primarily a Perl programmer and I've
always liked the philosophy of TMTOWTDI. MySQL appears to be similar.

Wilkie