trying to get multiple query to work

CREATE TABLE `invoice` (
  `id` double NOT NULL auto_increment,
  `invoicenumber` double default '0',
  `ponumber` double default '0',
  `paymenttype` varchar(100) default '',
  `salesrepid` int(11) default '0',
  `customerid` double default '0',
  `date` date default '0000-00-00',
  `time` time default '00:00:00',
  `quoteorinvoice` varchar(100) default 'quote',
  `quotenumber` double default '0',
  `shippingcharge` decimal(10,2) default '0.00',
  `salestax` decimal(10,2) default '0.00',
  `tmp2` varchar(100) default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;



CREATE TABLE `invoice_parts` (
  `id` double NOT NULL auto_increment,
  `part_id` double NOT NULL default '0',
  `interchangenumber` varchar(100) default '',
  `description` tinytext,
  `rate` decimal(10,2) default '0.00',
  `qty` int(11) default '1',
  `taxable` varchar(20) default '',
  `invoice_notes_id` double default '0',
  `invoiceid` int(11) NOT NULL default '0',
  `filled` tinyint(4) default '0',
  `isrelocated` tinyint(4) default '0',
  `credit` tinyint(4) default '0',
  `trackingnumber` varchar(50) default NULL,
  `shippingcost` decimal(10,2) default NULL,
  `ourcost` decimal(10,2) default '0.00',
  `isvendorshipping` varchar(100) default '',
  `paymentinfo` varchar(50) default NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;


CREATE TABLE `users` (
  `users_id` int(11) NOT NULL auto_increment,
  `users_name` varchar(100) NOT NULL default '',
  `users_password` varchar(100) NOT NULL default '',
  `users_level` int(11) default '0',
  `users_email` varchar(100) NOT NULL default '',
  `users_setup` varchar(100) default '',
  PRIMARY KEY  (`users_id`)
) TYPE=MyISAM;


SELECT distinct(invoiceid) FROM invoice_parts where filled='0'

SELECT * FROM invoice where id='$invoiceid'

SELECT * FROM users where users_id='$users_id'


i want the output to look like this
-----------------------------------
invoiceid   users_name      totalnumber (where filled=0)
12345          sally                 2
LVL 1
aot2002Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

snoyes_jwCommented:
I'm not exactly sure what results you are looking for, but start with something like this:

SELECT invoice.invoiceid, users_name, count(invoice_parts.invoiceid) as totalnumber from invoice, users, invoice_parts WHERE
users_id = '$users_id' AND invoice.id = '$invoiceid' invoice_parts.invoiceid = invoice.invoice id AND filled = 0 group by invoice.invoiceid;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SqueebeeCommented:
What links these three pieces of information together? Is the users_id a salesrepid or customerid of the invoice table? What version of MySQL do you use?

Assume sally is a salesrep:

SELECT invoiceid, users_name, s1.missed
FROM invoice, users,
(SELECT invoiceid, COUNT(invoiceid) AS missed
FROM invoice_parts
GROUP BY invoiceid
WHERE filled = 0) AS s1
WHERE invoice.salesrepid = users.users-id
AND invoice.invoiceid = s1.invoiceid;
0
SqueebeeCommented:
Whoops:

SELECT invoiceid, users_name, s1.missed
FROM invoice, users,
(SELECT invoiceid, COUNT(invoiceid) AS missed
FROM invoice_parts
GROUP BY invoiceid
WHERE filled = 0) AS s1
AND invoice.salesrepid = users.users-id
AND invoice.invoiceid = s1.invoiceid
AND users.user_id = $users_id;
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

aot2002Author Commented:
4.0.21
0
aot2002Author Commented:
SELECT invoiceid, users_name, s1.missed
FROM invoice, users,
(SELECT invoiceid, COUNT(invoiceid) AS missed
FROM invoice_parts
GROUP BY invoiceid
WHERE filled = 0) AS s1
AND invoice.salesrepid = users.users-id
AND invoice.id= s1.invoiceid
AND users.user_id = 0;

[local] ERROR 1064: 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 invoiceid, COUNT(invoiceid) AS missed
0
aot2002Author Commented:
BTW the invoice.invoiceid is not how it works
its invoice.id as there is no valid information anymore in the invoice.invoiceid
0
aot2002Author Commented:
well let me revise that
only from the invoice table it uses the id field for a link or relationship to the invoice_parts table and then i want to display the usersname not id too.

so basically i store items in the parts table and i store items in the invoice table
if i add an invoice it goes like this

invoice.id = 1
invoicenumber="" <--not used right now
ponumber=""<--not used right now
paymenttype="cash"
salesrepid="46"
customerid="99"
date=datenow()
quoteorinvoice="invoice"
quotenumber=""<--not used right now
shippingcharge="46.09"
  `salestax="7.89"







  `id` =autoincrement
  `part_id` ="101"
  `interchangenumber="09854"
  `description` ="right side fendor"
  `rate` ="9.99"
  `qty` ="1"
  `taxable` ="1"
  `invoice_notes_id` =""
  `invoiceid` ="1" <--LINKED WITH INVOICE ID
  `filled` ="0", <--NOT FINISHED WITH INVOICE
0
SqueebeeCommented:
CREATE TEMPORARY TABLE missed
SELECT invoiceid, COUNT(invoiceid) AS missed
FROM invoice_parts
GROUP BY invoiceid
WHERE filled = 0

SELECT invoice.id, users_name, missed.missed
FROM invoice, users, missed
AND invoice.salesrepid = users.users-id
AND invoice.id= missed.invoiceid
AND users.user_id = 0;
0
aot2002Author Commented:
snoyes_jw  ---> I motified your query here as

SELECT invoice.id, users_name, count(invoice_parts.invoiceid) as totalnumber from invoice, users, invoice_parts WHERE
users_id = invoice.salesrepid and invoice_parts.invoiceid = invoice.id AND filled = 0 group by invoice.id order by users_name desc;

and i get everything i need thanks guys
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.