[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

trying to get multiple query to work

Posted on 2004-11-29
9
Medium Priority
?
375 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:aot2002
  • 5
  • 3
9 Comments
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 1200 total points
ID: 12699165
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
 
LVL 17

Assisted Solution

by:Squeebee
Squeebee earned 800 total points
ID: 12699181
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
 
LVL 17

Expert Comment

by:Squeebee
ID: 12699265
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 1

Author Comment

by:aot2002
ID: 12699326
4.0.21
0
 
LVL 1

Author Comment

by:aot2002
ID: 12699353
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
 
LVL 1

Author Comment

by:aot2002
ID: 12699365
BTW the invoice.invoiceid is not how it works
its invoice.id as there is no valid information anymore in the invoice.invoiceid
0
 
LVL 1

Author Comment

by:aot2002
ID: 12699477
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
 
LVL 17

Expert Comment

by:Squeebee
ID: 12699493
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
 
LVL 1

Author Comment

by:aot2002
ID: 12699613
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month17 days, 14 hours left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question